未加星标

Mysqlinsert锁异常 mysql mysql数据库 Mysqlinsert 锁异常

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

mysqlinsert锁异常,今天自己在mysql库中遇到一个insert报锁等待超时的异常,再网上搜了下,查到这篇文章,和自己场景一样,同样是在表中存在联合主键,虽然对问题有了解释,但未找到解决办法。

一个很有意思的死锁,拿来记录下:

环境:deadlock on

事务隔离级别: read commited

表结构:

[email protected] 08:34:01>show create table lingluo\G
*************************** 1. row ***************************
Table: lingluo
Create 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=gbk
1 row in set (0.00 sec)

session 1:

[email protected] 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)
[email protected] 08:46:38>begin;
Query OK, 0 rows affected (0.00 sec)
[email protected] 08:47:04>insert into lingluo values(100213,215,215,312);
Query OK, 1 row affected (0.00 sec)

session 2:

[email protected] 08:46:02>begin;
Query OK, 0 rows affected (0.00 sec)
[email protected] 08:47:20>insert into lingluo values(100214,215,215,312);
Query OK, 1 row affected (12.77 sec)

session3:

[email protected] 08:46:24>begin;
Query OK, 0 rows affected (0.00 sec)
[email protected] 08:47:23>insert into lingluo values(100215,215,215,312);

session 1 rollback前:

---TRANSACTION 4F3D6F33, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root update
insert 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 IX
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
---TRANSACTION 4F3D6D24, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root update
insert 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 IX
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
---TRANSACTION 4F3D4423, ACTIVE 33 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 18124692, OS thread handle 0x7fe73c89a700, query id 1435651549 localhost root
TABLE LOCK table `test`.`lingluo` trx id 4F3D4423 lock mode IX
RECORD 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 1
MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root
TABLE LOCK table `test`.`lingluo` trx id 4F3D6D24 lock mode IX
RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S
RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S
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
RECORD 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 1
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root update
insert 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 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root update
insert 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。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLInnoDBTI数据CTIAU数据库RYRIM
分页:12
转载请注明
本文标题:Mysqlinsert锁异常 mysql mysql数据库 Mysqlinsert 锁异常
本站链接:http://www.codesec.net/view/530888.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(56)