MySQL Deadlock issues, 2 transaction updating the same row

LATEST DETECTED DEADLOCK
------------------------
2022-12-27 04:37:57 2bde9a202700
*** (1) TRANSACTION:
TRANSACTION 346768561314, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 376, 5 row lock(s), undo log entries 1
MySQL thread id 457904353, OS thread handle 0x2bdb9ae41700, query id 427241339603 xxx.xx.xx.xx xxxx updating
update merchant_bank set balance=balance+200.00 where id=22267
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1695 page no 602 n bits 44 index `PRIMARY` of table `xxxx`.`merchant_bank` trx id 346768561314 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
 0: len=8; bufptr=0x2bb8785ee142; hex= 80000000000056fb; asc       V ;;
 1: len=6; bufptr=0x2bb8785ee14a; hex= 0050bcf55ace; asc  P  Z ;;
 2: len=7; bufptr=0x2bb8785ee150; hex= 1300bb24ca2dc5; asc    $ - ;;
 3: len=20; bufptr=0x2bb8785ee157; hex= 5349414d20436f6d6d65726369616c2042616e6b; asc SIAM Commercial Bank;;
 4: len=0; bufptr=0x2bb8785ee16b; hex= ; asc ;;
 5: len=3; bufptr=0x2bb8785ee16b; hex= 544842; asc THB;;
 6: len=10; bufptr=0x2bb8785ee16e; hex= 39343332353934383135; asc 9432594815;;
 7: len=15; bufptr=0x2bb8785ee178; hex= 4d697373205061746368616e696461; asc Miss Patchanida;;
 8: len=1; bufptr=0x2bb8785ee187; hex= 44; asc D;;
 9: len=1; bufptr=0x2bb8785ee188; hex= 42; asc B;;
 10: len=1; bufptr=0x2bb8785ee189; hex= 45; asc E;;
 11: len=1; bufptr=0x2bb8785ee18a; hex= 41; asc A;;
 12: len=5; bufptr=0x2bb8785ee18b; hex= 8000000000; asc      ;;
 13: len=5; bufptr=0x2bb8785ee190; hex= 8000000000; asc      ;;
 14: len=7; bufptr=0x2bb8785ee195; hex= 8000001683043b; asc       ;;;
 15: len=5; bufptr=0x2bb8785ee19c; hex= 8000000000; asc      ;;
 16: len=5; bufptr=0x2bb8785ee1a1; hex= 8000000000; asc      ;;
 17: len=5; bufptr=0x2bb8785ee1a6; hex= 8000000000; asc      ;;
 18: len=5; bufptr=0x2bb8785ee1ab; hex= 8000000000; asc      ;;
 19: len=9; bufptr=0x2bb8785ee1b0; hex= 800000000000000000; asc          ;;
 20: len=9; bufptr=0x2bb8785ee1b9; hex= 800000000000000000; asc          ;;
 21: len=8; bufptr=0x2bb8785ee1c2; hex= 5343423030353934; asc SCB00594;;
 22: len=1; bufptr=0x2bb8785ee1ca; hex= 01; asc  ;;
 23: SQL NULL;
 24: len=4; bufptr=0x2bb8785ee1cb; hex= 80000002; asc     ;;
 25: len=4; bufptr=0x2bb8785ee1cf; hex= 80000001; asc     ;;
 26: len=4; bufptr=0x2bb8785ee1d3; hex= 63928782; asc c   ;;
 27: len=5; bufptr=0x2bb8785ee1d7; hex= 616368656e; asc achen;;
 28: len=4; bufptr=0x2bb8785ee1dc; hex= 63a8ac41; asc c  A;;
 29: len=5; bufptr=0x2bb8785ee1e0; hex= 616368656e; asc achen;;
 30: len=8; bufptr=0x2bb8785ee1e5; hex= 80000000000101d3; asc         ;;
 31: len=8; bufptr=0x2bb8785ee1ed; hex= 80000000000000a8; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 346768561263, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 376, 5 row lock(s), undo log entries 1
