Deadlock with identical queries

Question

I'm trying to resolve an error involving deadlocks on one of our busy tables. I've read this SO question about deadlocks and while it makes sense, the query order doesn't seem to be the cause in my case.

Here's the abbreviated output of SHOW ENGINE INNODB STATUS;:

*** (1) TRANSACTION:
TRANSACTION 1 2611184895, ACTIVE 0 sec, process no 17501, OS thread id 140516779579136 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
MySQL thread id 211935717, query id 3146186174 [SERVER A] Searching rows for update

UPDATE images_unread_comments
    SET unread = 0
    WHERE user_id = 1 AND comment_id IN(1,2,3) AND unread = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 404976 n bits 632 index `users_unread_comments` of table images_unread_comments trx id 1 2611184895 lock_mode X waiting
Record lock, heap no 558 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67d888; asc    g  ;;

*** (2) TRANSACTION:
TRANSACTION 1 2611184892, ACTIVE 0 sec, process no 17501, OS thread id 140516774520576 updating or deleting, thread declared inside InnoDB 494
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1216, 11 row lock(s), undo log entries 1
MySQL thread id 211935715, query id 3146186169 [SERVER B] Updating

UPDATE images_unread_comments
    SET unread = 0
    WHERE user_id = 1 AND comment_id IN(1,2,3) AND unread = 1
    *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 404976 n bits 632 index users_unread_comments of table images_unread_comments trx id 1 2611184892 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 555 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67daf0; asc    g  ;;

Record lock, heap no 556 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67dadb; asc    g  ;;

Record lock, heap no 557 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67d940; asc    g @;;

Record lock, heap no 558 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67d888; asc    g  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 404976 n bits 632 index users_unread_comments of table images_unread_comments trx id 1 2611184892 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 558 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 0001461a; asc   F ;; 1: len 1; hex 01; asc  ;; 2: len 6; hex 00000e67d888; asc    g  ;;

*** WE ROLL BACK TRANSACTION (1)

The thing I noticed is that the two SQL statements are identical; however one is being executed on Server A and the other on Server B. Regardless of why that is happening - why would this create a deadlock if both queries lock the same keys in the same order? Or am I misunderstanding the casue of deadlocks in the first place?

It is likely that another query which runs before this one (in one or both threads) has locked some rows. Also you cut the status report a bit short - what is the second transaction waiting for and which lock is it holding? — Vatev, Jul 04 '13 at 13:52
I should of noticed this the 1-st time... The locks are on another table (images_unread_comments). Are there any triggers or foreign keys related to the 2 tables? — Vatev, Jul 04 '13 at 14:17
@Vatev no that's my mistake when shortening the table names, they should all read `images_unread_comments`. Sorry.. — Graham, Jul 04 '13 at 14:49

score 0 · Answer 1 · answered Jul 06 '13 at 18:25

It seems that transaction 1 has performed another operation (insert?) in which it locked a gap in the index. It than waits for transaction 2 to perform the update, since 2 has locked the record with ID 1. Transaction 2 however can not proceed because transaction 1 holds a lock on the index. If you can isolate all SQL statements that are used in a transaction by this operation, we can see the exact reason for the deadlock

Deadlock with identical queries

1 Answers1