MySQL InnoDB locks primary key on delete even in READ COMMITTED

Question

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 NULL,
    PRIMARY KEY (`id`),
    KEY `day_position__trans` (`transaction_id`),
    KEY `day_position__is` (`ivehicle_day_id`,`sort_index`),
    KEY `day_position__id` (`ivehicle_day_id`,`dirty_data`)
) ;

CREATE TABLE  `ivehicle_days` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `d` date DEFAULT NULL,
    `sort_index` int(11) DEFAULT NULL,
    `ivehicle_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `ivehicle_days__is` (`ivehicle_id`,`sort_index`),
    KEY `ivehicle_days__d` (`d`)
);

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 locked. Moreover, the index is not locked either when proc_warnings table is empty or the day_position table contains fewer number of rows (i.e. one hundred rows).

Further investigation was 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.

Simplified case

Issue also can be reproduced in a simpler case when there are only two tables with couple of records, but the child table doesn't has an index on the parent table ref column.

Create parent table

CREATE TABLE `parent` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Create child table

CREATE TABLE `child` (
  `id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Fill tables

INSERT INTO `parent` (id) VALUES (1), (2);
INSERT INTO `child` (id, parent_id) VALUES (1, NULL), (2, NULL);

Test in two parallel transactions:

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;

The common part in both cases is that MySQL doesn't use indices. I believe that's the reason of lock of entire table.

Our Solution

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.

I have a question: Did you execute COMMIT in any of the transactions? — RolandoMySQLDBA, Dec 13 '12 at 16:47
Of course. The problem is that all other transactions must wait until one of them commit changes. The simple test case doesn't contain a commit statement to show how to reproduce the issue. If you run commit or rollback in non-waiting transaction it releases lock simultaneously and waiting transaction completes it's work. — vitalidze, Dec 13 '12 at 16:50
When you say MySQL does not use indices in either case, is it because there are none in the real scenario? If there are indexes could you provide the code for them? Is it possible to try any of the below posted index suggestions? If there are no indexes, and it's not possible to try adding any, then MySQL can't restrict the data set processed by each thread. If that's the case then N threads would simply multiply the server workload by N times, and it would be more efficient to just let one thread run with a parameter list like {WHERE vd.ivehicle_id IN (2, 13) AND dp.dirty_data=1;}. — JM Hicks, Dec 17 '12 at 18:56
Ok, found the indexes tucked away in the linked sample data file. — JM Hicks, Dec 17 '12 at 19:04
couple more questions: 1) how many rows does the day_position table normally contain, when it starts to run so slow that you have to bump the timeout limit to 500 sec? 2) How long does it take to run when you have only the sample data? — JM Hicks, Dec 17 '12 at 19:40
Added an answer with a more novel approach. Split the single delete statement into a stored procedure with an isolated read statement and isolated delete statement. The delete statement is dynamically generated using DELETE FROM proc_warnings WHERE IN (...) where the IN clause is the query result from the first statement. Still think that if there's much data, trying indexes that better cover will make a big difference, but this seems like it should keep the threads from blocking for any non-trivial time due to the mutually incompatible exclusive lock requests. — JM Hicks, Dec 18 '12 at 00:49
Hmmm... "two stages", a "SELECT" and then a "DELETE", SELECT "doesn't lock rows", DELETE "if check succeeds" that's exactly what was in the stored procedure that was posted here about 9 hours before this last comment. If that's the solution you accept for yourself, why not mark the one here, that was posted 9 hours before this revelation for future readers to help them realize this work-around/solution more easily. And if it's someone else's answer you mark you get 2 points to your name :-) Happy New Year. — JM Hicks, Jan 05 '13 at 19:09

score 3 · Answer 1 · edited Apr 13 '17 at 12:42

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

I've read about the things you describe in MySQL docs. But as for me the main feature of READ COMMITTED is how it deals with locks. It should release the index locks of non-matching rows, but it doesn't. — vitalidze, Dec 13 '12 at 19:45
If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement : http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlock-detection.html — RolandoMySQLDBA, Dec 13 '12 at 19:47
Please note that I mentioned the possibility of two rows existing in the same page for locking ( See Look back at information_schema.innodb_locks you supplied in the Question ) — RolandoMySQLDBA, Dec 13 '12 at 19:49
About rolling back single statement - I understand this as if single statement fails within single transaction it may still hold the locks. That's ok. My big question is why it doesn't release non-matching row locks after successfully processing DELETE statement. — vitalidze, Dec 13 '12 at 19:57
With two completing locks, one has to be rolled back. It is possible that locks may linger. WORKING THEORY : the transaction that rolled back may retry and may encounter an old lock from the previous transaction that held it. — RolandoMySQLDBA, Dec 13 '12 at 20:09