MySQL thread id 457904551, OS thread handle 0x2bde96706700, query id 427241339521 xxx.xx.xx.xx xxxx updating
update merchant_bank set balance=balance+100.00 where id=22267
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1695 page no 602 n bits 44 index `PRIMARY` of table `xxx`.`merchant_bank` trx id 346768561263 lock mode S locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
 0: len=8; bufptr=0x2bb8785ee142; hex= 80000000000056fb; asc       V ;;
 1: len=6; bufptr=0x2bb8785ee14a; hex= 0050bcf55ace; asc  P  Z ;;
 2: len=7; bufptr=0x2bb8785ee150; hex= 1300bb24ca2dc5; asc    $ - ;;
 3: len=20; bufptr=0x2bb8785ee157; hex= 5349414d20436f6d6d65726369616c2042616e6b; asc SIAM Commercial Bank;;
 4: len=0; bufptr=0x2bb8785ee16b; hex= ; asc ;;
 5: len=3; bufptr=0x2bb8785ee16b; hex= 544842; asc THB;;
 6: len=10; bufptr=0x2bb8785ee16e; hex= 39343332353934383135; asc 9432594815;;
 7: len=15; bufptr=0x2bb8785ee178; hex= 4d697373205061746368616e696461; asc Miss Patchanida;;
 8: len=1; bufptr=0x2bb8785ee187; hex= 44; asc D;;
 9: len=1; bufptr=0x2bb8785ee188; hex= 42; asc B;;
 10: len=1; bufptr=0x2bb8785ee189; hex= 45; asc E;;
 11: len=1; bufptr=0x2bb8785ee18a; hex= 41; asc A;;
 12: len=5; bufptr=0x2bb8785ee18b; hex= 8000000000; asc      ;;
 13: len=5; bufptr=0x2bb8785ee190; hex= 8000000000; asc      ;;
 14: len=7; bufptr=0x2bb8785ee195; hex= 8000001683043b; asc       ;;;
 15: len=5; bufptr=0x2bb8785ee19c; hex= 8000000000; asc      ;;
 16: len=5; bufptr=0x2bb8785ee1a1; hex= 8000000000; asc      ;;
 17: len=5; bufptr=0x2bb8785ee1a6; hex= 8000000000; asc      ;;
 18: len=5; bufptr=0x2bb8785ee1ab; hex= 8000000000; asc      ;;
 19: len=9; bufptr=0x2bb8785ee1b0; hex= 800000000000000000; asc          ;;
 20: len=9; bufptr=0x2bb8785ee1b9; hex= 800000000000000000; asc          ;;
 21: len=8; bufptr=0x2bb8785ee1c2; hex= 5343423030353934; asc SCB00594;;
 22: len=1; bufptr=0x2bb8785ee1ca; hex= 01; asc  ;;
 23: SQL NULL;
 24: len=4; bufptr=0x2bb8785ee1cb; hex= 80000002; asc     ;;
 25: len=4; bufptr=0x2bb8785ee1cf; hex= 80000001; asc     ;;
 26: len=4; bufptr=0x2bb8785ee1d3; hex= 63928782; asc c   ;;
 27: len=5; bufptr=0x2bb8785ee1d7; hex= 616368656e; asc achen;;
 28: len=4; bufptr=0x2bb8785ee1dc; hex= 63a8ac41; asc c  A;;
 29: len=5; bufptr=0x2bb8785ee1e0; hex= 616368656e; asc achen;;
 30: len=8; bufptr=0x2bb8785ee1e5; hex= 80000000000101d3; asc         ;;
 31: len=8; bufptr=0x2bb8785ee1ed; hex= 80000000000000a8; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1695 page no 602 n bits 44 index `PRIMARY` of table `xxxx`.`merchant_bank` trx id 346768561263 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
 0: len=8; bufptr=0x2bb8785ee142; hex= 80000000000056fb; asc       V ;;
 1: len=6; bufptr=0x2bb8785ee14a; hex= 0050bcf55ace; asc  P  Z ;;
 2: len=7; bufptr=0x2bb8785ee150; hex= 1300bb24ca2dc5; asc    $ - ;;
 3: len=20; bufptr=0x2bb8785ee157; hex= 5349414d20436f6d6d65726369616c2042616e6b; asc SIAM Commercial Bank;;
 4: len=0; bufptr=0x2bb8785ee16b; hex= ; asc ;;
 5: len=3; bufptr=0x2bb8785ee16b; hex= 544842; asc THB;;
 6: len=10; bufptr=0x2bb8785ee16e; hex= 39343332353934383135; asc 9432594815;;
 7: len=15; bufptr=0x2bb8785ee178; hex= 4d697373205061746368616e696461; asc Miss Patchanida;;
 8: len=1; bufptr=0x2bb8785ee187; hex= 44; asc D;;
 9: len=1; bufptr=0x2bb8785ee188; hex= 42; asc B;;
 10: len=1; bufptr=0x2bb8785ee189; hex= 45; asc E;;
 11: len=1; bufptr=0x2bb8785ee18a; hex= 41; asc A;;
 12: len=5; bufptr=0x2bb8785ee18b; hex= 8000000000; asc      ;;
 13: len=5; bufptr=0x2bb8785ee190; hex= 8000000000; asc      ;;
 14: len=7; bufptr=0x2bb8785ee195; hex= 8000001683043b; asc       ;;;
 15: len=5; bufptr=0x2bb8785ee19c; hex= 8000000000; asc      ;;
 16: len=5; bufptr=0x2bb8785ee1a1; hex= 8000000000; asc      ;;
 17: len=5; bufptr=0x2bb8785ee1a6; hex= 8000000000; asc      ;;
 18: len=5; bufptr=0x2bb8785ee1ab; hex= 8000000000; asc      ;;
 19: len=9; bufptr=0x2bb8785ee1b0; hex= 800000000000000000; asc          ;;
 20: len=9; bufptr=0x2bb8785ee1b9; hex= 800000000000000000; asc          ;;
 21: len=8; bufptr=0x2bb8785ee1c2; hex= 5343423030353934; asc SCB00594;;
 22: len=1; bufptr=0x2bb8785ee1ca; hex= 01; asc  ;;
 23: SQL NULL;
 24: len=4; bufptr=0x2bb8785ee1cb; hex= 80000002; asc     ;;
 25: len=4; bufptr=0x2bb8785ee1cf; hex= 80000001; asc     ;;
 26: len=4; bufptr=0x2bb8785ee1d3; hex= 63928782; asc c   ;;
 27: len=5; bufptr=0x2bb8785ee1d7; hex= 616368656e; asc achen;;
 28: len=4; bufptr=0x2bb8785ee1dc; hex= 63a8ac41; asc c  A;;
 29: len=5; bufptr=0x2bb8785ee1e0; hex= 616368656e; asc achen;;
 30: len=8; bufptr=0x2bb8785ee1e5; hex= 80000000000101d3; asc         ;;
 31: len=8; bufptr=0x2bb8785ee1ed; hex= 80000000000000a8; asc         ;;

