MySQL Forums
Forum List  »  InnoDB

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock with single SQL statement ?
3909
April 07, 2005 11:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.