I am getting some strange (and hard-to-reproduce, timing-dependent) deadlocks in my application. The especially strange part is the deadlocks often seem to be on indexes that *I am not updating!*

I am using InnoDB. I have a "workrequest" table, and a "worklogentry" table. One of worklogentry's columns is a foreign key reference to workrequest.

Here's the output of "show innodb status" for one of the deadlocks:

------------------------
LATEST DETECTED DEADLOCK
------------------------
040409 17:17:25
*** (1) TRANSACTION:
TRANSACTION 0 221332, ACTIVE 0 sec, OS thread id 2468 inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 31, query id 18333 localhost 127.0.0.1 dummy update
insert into WorkLogEntry (server, env, timestamp, level, loggingClass, message, workrequest_id, job_
id) values ('localhost', 'local_test', '2004-04-09
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 16422 n bits 72 table barracuda_test/workrequest index PRIMARY trx i
d 0 221332 lock mode S locks rec but not gap waiting
Record lock, heap no 2 RECORD: info bits 0 0: len 8; hex 8000000000000001; asc �.......;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 221333, ACTIVE 0 sec, OS thread id 1988 setting auto-inc lock, thread declared inside
InnoDB 500
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1024, undo log entries 3
MySQL thread id 29, query id 18334 localhost 127.0.0.1 dummy update
insert into WorkLogEntry (server, env, timestamp, level, loggingClass, message, workrequest_id, job_
id) values ('localhost', 'local_test', '2004-04-09
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 16422 n bits 72 table barracuda_test/workrequest index PRIMARY trx i
d 0 221333 lock_mode X locks rec but not gap
Record lock, heap no 2 RECORD: info bits 0 0: len 8; hex 8000000000000001; asc �.......;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table barracuda_test/worklogentry trx id 0 221333 lock_mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)


It looks like doing two concurrent inserts to the worklogentry table is deadlocking on the workrequest index. My question is, WHY??? I have transaction-isolation=READ_COMMITTED in my my.ini, so I would expect that (as http://dev.mysql.com/doc/mysql/en/In...tent_read.html says) it doesn't do any locking on tables that I'm not writing to. And in this case, I'M NOT WRITING to the workrequest table!

I am using autoincrement indexing for the WorkLogEntry records, but that shouldn't be affecting the workrequest index at all, should it?

Does anyone have any idea what may be going on here?

Thanks very much for any enlightenment,
cheers!
Rob Jellinghaus
robj at nimblefish dot com