JM Hicks · Accepted Answer · 2012-12-18T00:53:43.810

NEW ANSWER (MySQL-style dynamic SQL): Ok, this one tackles the problem in the way one of the other poster's described - reversing the order in which mutually incompatible exclusive locks are acquired so that regardless of how many occur, they occur only for the least amount of time at the end of transaction execution.

This is accomplished by separating the read part of the statement into it's own select statement and dynamically generating a delete statement that will be forced to run last due to order of statement appearance, and which will affect only the proc_warnings table.

A demo is available at sql fiddle:

This link shows the schema w/ sample data, and a simple query for rows that match on ivehicle_id=2. 2 rows result, as none of them have been deleted.

This link shows the same schema, sample data, but pass a value 2 to the DeleteEntries stored program, telling the SP to delete proc_warnings entries for ivehicle_id=2. The simple query for rows returns no results as they've all been successfully deleted. The demo links only demostrate that the code works as intended to delete. The user with the proper test environment can comment on whether this solves the problem of the blocked thread.

Here is the code as well for convenience:

CREATE PROCEDURE DeleteEntries (input_vid INT)
BEGIN

    SELECT @idstring:= '';
    SELECT @idnum:= 0;
    SELECT @del_stmt:= '';

    SELECT @idnum:= @idnum+1 idnum_col, @idstring:= CONCAT(@idstring, CASE WHEN CHARACTER_LENGTH(@idstring) > 0 THEN ',' ELSE '' END, CAST(id AS CHAR(10))) idstring_col
    FROM proc_warnings
    WHERE EXISTS (
        SELECT 0
        FROM day_position
        WHERE day_position.transaction_id = proc_warnings.transaction_id
        AND day_position.dirty_data = 1
        AND EXISTS (
            SELECT 0
            FROM ivehicle_days
            WHERE ivehicle_days.id = day_position.ivehicle_day_id
            AND ivehicle_days.ivehicle_id = input_vid
        )
    )
    ORDER BY idnum_col DESC
    LIMIT 1;

    IF (@idnum > 0) THEN
        SELECT @del_stmt:= CONCAT('DELETE FROM proc_warnings WHERE id IN (', @idstring, ');');

        PREPARE del_stmt_hndl FROM @del_stmt;
        EXECUTE del_stmt_hndl;
        DEALLOCATE PREPARE del_stmt_hndl;
    END IF;
END;

This is the syntax to call the program from within a transaction:

CALL DeleteEntries(2);

ORIGINAL ANSWER (still think it's not too shabby) Looks like 2 issues: 1) slow query 2) unexpected locking behavior

As regards issue #1, slow queries are often resolved by the same two techniques in tandem query statement simplification and useful additions of or modifications to indexes. You yourself already made the connection to indexes - without them the optimizer cannot search for a limited set of rows to process, and each row from each table multiplying per extra row scanned the amount of extra work which must be done.

REVISED AFTER SEEING POST OF SCHEMA AND INDEXES: But I imagine you'll get the most performance benefit for your query by making sure you have a good index configuration. To do so, you can go for better delete performance, and possibly even better delete performance, with trade off of larger indexes and perhaps noticeably slower insert performance on the same tables to which additional index structure is added.

SOMEWHAT BETTER:

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd` (`dirty_data`, `ivehicle_day_id`)

) ;


CREATE TABLE  `ivehicle_days` (
    ...,
    KEY `ivehicle_days__vid_no_sort_index` (`ivehicle_id`)
);

REVISED HERE TOO: Since it takes as long as it does to run, I'd leave the dirty_data in the index, and I got it wrong too for sure when I placed it after the ivehicle_day_id in index order - it should be first.

But if I had my hands on it, at this point, since there must be a good amount of data to make it take that long, I'd would just go for all covering indexes just to make sure I was getting the best indexing that my troubleshooting time could buy, if nothing else to rule that part of the problem out.

BEST/COVERING INDEXES:

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd_trnsid_cvrng` (`dirty_data`, `ivehicle_day_id`, `transaction_id`)
) ;

CREATE TABLE  `ivehicle_days` (
    ...,
    UNIQUE KEY `ivehicle_days__vid_id_cvrng` (ivehicle_id, id)
);

