shared and exclusive locks


shared lock, lock for read

exclusive lock, lock for write

Intention Locks

意向锁。是表级锁(table-level locks), 表示接下来要执行的事务需要加什么类型的锁到数据行上(lock for row in a table)。有两种意向锁:

  • intention shared lock (IS) 在数据行上加共享锁
  • Intention exclusive lock (IX) 在数据行上加排它锁

The intention locking protol is as follows:

  • 一事务在加共享锁之前(acquire a shared lock on a row in a table)得先加上意向共享锁
  • 一个事务在加排它锁之前(acquire an exclusive lock on a row in a table)得先加上意向排它锁


意向锁只会阻塞表锁(Intention locks do not block anything except full table lock requests)。

Record Locks

锁住索引记录(index record)的锁,如果记录没有索引数据库会创建一个临时的索引锁住。(Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking)

For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10

Gap Locks

范围锁,锁住两个index record之间的记录,或者锁住第一个index record之前的记录,或者锁住index record之后的记录。比如:select * form t where c1 between 19 and 33 for update,会锁住19和33之间的记录,阻止在它们之间插入记录。Gab Lock用来阻止在gap之间插入数据。因此gap lock可以被不同的事务持有。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

Next-Key Locks

Record locks和gap locks结合起来就叫做Next-key locks. 效果就是锁住index record同时锁住和这个index record相邻的gap。

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

Insert Intention Locks

一种gap lock, 在insert时锁住插入的index record的gap,这样别的事务就可以快速知道自己操作的数据是否和别的插入事务有锁冲突了(通过检查是不是在同一个gap中)。

This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap

Auto-inc Locks



14.7.1 InnoDB Locking