Deadlock with single SQL statement ?
Posted by:
Maxim M
Date: April 07, 2005 11:12AM
Hi,
I see very strange behaviour - MySQL discover deadlock when one of transactions only started with single query. The close (but not exact) scenario is:
T1: SET autocommit = 0
T1: SELECT from table1 WHERE id = 10 FOR UPDATE
T1: UPDATE table1 ... WHERE id =10;
T1: SELECT from table1 WHERE id = 10 FOR UPDATE
T1: UPDATE table1 ... WHERE id =10;
T1: SELECT DISTINCT table1.id FROM table1 LEFT JOIN table2 ON table.id = table2.used_by WHERE table2.id = 1 FOR UPDATE (this query locks the row with id = 10 from table1 again)
T1: SELECT from table1 WHERE id = 10 FOR UPDATE
T2: SET autocommit = 0
T1: SELECT from table1 WHERE id = 10 FOR UPDATE
T2: SELECT from table1 WHERE id = 10 FOR UPDATE - here deadlock happens
.............
T1: SELECT from table1 WHERE id = 10 FOR UPDATE
T2: rollback
T1: commit
I expect from T2 to waiting lock, but how deadlock happens ?
How it may be possible in first query after start of transaction ?
After run of "SHOW ENGINE INNODB STATUS", next output visible in deadlock section:
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 0 36994, ACTIVE 1 sec, process no 8257, OS thread id 143376 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 728, query id 120368 localhost.localdomain 127.0.0.1
SELECT from table1 WHERE id = 10 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 300 n bits 88 index `PRIMARY` of table `qrm/table1` trx id 0 36994 lock_mode X loc
ks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 40; 1-byte offs FALSE; info bits 0
0: len 8; hex 800000000000000b; asc ;; 1: len 6; hex 000000009080; asc ;; 2: len 7; hex
........................
*** (2) TRANSACTION:
TRANSACTION 0 36992, ACTIVE 9 sec, process no 32275, OS thread id 229393 fetching rows, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
23 lock struct(s), heap size 2496, undo log entries 19
MySQL thread id 727, query id 120407 localhost.localdomain 127.0.0.1 qrm Copying to tmp table
SELECT from table1 WHERE id = 10 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 300 n bits 88 index `PRIMARY` of table `qrm/table1` trx id 0 36992 lock_mode X loc
ks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 40; 1-byte offs FALSE; info bits 0
0: len 8; hex 800000000000000b; asc ;; 1: len 6; hex 000000009080; asc ;; 2: len 7; hex 000000003f2efe;
.............................
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 300 n bits 88 index `PRIMARY` of table `qrm/table1` trx id 0 36992 lock_mode X waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 40; 1-byte offs FALSE; info bits 0
0: len 8; hex 800000000000000b; asc ;; 1: len 6; hex 000000009080; asc ;; 2: len 7; hex 000000003f2efe; asc
...........
*** WE ROLL BACK TRANSACTION (1)
Please help me to solve this problem.
Used configuration:
MySQL 4.1.9 with InnoDB (all tables)
Connector/J 3.1.6
RH Linux 8.0
Kernel version 2.4.20-24.8