分析兼容矩阵可以得出如下几个结论:
- INSERT操作之间不会有冲突。
- GAP,Next-Key会阻止Insert。
- GAP和Record,Next-Key不会冲突
- Record和Record、Next-Key之间相互冲突。
- 已有的Insert锁不阻止任何准备加的锁。
5. 自增锁(AUTO-INC Locks)
AUTO-INC锁是一种特殊的表级锁,发生涉及AUTO_INCREMENT列的事务性插入操作时产生。
官方解释如下3:
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
四、insert加锁过程
官方文档6对于insert加锁的描述如下:
INSERT sets an 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.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. 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. 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.
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.
简单的insert会在insert的行对应的索引记录上加一个排它锁,这是一个record lock,并没有gap,所以并不会阻塞其他session在gap间隙里插入记录。
不过在insert操作之前,还会加一种锁,官方文档称它为insertion intention gap lock,也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他session就可完成,这样就使得insert操作无须加真正的gap lock。 假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。
假设发生了一个唯一键冲突错误,那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时,如果另外一个session已经获得该行的排它锁,那么将会导致死锁。
思考:Insert Intention Locks作用
Insert Intention Locks的引入,我理解是为了提高数据插入的并发能力。 如果没有Insert Intention Locks的话,可能就需要使用Gap Locks来代替