Hi,
I've got a deadlock problem using InnoDB tables in MySQL 4.1.5 for win32. I have two
tables, 'jobs' and 'results', where 'results' has a foreign key 'id_job' that
references the primary key of 'jobs'. There may be more than one result for any given
job. Both tables have a single auto_increment column for their primary key. I have two
threads that process a queue of jobs; when a job is completed, I want to delete all
existing results for that job (if any) and insert the results for that job, e.g.:
Transaction 1:
START TRANSACTION;
DELETE FROM results WHERE id_job = 25920;
INSERT INTO results(result,id_job) VALUES (31.461937,25920);
COMMIT;
Transaction 2:
START TRANSACTION;
DELETE FROM results WHERE id_job = 25919;
INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919);
COMMIT;
I'm using the default isolation level. Deadlock sometimes occurs, relevant output of
SHOW INNODB STATUS is below:
------------------------
LATEST DETECTED DEADLOCK
------------------------
041006 10:06:10
*** (1) TRANSACTION:
TRANSACTION 0 147130, ACTIVE 0 sec, OS thread id 2688 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 7157, query id 30803 localhost 127.0.0.1 root updating
DELETE FROM results WHERE id_job = 25920
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results`
trx id 0 147130 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** (2) TRANSACTION:
TRANSACTION 0 147129, ACTIVE 0 sec, OS thread id 3556 inserting, thread declared
inside InnoDB 499
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 7156, query id 30799 localhost 127.0.0.1 root update
INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results`
trx id 0 147129 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results`
trx id 0 147129 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** WE ROLL BACK TRANSACTION (1)
Thanks in advance for any help.
David
---------------------------------
ALL-NEW Yahoo! Messenger - all new features - even more fun!