CREATE TABLE  `proc_warnings` (

    .., /*rename primary key*/
    CONSTRAINT pk_proc_warnings PRIMARY KEY (id),
    UNIQUE KEY `proc_warnings__transaction_id_id_cvrng` (`transaction_id`, `id`)
);

There are two performance optimization goals sought by the last two change suggestions:
1) If the search keys for successively accessed tables are not the same as the clustered key results returned for the currently accessed table, we eliminate what would have been a need to make a second set of index-seek-with-scan operations on the clustered index
2) If the latter is not the case, there is still at least the possibility that the optimizer can select a more efficient join algorithm since the indexes will be keeping the required join keys in sorted order.

Your query seems about as simplified as it can be (copied here in case it is edited later):

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;

Unless of course there's something about written join order that affects the way the query optimizer proceeds in which case you could try some of the rewrite suggestions others have provided, including perhaps this one w/ index hints (optional):

DELETE FROM proc_warnings
FORCE INDEX (`proc_warnings__transaction_id_id_cvrng`, `pk_proc_warnings`)
WHERE EXISTS (
    SELECT 0
    FROM day_position
    FORCE INDEX (`day_position__id_rvrsd_trnsid_cvrng`)  
    WHERE day_position.transaction_id = proc_warnings.transaction_id
    AND day_position.dirty_data = 1
    AND EXISTS (
        SELECT 0
        FROM ivehicle_days
        FORCE INDEX (`ivehicle_days__vid_id_cvrng`)  
        WHERE ivehicle_days.id = day_position.ivehicle_day_id
        AND ivehicle_days.ivehicle_id = ?
    )
);

As regards #2, unexpected locking behavior.

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

I guess it would be the index that's locked because the row of data to be locked is in a clustered index, i.e. the single row of data itself resides in the index.

It would be locked, because:
1) according to http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

...a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

You also mentioned above:

...as for me the main feature of READ COMMITTED is how it deals with locks. It should release the index locks of non-matching rows, but it doesn't.

and provided the following reference for that:
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

Which states the same as you, except that according to that same reference there is a condition upon which a lock shall be released:

Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

Which is reiterated as well at this manual page http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

There are also other effects of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog: Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

So, we're told that the WHERE condition must be evaluated before the lock can be relased. Unfortunately we're not told when the WHERE condition is evaluated and it would probably something subject to change from one plan to another created by the optimizer. But it does tell us that lock release, is dependent somehow on performance of query execution, optimization of which as we discuss above is dependent on careful writing of the statement, and judicious use of indexes. It can also be improved by better table design but that would probably be left best to a separate question.

Moreover, the index is not locked either when proc_warnings table is empty

The database can't lock records within the index if there are none.

Moreover, the index is not locked when...the day_position table contains fewer number of rows (i.e. one hundred rows).

This could mean numerous things such as but probably not limited to: a different execution plan due to a change in statistics, a too-brief-to-be-observed-lock due to a much faster execution due to a much smaller data set/join operation.

