InnoDB中的锁
表如下:
Create Table: CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
数据如下:
+------+
| a |
+------+
| 11 |
| 12 |
| 13 |
| 14 |
+------+
有两个事务同时开启:
事务1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where a = 11;
Query OK, 1 row affected (0.00 sec)
mysql>
事务2
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t select 9;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t select 15;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
问题是为什么事务1删除11,会把11和它之前的范围锁定,导致事务2不能再这个区间插入数据。
这是InnoDB的事务状态,看的不是很明白,事务隔离级别为REPEATABLE-READ:
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 18446744072393214172, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 637972, ACTIVE 28 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1080, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 2804091712, query id 24 122.205.8.162 root executing
insert into t select 9
------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index idx_a of table `test`.`t` trx id 637972 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 000000000414; asc ;;
------------------
---TRANSACTION 637971, ACTIVE 32 sec
4 lock struct(s), heap size 1080, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 2802842432, query id 23 122.205.8.162 root cleaning up
可能优点啰嗦,望解答。
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这里说的很清楚啊 lock_mode X意味着是排它锁 gap代表是区间锁
也就是说在insert之前该表加入了区间排他锁,为什么呢?
因为之前执行的这句delete from t where a = 11;会在(negative infinity,11]这个区间加上排他锁,为什么是排他锁而不是Record Lock呢,因为你这里的a并非唯一索引,只是一个普通的索引,具体的看http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
mysql默认的会话隔离级别是repeated read,会产生更多的gap锁,如果可以接受幻读,可以考虑降为read commit级别,减少锁冲突的概率。