mysql保持并等待相同的锁 [英] mysql holds and waiting for the same lock
问题描述
我正在使用 innoDB 开发 mysql5.6.34 。
发生死锁，我跟随 show engine innodb status 跟随。我不知道死锁是怎么发生的，为什么 TRANSACTION-2 保持并等待相同的X锁，然后为什么 ROLLBACK 吗？
日志：
I'm working on mysql5.6.34 with innoDB.
There is a deadlock happened and I get following with show engine innodb status. I don't know how the deadlock happened, and why the TRANSACTION-2 holds and waiting for the same X lock, and then ROLLBACK it?
logs:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-08-15 05:58:56 7fdff5872700
*** (1) TRANSACTION:
TRANSACTION 81567872, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 455326, OS thread handle 0x7fdff9083700, query id 255309181 10.8.201.34 slnbdata update
INSERT INTO XXX
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8065 page no 11084 n bits 192 index `PRIMARY` of table `XXX` trx id 81567872 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 81567879, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 455338, OS thread handle 0x7fdff5872700, query id 255309187 10.8.201.34 slnbdata update
INSERT INTO XXX
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8065 page no 11084 n bits 192 index `PRIMARY` of table `XXX` trx id 81567879 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 8065 page no 11084 n bits 192 index `PRIMARY` of table `XXX` trx id 81567879 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)
------------
TRANSACTIONS
------------
There do have a query before the insert:
SELECT
pk_1,
max(pk_2)
FROM
table
WHERE
pk_1 IN (...)
GROUP BY
pk_1
but no queries between each insert.
And let me correct my reply above, the insert statement is:
insert into table_name(pk_1,pk_2 ...) values (1,1_1 ...) and insert into table_name(pk_1,pk_2 ...) values (2,2_1 ...)
We use foreach of mybatis like this:
<insert id="save">
<foreach collection="list" item="item" separator=";">
INSERT INTO ...
CREATE TABLE `customer_address_info` (
`customer_no` char(10) NOT NULL,
`addr_index` int(1) unsigned NOT NULL,
`addr_type` tinyint(1) NOT NULL,
`province_code` char(6) DEFAULT NULL,
`province_name` varchar(20) DEFAULT NULL,
`city_code` char(6) DEFAULT NULL,
`city_name` varchar(50) DEFAULT NULL,
`county_code` char(6) DEFAULT NULL,
`county_name` varchar(100) DEFAULT NULL,
`zip_code` char(6) DEFAULT NULL,
`detail` varchar(100) NOT NULL,
`status` tinyint(4) unsigned NOT NULL,
`create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`create_user` varchar(30) NOT NULL,
`modify_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`modify_user` varchar(30) DEFAULT NULL,
PRIMARY KEY (`customer_no`,`addr_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
推荐答案
正如我评论的那样，发布的信息不足以查看完整图片并了解真正的原因。我将只分享我的两个美分。
As I commented, the posted information is not enough to see the full picture and know the true reason. I will just share my two cents.
Show ENGINE INNODB STATUS状态表明每个转换已锁定两行并具有两个未完成的提交更改（2行锁定，撤消日志条目2），因此同一事务中应该有其他语句，但未显示。
The Show ENGINE INNODB STATUS indicates that each transation has locked two rows and has two pending committed changes (2 row lock(s), undo log entries 2), so there should be other statements in the same transaction, which is not showing.
事务1正在等待IX锁定，该锁定由X锁定阻止由交易2持有；事务2正在等待事务1持有的IX锁。
Transaction 1 is waiting for IX lock which prevents by the X lock hold by Transaction 2; Transaction 2 is waiting for IX lock which holds by Transaction 1.
可以通过从表中选择*获取IX以进行更新。 OP添加的select语句是一个简单的选择，不需要锁定。
A IX could be acquired by select * from table for update. The select statement added by OP is a simple select and won't require lock.
由于您的隔离级别是REPEATABLE_READ（在MySQL中是默认设置），因此在交易期间将保留在交易期间获取的每个锁，因此您需要分析来自事务的开始，直到insert语句查看可能获得的锁为止。
Since your isolation level is REPEATABLE_READ, which is default in MySQL, every lock acquired during a transaction is held for the duration of the transaction, so you need analysis the queries from the start of the transaction until the insert statement to see the possible locks acquired.
这篇关于mysql保持并等待相同的锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
