mysql锁等待 立马返回,mysql保持并等待相同的锁

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 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.

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.

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.

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.

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.