Mysql Deadlock on update query on same row

innodbMySQL

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key).

The conflicting queries are:

UPDATE auth_user 
SET rep_score=185 
WHERE (auth_user.id = 134814)

and

UPDATE auth_user 
SET rep_score=(auth_user.rep_score - 15) 
WHERE (auth_user.id = 134814)

I could not figure out how to make sense from the following log.
The Mysql logs say the following:

------------------------ LATEST DETECTED DEADLOCK
------------------------ 130626 15:06:45
*** (1) TRANSACTION: TRANSACTION ACE6801, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 54776, OS thread handle 0x7f2a564c6700, query id 434053605 localhost root Updating UPDATE auth_user SET rep_score=185 WHERE (auth_user.id = 134814)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 81752 n bits 112 index `PRIMARY` of table `zumbl_db`.`auth_user` trx id ACE6801 lock_mode X locks rec but not gap waiting Record lock, heap no 44 PHYSICAL RECORD: n_fields 31; compact format; info bits 0  0: len 4; hex 80020e9e; asc     ;;  1: len 6; hex 00000ace67f2; asc     g ;;  2: len 7; hex 710001db6208fe; asc q   b  ;;  3: SQL NULL;  4: SQL NULL;  5: len 4; hex 80000376; asc v;;  6: len 4; hex 800004ca; asc     ;;  7: len 4; hex 800000c7; asc   ;;  8: len 4; hex 80000000; asc     ;;  9: SQL NULL;  10: len 7; hex 7a7369676e7570; asc zsignup;;  11: SQL NULL;  12: len 30; hex 70626b64663228313030302c32302c736861353132292438393836613632; asc pbkdf2(1000,20,sha512)$8986a62; (total 80 bytes);  13: len 0; hex ; asc ;;  14: len 0; hex ; asc ;;  15: len 0; hex ; asc ;;  16: len 4; hex 80000001; asc     ;;  17: len 30; hex 302e303b302e303b302e303b302e303b342e303b312e303b312e303b302e; asc
0.0;0.0;0.0;0.0;4.0;1.0;1.0;0.; (total 79 bytes);  18: SQL NULL;  19: len 4; hex 80000000; asc     ;;  20: SQL NULL;  21: SQL NULL;  22: len 4; hex 800000b9; asc     ;;  23: len 10; hex 677265656e6772617373; asc greengrass;;  24: SQL NULL;  25: len 25; hex 62656e6e7977696c6c69616d73323540676d61696c2e636f6d; asc bennywilliams25@gmail.com;;  26: len 4; hex 80000532; asc    2;;  27: len 2; hex 494e; asc IN;;  28: len 1; hex 54; asc T;;  29: len 8; hex 8000124f0109b616; asc    O    ;;  30: SQL NULL;