The WHERE condition is evaluated when query completes. Isn't it? I thought that lock is released right after some of concurrent queries executes. That's the natural behavior. However, this doesn't happen. Neither of suggested queries in this thread helps to avoid the clustered index locking in the proc_warnings table. I think I'll file a bug to the MySQL. Thanks for your help. — vitalidze, Dec 17 '12 at 14:36
I wouldn't expect them to avoid the locking behavior either. I would expect it to lock because I think the documentation says that's what's expected, whether or not that's the way we would want it to process the query. I would just expect that getting rid of the performance problem will keep the concurrent query from blocking for such an obviously (500+ second timeout) long time. — JM Hicks, Dec 17 '12 at 15:02
Although your {WHERE} seems like it could be used during the join processing to restrict which rows are included in the join calculation, I don't see how your {WHERE} clause could be evaluated per locked row until the entire set of joins is computed as well. That said, for our analysis, I suspect you're right that we should suspect "The WHERE condition is evaluated when query completes". Yet that leads me to the same overall conclusion, that the performance needs to be resolved, and then the apparent degree of concurrency will increase proportionally. — JM Hicks, Dec 17 '12 at 15:13
Remember that proper indexes can potentially eliminate any full table scan that occurs on the proc_warnings table. In order for that to happen we need the query optimizer to work nicely for us, and we need our indexes, query, and data to cowork nicely with it. The parameter values have to evaluate in the end to rows in the target table which do not overlap between the two queries. The indexes need to provide the query optimizer with a means to efficiently search for those rows. We need the optimizer to realize that potential search efficiency and select such a plan. — JM Hicks, Dec 17 '12 at 15:28
If all goes well between parameter values, indexes, non-overlapping results in the proc_warnings table, and optimizer plan selection, even though locks may be generated for the duration of the time needed to execute the query for each thread, those locks, if not overlapping, will not conflict with the lock requests of the other threads. — JM Hicks, Dec 17 '12 at 15:31
If you have a reasonable set of indexes, you can also try the index hint syntax - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html. This helps if for some reason the optimizer doesn't seem to use a good index like the ones suggested. To continue the above suggestion, if after adding indexes it still takes too long, you could try {... FROM proc_warnings pw USE INDEX (pw_transaction_id_index)} — JM Hicks, Dec 17 '12 at 16:08
As I've said somewhere here there are several DELETE queries (~10) that execute in parallel transactions. DELETE from proc_warnings table is just one of them. All indices are properly defined, MySQL uses them when either proc_warnings is big enough or joined tables are not so big. I believe that this particular query works pretty fast. However, it blocks entire proc_warnings and all other concurrent transactions have to wait until all (~10) DELETEs are finished. They may took long depending on the size of tables. — vitalidze, Dec 18 '12 at 09:25
Yeah, if FORCE INDEX doesn't work, then I don't see any other way you can try to avoid a locking table scan on the small table-(barring adding a bunch of dummy records with NULL transaction_ids, ANSI NULL settings, and your existing = operator, to fool the optimizer). But I think the work-around stored program, because it forces the locking part to come only at the end, that should minimize the impact on concurrency to a noticeable degree. Are you able to give the SP a try? (fingers crossed and all that). I hope so. — JM Hicks, Dec 18 '12 at 12:42
I'll give it a try today or tomorrow. Then report results here. — vitalidze, Dec 18 '12 at 13:15

score 2 · Answer 3 · answered Dec 13 '12 at 17:09

I looked at the query and the explain. I am not sure, but have a gut feeling, that the problem is the following. Let's look at the query:

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;

The equivalent SELECT is:

SELECT pw.id
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=16 AND dp.dirty_data=1;

If you look at its explain, you will see that the execution plan starts with the proc_warnings table. That means that MySQL scans the primary key in the table and for each row checks if the condition is true, and if it is - the row is deleted. That is MySQL has to lock the whole primary key.

What you need is to invert the JOIN order, that is find all transaction ids with vd.ivehicle_id=16 AND dp.dirty_data=1 and join them on proc_warnings table.

That is you will need to patch one of the indices:

ALTER TABLE `day_position`
 DROP INDEX `day_position__id`,
 ADD INDEX `day_position__id`
   USING BTREE (`ivehicle_day_id`, `dirty_data`, `transaction_id`);

and rewrite the delete query:

DELETE pw
FROM (
  SELECT DISTINCT dp.transaction_id
  FROM ivehicle_days vd
  JOIN day_position dp ON dp.ivehicle_day_id = vd.id
  WHERE vd.ivehicle_id=? AND dp.dirty_data=1
) as tr_id
JOIN proc_warnings pw ON pw.transaction_id = tr_id.transaction_id;

Unfortunately this doesn't help, i.e. rows in proc_warnings still get locked. Thanks anyway. — vitalidze, Dec 17 '12 at 14:00

score 2 · Answer 4 · edited Dec 13 '12 at 19:12

2

When you set the transaction level without the way you do it applies the Read Committed to the next transaction only, thus (set auto commit). This mean after autocommit=0, you're not in Read Committed anymore . I would write it this way:

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

You can check what isolation level you're in by querying

SELECT @@tx_isolation;

edited Dec 13 '12 at 19:12

Paul White

83,961
28
402
634

answered Dec 13 '12 at 18:39

Franck

21
1

That's not true. Why the SET AUTOCOMMIT=0 should reset the isolation level for next transaction? I believe it starts a new transaction if none was started before (which is my case). So, to be more precise the next START TRANSACTION or BEGIN statement is not necessary. My purpose of disabling autocommit is to leave transaction opened after the DELETE statement execution. – vitalidze Dec 13 '12 at 19:39
1

@SqlKiwi this was the way to edit this post, and this was the one to comment on ;-) – jcolebrand Dec 13 '12 at 21:49

MySQL InnoDB locks primary key on delete even in READ COMMITTED

4 Answers4

Linked