Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 form 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)
share|improve this question
1  
What is the isolation level? Do these statements run in auto-commit or are they part of larger transactions? –  ypercube Sep 9 at 19:25

1 Answer 1

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:

  1. Commit your transactions often as smaller transactions are less likely to be caught in a deadlock.
  2. Access your tables and rows in a fixed order.
  3. Avoid use of locks as much as you can.
  4. 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

share|improve this answer
    
Thanks or reply, where can I get documentation for these Mysql logs so as to understand what they mean to say? –  Saurabh Kumar Jun 28 '13 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.