*** (2) TRANSACTION: TRANSACTION ACE6803, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 7 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 MySQL thread id 56630, OS thread handle 0x7f2a564f7700, query id 434053709 localhost root Updating UPDATE auth_user SET rep_score=(auth_user.rep_score - 15) WHERE (auth_user.id = 134814)
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 81752 n bits 112 index `PRIMARY` of table `zumbl_db`.`auth_user` trx id ACE6803 lock mode S locks rec but not gap Record lock, heap no 44 PHYSICAL RECORD: n_fields 31; compact format; info bits 0  0: len 4; hex 80020e9e; asc     ;;  1: len 6; hex 00000ace67f2; asc     g ;;  2: len 7; hex 710001db6208fe; asc q   b  ;;  3: SQL NULL;  4: SQL NULL;  5: len 4; hex 80000376; asc    v;;  6: len 4; hex 800004ca; asc     ;; 7: len 4; hex 800000c7; asc     ;;  8: len 4; hex 80000000; asc     ;; 9: SQL NULL;  10: len 7; hex 7a7369676e7570; asc zsignup;;  11: SQL NULL;  12: len 30; hex 70626b64663228313030302c32302c736861353132292438393836613632; asc pbkdf2(1000,20,sha512)$8986a62; (total 80 bytes);  13: len 0; hex ; asc ;;  14: len 0; hex ; asc ;;  15: len 0; hex ; asc ;;  16: len 4; hex 80000001; asc     ;;  17: len 30; hex 302e303b302e303b302e303b302e303b342e303b312e303b312e303b302e; asc
0.0;0.0;0.0;0.0;4.0;1.0;1.0;0.; (total 79 bytes);  18: SQL NULL;  19: len 4; hex 80000000; asc     ;;  20: SQL NULL;  21: SQL NULL;  22: len 4; hex 800000b9; asc     ;;  23: len 10; hex 677265656e6772617373; asc greengrass;;  24: SQL NULL;  25: len 25; hex 62656e6e7977696c6c69616d73323540676d61696c2e636f6d; asc bennywilliams25@gmail.com;;  26: len 4; hex 80000532; asc    2;;  27: len 2; hex 494e; asc IN;;  28: len 1; hex 54; asc T;;  29: len 8; hex 8000124f0109b616; asc    O    ;;  30: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 81752 n bits 112 index `PRIMARY` of table `zumbl_db`.`auth_user` trx id ACE6803 lock_mode X locks rec but not gap waiting Record lock, heap no 44 PHYSICAL RECORD: n_fields 31; compact format; info bits 0  0: len 4; hex 80020e9e; asc     ;;  1: len 6; hex 00000ace67f2; asc     g ;;  2: len 7; hex 710001db6208fe; asc q   b  ;;  3: SQL NULL;  4: SQL NULL;  5: len 4; hex 80000376; asc v;;  6: len 4; hex 800004ca; asc     ;;  7: len 4; hex 800000c7; asc   ;;  8: len 4; hex 80000000; asc     ;;  9: SQL NULL;  10: len 7; hex 7a7369676e7570; asc zsignup;;  11: SQL NULL;  12: len 30; hex 70626b64663228313030302c32302c736861353132292438393836613632; asc pbkdf2(1000,20,sha512)$8986a62; (total 80 bytes);  13: len 0; hex ; asc ;;  14: len 0; hex ; asc ;;  15: len 0; hex ; asc ;;  16: len 4; hex 80000001; asc     ;;  17: len 30; hex 302e303b302e303b302e303b302e303b342e303b312e303b312e303b302e; asc
0.0;0.0;0.0;0.0;4.0;1.0;1.0;0.; (total 79 bytes);  18: SQL NULL;  19: len 4; hex 80000000; asc     ;;  20: SQL NULL;  21: SQL NULL;  22: len 4; hex 800000b9; asc     ;;  23: len 10; hex 677265656e6772617373; asc greengrass;;  24: SQL NULL;  25: len 25; hex 62656e6e7977696c6c69616d73323540676d61696c2e636f6d; asc bennywilliams25@gmail.com;;  26: len 4; hex 80000532; asc    2;;  27: len 2; hex 494e; asc IN;;  28: len 1; hex 54; asc T;;  29: len 8; hex 8000124f0109b616; asc    O    ;;  30: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)

Best Answer

As an immediate solution, you can try and restart the victim transactions. Use the information on the deadlocks provided here to fine-tune your application to prevent them from occurring again. The following are general guidelines to avoid deadlocks:

Commit your transactions often as smaller transactions are less likely to be caught in a deadlock.
Access your tables and rows in a fixed order.
Avoid use of locks as much as you can.
While using locks, use a higher level of isolation, such as READ COMMITTED.

If you are using InnoDB as storage engine then it has deadlock detection and you can read this documentation as well How to cope up with deadlock

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks

Mysql – Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock

Can some or all of the 4 steps be replaced by a single query:

INSERT ... ON DUPLICATE KEY UPDATE ...

or maybe

INSERT IGNORE ... and check rows_affected?

It would be better if you showed us all the SQL between BEGIN and COMMIT.

Are you doing any time-consuming processing between BEGIN and COMMIT? Can any of it be moved out. The faster the transaction runs, the less likely it will deadlock.

Do you have code to rerun the transaction when it deadlocks? This a useful "last resort". (It is not yet obvious whether the particular deadlock can always be avoided.)

Best Answer

Related Solutions

Mysql – Clarification on thesql deadlock situation

SUGGESTION

Mysql – Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock

Related Question