Bug #67924 Row lock isn't released after evaluating WHERE condition in READ COMMITTED
Submitted: 17 Dec 2012 15:01 Modified: 17 Dec 2012 18:39
Reporter: Vitaly Litvak Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.59, 5.5.24 OS:Linux
Assigned to: CPU Architecture:Any
Tags: innodb, lock, read committed, row
Triage: Needs Triage: D3 (Medium)

[17 Dec 2012 15:01] Vitaly Litvak
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.
[17 Dec 2012 15:02] Vitaly Litvak
Data for three tables, where issue always reproduces

Attachment: _20121212_1939.sql (text/x-sql), 52.92 KiB.

[17 Dec 2012 18:39] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Dec 2012 18:40] Sveta Smirnova
test case for MTR

Attachment: bug67924.test (application/octet-stream, text), 52.17 KiB.