------------
TRANSACTIONS
------------
Trx id counter 346768916240
Purge done for trx's n:o < 346741158134 undo n:o < 0 state: running
History list length 7075704

Desc:

MySQL 5.6

DB isolation = RC

update ... where id, id is primary key

I can't figure where is the share lock append by transaction 2

Also it's weird in my deadlock log i cannot find "*** WE ROLL BACK TRANSACTION (2)"

What i have try:

Find out code layer any share lock query, unfortunely can't find any.
Try to simulate simple update query in own project with high concurrency , cannot simulate deadlock
Try to open general log from prod, but the log is too huge and there is no trace id for me to track specific transaction.
MySQL said share lock will append when hit duplicate key , but in my code layer this merchant bank table is insert during setup and rarely to insert new record. So i can eliminate the insert, also i look into the unique key update statement from my code layer, it doesn't seem will cause deadlock.

Any tips how can i further find out what cause the deadlock issues, since i am not able to find anything from code and mysql deadlock info.

Appreciate for your help ! Thank you!

Solution

There is some scenario mySQL will issues Share lock

it's same with this scenario that happened in our code. (refer to below)

transaction 1 insert into table B which have foreign key reference to table A -(at this time S lock granted to the table A index)

transaction 2 update table A same key - BLOCK, because S lock holding in transaction 1

transaction 1 itself update table A same key - deadlock deadlock occurred here

Solution:

set null to the foreign key entity object that reference to that table before call hibernate save
perform hibernate.save method, after it return back the entity , immediately set back the foreign key object. so it won't affect our code flow
when transaction is commit, persist object will issues update statement for the updated foreign key object.

I tested in prod env and all deadlock are resolve now.