non-locking read

A query that does not use the SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE clauses.

locking read

A SELECT statement that also performs a locking operation on an InnoDB table. Either SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE. It has the potential to produce a deadlock, depending on the isolation level of the transaction.

  • 为什么会导致死锁？

locking read & update & delete

  • set record locks on every index record that is scanned
    • record lock (唯一索引并且唯一查询)
    • next-key lock ( gap + record lock)

no indexes: MySQL must scan the entire table,every row of the table becomes locked, which in turn blocks all inserts by other users to the table. (全表扫描,扫描到的每一行都加锁)

Locks Set by Different SQL Statements in InnoDB

  • SELECT ... FROM 事务隔离级别为SERIALIZABLE才会set lock

  • SELECT ... FROM ... LOCK IN SHARE MODE 扫描到的索引记录都会set shared next-key locks

  • For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.

  • UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.设置排它锁,有唯一索引的话set record lock.

  • DELETE FROM ... WHERE ...sets an exclusive next-key lock on every record the search encounters.有唯一索引的话set record lock.

  • INSERT

    • exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.(在插入的行上面设排它锁，不会阻塞这个行前面的gap插入)

    • Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. (插入前会先设置insert intention gap lock ) Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.（假设索引记录为4和7，有两个独立的事务要插入5和6，那么这两个事务会先获得insert intention gap lock between 4 and 7,然后才会获得exclusive lock on the inserted row）

    • If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. （如果有多个事务对同一行进行插入，并且有另外一个事务持有该行的 X lock（insert or delete），那么其他事务因为duplicate key error而持有S lock，当X lock的事务释放后，持有 S lock的事务会导致死锁）

  • 锁为什么是加到inserted row上面？
    • 首先表上会加意向插入锁IX，然后才能获得X lock，
    • Auto-inc是一个特殊的表锁
    • 锁住插入的行，那么其他事务插入相同的行就会lock wait
  • 自增ID是不是连续的？不一定，为什么？
ClientA ClientB ClientC
delete from t2; 1:4 2:4
START TRANSACTION; 2:2
INSERT INTO t2(uid,coins) VALUES (1,1); START TRANSACTION; 2:3
select * from t2; INSERT INTO t2(uid,coins) VALUES (1,1); 2:4
还没commit就能查询插入的数据 select * from t2; select * from t2;
rollback; commit; 2:6
0:7 select * from t2; select * from t2;
0:7 1:7
  • INSERT ... ON DUPLICATE KEY UPDATE exclusive next-key lock rather than a shared lock when a duplicate-key error occurs
    • 为什么是 X next-key lock?
    • gap的范围是多少？怎么计算出来的？
      • gap 是怎么来的？
      • 没有gap，next-key lock会怎么样？
    • 一般的insert set record lock, 但是该语句设置 next-key lock

  • REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.（跟insert差不多,如果有冲突的话set next-key lock)

  • INSERT INTO T SELECT ... FROM S WHERE ...

    • exclusive index record lock (without a gap lock，插入的数据不一定连续) on each row inserted into T
    • InnoDB sets shared next-key locks on rows from S.
    • CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.
    • When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.

  • AUTO_INCREMENT InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction.(特殊的table lock,并且当前SQL结束该锁就被释放,而不需要等到整个事务结束)

  • FOREIGN KEY, set shared record-level locks to check constraint

  • LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks.

Innodb跟锁相关的信息

  • show engine innodb status\G;
  • 表information_schema.
  • 表information_schema.INNODB_LOCK_WAITS

SQL加锁分析

  1. insert into t2(uid,coins) select 1,1 from dual where (select count(*) from t2 where uid=1) < 10;
    • delete form t2;
    • session A: set autocommit = 0;
    • session B: set autocommit = 0;
    • session A: 执行语句
    • session B: 执行语句,此时lock wait
    • session C:
      • LOCK_TYPE: Type of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively.
      • LOCK_INDEX: Name of the index if LOCK_TYPE='RECORD', otherwise NULL.
      • LOCK_DATA: Primary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL; if there is no primary key then the InnoDB internal unique row ID number is used; If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”.
      • lock_data表明两个事务锁住了557这一条记录
      • 另一个事务lock_mode 为 S， 如果此时还有其他事务执行该语句，那么获得 S lock，锁住同一条记录，可能会导致死锁，比如 X lock的事务rollback
      • 两个事务并发运行，313616想获得X-lock，但是它得等待313624 S-lock

results matching ""

    No results matching ""