记一次Mysql死锁deadlock

Posted on In ,

The deadlock is caused by below two scripts running in parallel from distributed scheduled tasks:

Script 1: INSERT INTO XXX
Script 2: DELETE rv FROM XXX

Below is the deadlock lock info

TRANSACTION 421377375759984
MySQL thread id 1159233, OS thread handle 139895407183616, query id 13826020 prod-k8s-nodeXXX 100.xx.xx.xx Sending data
INSERT INTO XXX

WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 112 index run_version_UN of table XXX.xxx trx id 421377375759984 lock mode S waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 30; hex 306233636365313837323735343366616262303666356539303530313636; asc 0b3cce18727543fabb06f5e9050166; (total 32 bytes);
2: len 4; hex 80000009; asc ;;
3: len 6; hex 000000009d40; asc @;;
4: len 7; hex e7000001830275; asc u;;

TRANSACTION 265924
MySQL thread id 1159215, OS thread handle 139893116581632, query id 13826021 prod-k8s-nodeXXX 100.xx.xx.xx Sending data
DELETE rv FROM XXX

HOLDS THE LOCK(S):
RECORD LOCKS space id 248 page no 3 n bits 112 index run_version_UN of table XXX.xxx trx id 265924 lock_mode X
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 30; hex 306233636365313837323735343366616262303666356539303530313636; asc 0b3cce18727543fabb06f5e9050166; (total 32 bytes);
2: len 4; hex 80000009; asc ;;
3: len 6; hex 000000009d40; asc @;;
4: len 7; hex e7000001830275; asc u;;

WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 112 index run_version_UN of table XXX.xxx trx id 265924 lock_mode X waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 30; hex 303638333832346134316465343561376166333362653536363131626239; asc 0683824a41de45a7af33be56611bb9; (total 32 bytes);
2: len 4; hex 80000005; asc ;;
3: len 6; hex 000000009d40; asc @;;
4: len 7; hex e7000001830473; asc s;;

We can see tran2 holding the record tran1 requesting, and tran2 requesting the record that tran1 holding, which causes the deadlock.

Reproduce the case: currently our DB isolation is repeatable read

tran 1 tran 2
Tran2 is rollbacked due to blocked by tran1

To solve the issue: set transaction isolation level to Read Committed

tran1 tran2
Both two scripts executed successfully, no blocking anymore.

Scripts that used for toubleshooting:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

select * from information_schema.innodb_trx;

select * from information_schema.innodb_lock_waits

show engine innodb status;