How would this deadlock happens?

Question

Recently I am handing a SQL issue, I have two transaction, the transaction A first got the Next-Key Locks, and the transaction B tried to get the same lock, so it was waiting, then the transaction A tried to get Insert Intention Locks, so the deadlock happened. But I am confuse that why would this happens?

Here is my table structure:

CREATE TABLE `changeset` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT',    
  `userId` int(10) NOT NULL COMMENT,    
  `documentId` varchar(20) NOT NULL,    
  `memberId` bigint(13) NOT NULL,    
  `createTime` bigint(13) NOT NULL,    
  `version` bigint(13) NOT NULL COMMENT,    
  `changesets` mediumtext,    
  PRIMARY KEY (`id`),    
  UNIQUE KEY `uniq_documentId_version` (`documentId`,`version`) USING BTREE    
) ENGINE=InnoDB AUTO_INCREMENT=85771623 DEFAULT CHARSET=utf8

And here is my deadlock log:

(1) TRANSACTION:

TRANSACTION 22640, ACTIVE 66 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 209, OS thread handle 123145559986176, query id 6204 localhost root Sending data

select * from changeset where documentId = '7oO5C_v' and version >= 13 for update

(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 107 page no 15 n bits 704 index uniq_documentId_version of table test.changeset trx id 22640 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 7; hex 3976735431644a; asc 9vsT1dJ;;

1: len 8; hex 8000000000000000; asc ;;

2: len 4; hex 051cbef7; asc ;;

(2) TRANSACTION:

TRANSACTION 22639, ACTIVE 95 sec inserting

mysql tables in use 1, locked 1

6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1

MySQL thread id 212, OS thread handle 123145561657344, query id 6210 localhost root update insert into changeset values (0, 9, '7oO5C_v', 814, 1, 13, 'x')

(2) HOLDS THE LOCK(S):

RECORD LOCKS space id 107 page no 15 n bits 704 index uniq_documentId_version of table test.changeset trx id 22639 lock_mode X

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 7; hex 3976735431644a; asc 9vsT1dJ;;

1: len 8; hex 8000000000000000; asc ;;

2: len 4; hex 051cbef7; asc ;;

(2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 107 page no 15 n bits 704 index uniq_documentId_version of table test.changeset trx id 22639 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 7; hex 3976735431644a; asc 9vsT1dJ;;

1: len 8; hex 8000000000000000; asc ;;

2: len 4; hex 051cbef7; asc ;;

WE ROLL BACK TRANSACTION (1)

Might also be handy for us to see the code that causes the deadlock? At the moment it looks like quite a simple "one transaction has started reading and another transaction is trying to write to the same data area being read"? — Caius Jard
What is the isolation level? From the log, you have two long running transactions, the transaction is as simple as you described? — Jacob

Rena Rena · Accepted Answer · 2018-12-29T07:26:10

0

votes

Have you used "(nolock) " in your selects?

Select * From Table with (nolock)

How would this deadlock happens?

Here is my table structure:

And here is my deadlock log:

1 Answers