insert 死锁
来源:互联网 发布:圆形蓄水池算法 编辑:程序博客网 时间:2024/05/24 00:48
昨天看到一个很有意思的死锁,拿来记录下:
环境:deadlock on
事务隔离级别: read commited
表结构:
root@test 08:34:01>show create table lingluo\G*************************** 1. row *************************** Table: lingluoCreate Table: CREATE TABLE `lingluo` ( `a` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `uk_bc` (`b`,`c`)) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.00 sec)
session 1:
root@test 08:45:51>select * from lingluo;+--------+------+------+------+| a | b | c | d |+--------+------+------+------+| 1 | 2 | 3 | 4 || 500 | 100 | 200 | 43 || 1000 | 10 | 20 | 43 || 10001 | 21 | 21 | 32 || 100202 | 213 | 213 | 312 || 100212 | 214 | 214 | 312 |+--------+------+------+------+6 rows in set (0.00 sec)root@test 08:46:38>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:04>insert into lingluo values(100213,215,215,312);Query OK, 1 row affected (0.00 sec)
session 2:
root@test 08:46:02>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:20>insert into lingluo values(100214,215,215,312);Query OK, 1 row affected (12.77 sec)
session3:
root@test 08:46:24>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:23>insert into lingluo values(100215,215,215,312);
session 1 rollback前:
---TRANSACTION 4F3D6F33, ACTIVE 3 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root updateinsert into lingluo values(100215,215,215,312)------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S waiting------------------TABLE LOCK table `test`.`lingluo` trx id 4F3D6F33 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S waiting---TRANSACTION 4F3D6D24, ACTIVE 5 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root updateinsert into lingluo values(100214,215,215,312)------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S waiting------------------TABLE LOCK table `test`.`lingluo` trx id 4F3D6D24 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S waiting---TRANSACTION 4F3D4423, ACTIVE 33 sec2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124692, OS thread handle 0x7fe73c89a700, query id 1435651549 localhost rootTABLE LOCK table `test`.`lingluo` trx id 4F3D4423 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D4423 lock_mode X locks rec but not gap
/****
session 1上的转为显式锁:lock_mode X locks rec but not gap
session 2等待的锁:lock mode S waiting
session 3等待的锁:lock mode S waiting
***/
session 1 rollback
session 2插入成功
session 3:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
这个时候show engine innodb status:
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost rootTABLE LOCK table `test`.`lingluo` trx id 4F3D6D24 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode SRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode SRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock_mode X insert intentionRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S locks gap before rec
死锁信息:
------------------------LATEST DETECTED DEADLOCK------------------------130701 20:47:57*** (1) TRANSACTION:TRANSACTION 4F3D6D24, ACTIVE 13 sec inserting, thread declared inside InnoDB 1mysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root updateinsert into lingluo values(100214,215,215,312)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock_mode X insert intention waiting*** (2) TRANSACTION:TRANSACTION 4F3D6F33, ACTIVE 11 sec inserting, thread declared inside InnoDB 1mysql tables in use 1, locked 14 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root updateinsert into lingluo values(100215,215,215,312)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock_mode X insert intention waiting*** WE ROLL BACK TRANSACTION (2)
原因:
s1 , type_mode=1059 //s2为s1转换隐式锁为显式锁
s2, type_mode=1282 //检查重复键,需要加共享锁,被s1 block住,等待S锁
s3, type_mode=1282 // 被s1 block住,等待S锁
s1, type_mode=547 //s1回滚,删除记录,lock_update_delete锁继承,
s2, type_mode=546 //创建s锁 LOCK_GAP | LOCK_REC | LOCK_S
s3, type_mode=546 //创建s锁 LOCK_GAP | LOCK_REC | LOCK_S
s2, type_mode=2819 // LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION
s3, type_mode=2819 // LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION
当s1回滚后,s2和s3获得s锁,但随后s2和s3又先后请求插入意向锁,因此锁队列为:
s2(S GAP)<—s3(S GAP)<—s2(插入意向锁)<–s3(插入意向锁) s3(s锁),s2(x锁),s3(x锁)形成死锁。
这样的死锁不光出现在unique key,还包括primary key(unique key的特殊形式)
印风的博客里有更详细的代码级别的记录,有兴趣的可以看下
另外如果:deadlock off的话,即使session 1rollback了,session 2和session 3还是处于等待的状态,除非超过了innodb_lock_wait_timeout的时间,报
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这个情况和官方的这篇文章是一样的http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html
- insert 死锁
- 有趣的insert死锁
- 死锁: delete+insert不存在PK
- mysql中insert...select引发的死锁
- insert产生死锁dead lock的实验
- mysql insert into ..select 发生死锁问题。
- 两个INSERT发生死锁原因剖析
- mysql 并发 insert 引起的死锁
- mysql中 insert …select …带来的死锁问题
- mysql中 insert …select …带来的死锁问题
- INSERT ... ON DUPLICATE KEY UPDATE产生death lock死锁原理
- Mysql 并发引起的死锁问题(INSERT ... ON DUPLICATE KEY UPDATE 死锁)
- 死锁
- 死锁
- 死锁
- 死锁
- 死锁
- 死锁
- Linux设备驱动--LCD平台设备与驱动(smdk2440)
- 8.css可见性
- 关于vue2.0的一些文章
- Linux入门
- 关于Lua的Unity UI面向过程编程模板
- insert 死锁
- Maven私服软件Nexus
- JQ复选框,选择后按钮触发后出现图片,可同时出现多个图片
- 51nod--1098最小方差
- Spring 体系结构(Spring 概述 二)
- 接竹竿
- 在亚马逊AWS(ubuntu)服务器搭建git,用git从客户端上传代码到服务器
- 2017多校训练赛第一场 HDU 6035 Colorful Tree (dfs+正序统计)
- PAT乙级 1011. A+B和C (15)