我正在努力 mysql5.6.34
与 innoDB
. 有一个僵局发生了,我跟在后面 show engine innodb status
. 我不知道僵局是怎么发生的,为什么 TRANSACTION-2
保持并等待相同的x锁,然后 ROLLBACK
是吗?
日志:
------------------------
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;
1条答案
按热度按时间wgeznvg71#
正如我所评论的,发布的信息不足以看到全貌并知道真正的原因。我只分两分钱。
show engine innodb status表示每个事务都锁定了两行,并且有两个挂起的提交更改(2行锁定,undo log entries 2),因此在同一事务中应该有其他语句,但没有显示。
事务1正在等待ix锁,ix锁阻止事务2的x锁保持;事务2正在等待事务1持有的ix锁。
ix可以通过从表中选择*来获取以进行更新。op添加的select语句是一个简单的select语句,不需要锁。
由于隔离级别是repeatable\u read,这在mysql中是默认的,因此事务期间获取的每个锁都会在事务期间保留,因此需要分析从事务开始到insert语句的查询,以查看可能获取的锁。