MySQL InnoDB locks primary key on delete even in READ COMMITTED

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).

EDIT: The linked sample data also contains schema and indexes for the query tables, reproduced here for convenience:

``
CREATE TABLE proc_warnings (
id int(11) NOT NULL AUTO_INCREMENT,
transaction_id int(10) unsigned NOT NULL,
warning varchar(2048) NOT NULL,
PRIMARY KEY (id),
KEY proc_warnings__transaction (transaction_id)
);

CREATE TABLE day_position (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
transaction_id int(10) unsigned DEFAULT NULL,
sort_index int(11) DEFAULT NULL,
ivehicle_day_id int(10) unsigned DEFAULT NULL,
dirty_data` tinyint(4) DEFAULT

I can see how READ_COMMITTED can cause this situation.

READ_COMMITTED allows for three things:

• Visibility of committed changes by other transactions using READ_COMMITTED isolation level.

• Non-Repeatable Reads : Transaction performing same retrieval with the possibility of getting a different result each time.

• Phantoms : Transactions may have rows appear where it was not visible beforehand.

This creates an internal paradigm for the transaction itself because the transaction must maintain contact with:

• InnoDB Buffer Pool (while commit is still unflushed)

• Table's Primary Key

• Possibly

• the Double Write Buffer

• Undo Tablespace

• Pictorial Representation

If two distinct READ_COMMITTED transactions are accessing the same tables/rows that are being updated in the same way, be ready to expect not a table lock, but an exclusive lock within the gen_clust_index (aka Clustered Index). Given the queries from your simplified case:

-
Transaction 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c 
  INNER JOIN parent p ON p.id = c.parent_id 
WHERE p.id = 1;

-
Transaction 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c 
  INNER JOIN parent p ON p.id = c.parent_id 
WHERE p.id = 2;

You are locking the same location in the gen_clust_index. One may say, "but each transaction has a different primary key.". Unfortunately, this is not the case in the eyes of InnoDB. It just so happens that id 1 and id 2 reside on the same page.

Look back at information_schema.innodb_locks you supplied in the Question

| 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' |

With the exception of lock_id, lock_trx_id, the rest of the lock description is identical. Since the transactions are on the same level playing field (same transaction isolation), this should indeed happen.

Believe me, I have addressed this kind of situation before. Here are my past posts on this:

• Nov 05, 2012 : How to analyse innodb status on deadlock in insert Query?

• Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

• Jun 14, 2011 : Reasons for occasionally slow queries?

• Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?

• Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

StackExchange Database Administrators Q#30382, answer score: 3