疑惑?实战演示疑惑 mysql insert到底加什么锁

 

innodb的事务隔离级别是可重复读级别且innodb_locks_unsafe_for_binlog禁用，也就是说允许next-key lock

 实验来自网上. ( 如果你没有演示出来,请check order_id 是否是非unique key.) 如果你看不懂,请看后续文章. next-key lock (glap lock)完全解析.

CREATE TABLE `LockTest` (

   `order_id` varchar(20) NOT NULL,
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`),
   KEY `idx_order_id` (`order_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

 select * from LockTest;

 empty set;

事务1	事务2
begin delete from LockTest where order_id =  'D20'
	begin delete from LockTest where order_id =  'D19'
insert into LockTest (order_id) values ('D20')
	insert into LockTest (order_id) values ('D19')
commit	commit

 

事务1 执行到insert语句会block住，事务2执行insert语句会提示死锁错误

 

错误码: 1213
Deadlock found when trying to get lock; try restarting transaction

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

 

 show engine innodb status 显示死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-04-30 15:01:55 a233b90
*** (1) TRANSACTION:
TRANSACTION 596042, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 10851, OS thread handle 0x2abfb90, query id 251521 10.10.53.122 root update
insert into LockTest (order_id) values ('D20')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 502 page no 4 n bits 72 index `idx_order_id` of table `test`.`LockTest` trx id 596042 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 596041, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 10848, OS thread handle 0xa233b90, query id 251522 10.10.53.122 root update
insert into LockTest (order_id) values ('D19')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 502 page no 4 n bits 72 index `idx_order_id` of table `test`.`LockTest` trx id 596041 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 502 page no 4 n bits 72 index `idx_order_id` of table `test`.`LockTest` trx id 596041 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

delete语句删除不存在且删除的order_id大于现有表中的所有order_id,所以delete语句会使用next-key锁住(当前最大-无穷大) 

lock_id	lock_trx_id	lock_mode	lock_type	lock_table	lock_index	lock_space	lock_page	lock_rec	lock_data
596133:502:4:1	596133	X	RECORD	`test`.`LockTest`	idx_order_id	502	4	1	supremum pseudo-record
596134:502:4:1	596134	X	RECORD	`test`.`LockTest`	idx_order_id	502	4	1	supremum pseudo-record

在官方文档找到答案, 区间锁只是用来防止其他事务在区间中插入数据，间隙锁即可以是共享锁也可以是互斥锁,不过互相之间可共存不互斥, 又分为del,update,select for update这种当前读的间隙锁和insert的间隙锁. 当前读间隙锁之间不互斥,插入的间隙锁之间也不互斥, 但是和插入的间隙锁互斥. 在可重复读隔离级别下,可用于避免当前读的幻读问题

  Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist(间隙锁可共存,不互斥). A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks(共享或者互斥方式产生的间隙锁没有区别,都不互斥). They do not conflict with each other, and they perform the same function. 

     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 (see Section 15.7.4, “Phantom Rows”).

    An insert intention lock is a type of gap lock set by  INSERT operations prior to row insertion. 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. 

    The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record
          

  当两个事务都拿到相同区间锁,但是和insert的行锁互斥,就会阻止对方往区间内做insert操作。所以第一个事务insert会阻塞,第二个事务会提示死锁

换成read-commited级别后就没死锁了! 因为没有了间隙所,读提交不需要幻读控制,也就不需要间隙锁了.

万变归一: 事务内加锁总归是为了隔离级别.

 

再来分析另外一个现象(存疑惑,待试验验证):

    上述现象表明, delete/update会阻塞insert .那么换成先insert,再delete/update呢?

    实验表明不会阻塞?  这个感觉挺矛盾的,锁不是互斥的么?

     再次从事务内加锁原因是为了隔离级别这个角度分析.  insert 

     [mysql gap 锁源代码解析 -Innodb锁系统插入/删除锁处理及死锁示例分析]

 

【解决方案有两种】

1、改变程序中数据库操作的逻辑

2、取消gap lock机制 

innodb_locks_unsafe_for_binlog启用

或者设置为隔离级别为读提交

Gap locking can be disabled explicitly.This occurs if you change the transaction isolation level to READ COMMITTED orenable the innodb_locks_unsafe_for_binlog system variable.

3. 加上unique锁

 

 

select for update / update  where 

1. 有该行  对非unique列会加 间隙共享锁 和 行锁 见 (14.2.2.4 InnoDB Record, Gap, and Next-Key Locks http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html)

2. 无该行 对非unique  会加间隙共享锁  . 这个文档比较麻烦. 要通过 上面(该文)

   (Unexpected deadlock between concurrent INSERTs when unique key violation may occ http://bugs.mysql.com/bug.php?id=35821)和

   (Deadlock detected on concurrent insert into same table (InnoDB) https://bugs.mysql.com/bug.php?id=43210 )

里面有个很好玩的案例,这两个中文博客里也是该话题 (innodb next-key lock引发的死锁 http://www.cnblogs.com/xhan/p/3701459.html)