**Preface**
Our application runs several threads that execute "DELETE" queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same rows from separate threads. However, per documentation MySQL uses so-called 'next-key' lock for DELETE statements, which locks both matching key and some gap. This thing leads to dead-locks and the only solution that we've found is to use "READ COMMITTED" isolation level.
**The Problem**
Problem arises when executing complex DELETE statements with JOINS of huge tables. In a particular case we have an table with warnings that has only two rows, but the query needs to drop all warnings that belong to some particular entities from two separate INNER JOINed tables. The query is as follows:
DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=? AND dp.dirty_data=1
When the day_position table is large enough (in my test case there are 1448 rows) then any transaction even with "READ COMMITTED" isolation mode blocks **entire** 'proc_warnings' table.
The issue is always reproduced on this sample data -
http://yadi.sk/d/QDuwBtpW1BxB9 both in MySQL 5.1 (checked on 5.1.59) and MySQL 5.5 (checked on MySQL 5.5.24). Queries per transactions are as follows:
* Transaction 1
set transaction isolation level read committed;
set autocommit=0;
begin;
DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;
* Transaction 2
set transaction isolation level read committed;
set autocommit=0;
begin;
DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1;
One of them always fails with 'Lock wait timeout exceeded...' error. The 'information_schema.innodb_trx' contains following rows:
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_wait | trx_mysql_thread_id | trx_query |
| '1A2973A4' | 'LOCK WAIT' | '2012-12-12 20:03:25' | '1A2973A4:0:3172298:2' | '2012-12-12 20:03:25' | '2' | '3089' | 'DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1' |
| '1A296F67' | 'RUNNING' | '2012-12-12 19:58:02' | NULL | NULL | '7' | '3087' | NULL |
'information_schema.innodb_locks'
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4' | 'X' | 'RECORD' | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67' | 'X' | 'RECORD' | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
As I can see both queries wants an exclusive 'X' lock on a row with primary key = 53. However, neither of them must delete rows from 'proc_warnings' table. I just don't understand why the index is blocked.
Further investigation leads to run 'EXPLAIN' over the similar SELECT query. It shows that query optimizer doesn't use index to query 'proc_warnings' table and that's the only reason I can imagine why it blocks the entire primary key index.
The only solution that we can see for now is increase the default lock wait timeout from 50 seconds to 500 seconds to let the thread finish cleaning up. Then keep fingers crossed.
Any help appreciated.