MySQL Forums
Forum List  »  General

Help for fixing update failure due to lock in a outer join table
Posted by: Tian Deng
Date: June 02, 2020 01:42AM

I have an update statement for updating data in TABLE_A with outer join (JOIN TABLE_B) clauses,
please see the following code(starts from line "TABLE_A(a transaction table)") for detail.
This SQL command is run in stored function.
When the stored function is called by multiple users (like five users) at the same time,
The following error happened (almost 100% reproducible):
Deadlock found when trying to get lock; try restarting transaction

The database is MySQL 5.7.25, and the transaction isolation level is REPEATABLE-READ (the default).

Currently to workaround this lock problem, a temporary table is used to copy all data from TABLE_B within the stored function,
but appearantly this workaround could cause performance issue, and this workaround need large amount of changes to my code, I want to avoid this situation.

Could anyone tell me what's the real problem here, is there a simpler resolution? If there is no other resolution, do you think the above workaround is a reasonable one ?


The following are tables and the problematic SQL :
TABLE_A(a transaction table)
# Field, Type, Null, Key, Default, Extra
'ID', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment'
'NAME1', 'text', 'YES', '', NULL, ''
'NAME2', 'text', 'YES', '', NULL, ''
・・・

TABLE_B(a master table)
# Field, Type, Null, Key, Default, Extra
'ID', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment'
'CD1', 'text', 'YES', '', NULL, ''
'CD2', 'text', 'YES', '', NULL, ''
'DIV', 'text', 'YES', '', NULL, ''
'NAME', 'text', 'YES', '', NULL, ''


UPDATE TABLE_A AS WK
LEFT OUTER JOIN TABLE_B AS B1
ON B1.CD1 = in_cd1
AND B1.CD2 = in_cd2
AND B1.DIV = '1'
LEFT OUTER JOIN TABLE_B AS B2
ON B2.CD1 = in_cd1
AND B2.CD2 = in_cd2
AND B2.DIV = '2'
SET
WK.NAME1 = B1.NAME ,
WK.NAME2 = B2.NAME
WHERE WK.ID = in_id;

(*)in_id、in_cd1、in_cd2 are params of stored function.


The following output is picked up from the log when running
show engine innodb status

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-26 16:28:26 0x744
*** (1) TRANSACTION:
TRANSACTION 2154405, ACTIVE 3 sec starting index read
mysql tables in use 49, locked 37
LOCK WAIT 16 lock struct(s), heap size 1136, 29 row lock(s), undo log entries 15
MySQL thread id 132281, OS thread handle 212, query id 6289488 localhost 127.0.0.1 xxxx Creating sort index

UPDATE TABLE_A AS WK
LEFT OUTER JOIN TABLE_B AS B1
ON B1.CD1 = in_cd1
AND B1.CD2 = in_cd2
AND B1.DIV = '1'
LEFT OUTER JOIN TABLE_B AS B2
ON B2.CD1 = in_cd1
AND B2.CD2 = in_cd2
AND B2.DIV = '2'
SET
WK.NAME1 = B1.NAME ,
WK.NAME2 = B2.NAME
WHERE WK.ID = in_id;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19811 page no 59 n bits 360 index TABLE_B_IDX of table `xxxx`.`TABLE_B` trx id 2154405 lock_mode X waiting
Record lock, heap no 182 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 6; hex 313039313039; asc xxxx;;
1: len 6; hex 303439303439; asc xxxx;;
2: len 8; hex 8000000000000747; asc G;;

*** (2) TRANSACTION:
TRANSACTION 2154422, ACTIVE 2 sec starting index read, thread declared inside InnoDB 4989
mysql tables in use 100, locked 72
443 lock struct(s), heap size 57552, 14102 row lock(s), undo log entries 83
MySQL thread id 132292, OS thread handle 1860, query id 6295254 localhost 127.0.0.1 xxxx Creating sort index

UPDATE TABLE_A AS WK
LEFT OUTER JOIN TABLE_B AS B1
ON B1.CD1 = in_cd1
AND B1.CD2 = in_cd2
AND B1.DIV = '1'
LEFT OUTER JOIN TABLE_B AS B2
ON B2.CD1 = in_cd1
AND B2.CD2 = in_cd2
AND B2.DIV = '2'
SET
WK.NAME1 = B1.NAME ,
WK.NAME2 = B2.NAME
WHERE WK.ID = in_id;

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 19811 page no 59 n bits 360 index TABLE_B_IDX of table `xxx`.`TABLE_B` trx id 2154422 lock_mode X

Record lock, heap no 170 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 6; hex 313039313039; asc xxxx;;
1: len 6; hex 303439303337; asc xxxx;;
2: len 8; hex 800000000000073b; asc ;;;

Record lock, heap no 171 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 6; hex 313039313039; asc xxxx;;
1: len 6; hex 303439303338; asc xxxx;;
2: len 8; hex 800000000000073c; asc <;;

Record lock, heap no 182 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 6; hex 313039313039; asc xxxx;;
1: len 6; hex 303439303439; asc xxxx;;
2: len 8; hex 8000000000000747; asc G;;

Record lock, heap no 187 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 6; hex 313039313039; asc xxxx;;
1: len 6; hex 303439303534; asc xxxx;;
2: len 8; hex 800000000000074c; asc L;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
・・・・

*** WE ROLL BACK TRANSACTION (1)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.