Description:
Our application executes several concurrent DELETE statements with INNER JOINs on same table. In default REPEATABLE READ transaction mode it results in dead locks. So we've decided to move to the READ COMMITTED, which is as told by documentation avoids using of gap-locks by using of simple record-locks. Also it's told that locks on all non-matching rows are released right after WHERE condition is evaluated. I believe this means that in case no rows matched after statement completes all row-level locks on a table are released.
In our case we have one small child table with couple of rows related with the parent table with large number of rows. The relation isn't defined as a foreign key, but the column used for relation has index. However, MySQL query optimizer decides not to use the index, but iterate through couple of child table rows and thus lock them. This lock never gets released before transaction is committed or rolled back.
The application is written in java, uses official mysql JDBC driver v. 5.1.12, hibernate ORM to execute queries. The issue reproduces from the MySQL command-line tool.
How to repeat:
Attached is the simple SQL dump file for three tables, which are being joined in concurrent DELETE statements. To reproduce the problem the following queries must be ran in two separate transactions:
* 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;
Suggested fix:
None. The only workaround is to increase the innodb_lock_wait_timeout parameter up to 500 seconds or so. Then keep fingers crossed.
Description: Our application executes several concurrent DELETE statements with INNER JOINs on same table. In default REPEATABLE READ transaction mode it results in dead locks. So we've decided to move to the READ COMMITTED, which is as told by documentation avoids using of gap-locks by using of simple record-locks. Also it's told that locks on all non-matching rows are released right after WHERE condition is evaluated. I believe this means that in case no rows matched after statement completes all row-level locks on a table are released. In our case we have one small child table with couple of rows related with the parent table with large number of rows. The relation isn't defined as a foreign key, but the column used for relation has index. However, MySQL query optimizer decides not to use the index, but iterate through couple of child table rows and thus lock them. This lock never gets released before transaction is committed or rolled back. The application is written in java, uses official mysql JDBC driver v. 5.1.12, hibernate ORM to execute queries. The issue reproduces from the MySQL command-line tool. How to repeat: Attached is the simple SQL dump file for three tables, which are being joined in concurrent DELETE statements. To reproduce the problem the following queries must be ran in two separate transactions: * 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; Suggested fix: None. The only workaround is to increase the innodb_lock_wait_timeout parameter up to 500 seconds or so. Then keep fingers crossed.