Mysql – Gap Locking in Read Committed isolation level in Mysql

innodbisolation-levellockingMySQLtransaction

MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where even Read Committed has to use gap locking.

READ COMMITTED
….. For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

IMHO, only record lock is enough. Can anyone explain the scenario for Gap locking and why mysql does that?

Best Answer

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement

Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

implies.

Suppose transaction T1, with the RC isolation, updates the value of a column that is part of a unique key (index). Clearly this is only possible if the new key value does not already exist. Suppose also transaction T2, also with the RC isolation, attempts to insert a new record with the key value equal to that just created by T1. Due to the RC isolation level T2 does not see the change made by T1, since it has not been committed yet, so the duplicate key error is not raised. If it weren't for a gap lock on the unique index in question, this would eventually result in two records with the same purportedly unique key.

With MySQL 8 you can see this in action using the views data_locks and data_lock_waits in performance_schema. I've created this sample table:

create table fruits (
  id int not null auto_increment primary key, 
  name varchar(10), 
  property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property) 
values ('apple', 'red'), ('apple', 'tart'), 
       ('banana', 'yellow'), ('banana', 'sweet');

In transaction 1 I update banana properties:

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

I can see that key entries with new values have gap locks set on them (these rows don't really exist until the transaction is committed, so standard X locks can't be used here):

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

In transaction 2 I now try to insert a possible duplicate:

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

The statement blocks, and I can see that it blocks on the gap lock for that unique index key (note the blocking_engine_lock_id value):

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

Similarly, if T1 deletes a row from a parent table, while T2 attempts to insert a new record to a child table referencing the row just deleted by T1 (which it still sees), the gap lock on the foreign key index prevents insertion of a potential orphan record.

Related Solutions

MySQL InnoDB locks primary key on delete even in READ COMMITTED

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.

Sql-server – Locking in “Read Committed Snapshot”

The exact behaviour of concurrent modifications depends, in part, on the access path chosen by SQL Server to locate records to change.

If SQL Server uses the clustered index to locate data to change, locks will generally be taken on clustered index keys (and/or pages, etc.). If SQL Server locates rows to change using a nonclustered index, locks will be taken on the nonclustered index. In both cases, exclusive locks are also generally taken on the clustered index before the modification is actually performed.

Anticipating locking behaviour can be a fun and educational exercise, but it often the wrong question to ask. If you are experiencing blocking on locks where it does not seem necessary, then it might be the right question, but it is a very advanced one, requiring detailed internal knowledge to fully explain. I'll show an example based on your data later on.

Most often, the real question is "which isolation level should I use?". Locks are an implementation detail, used to provide the guarantees offered by the various isolation levels. The guarantees are the important thing. You should understand the different behaviours that are possible under each isolation level, and then make an informed choice. Please refer to that link for all the details.

When modifying data, RCSI behaves the same as standard READ COMMITTED. It will block if it needs to read something that is currently locked by another session's uncommitted changes. Once the blocking session commits or rolls back its changes, the blocked update continues, reading the committed values present at the time the blocking lock was released. This behaviour is required to prevent "lost updates", which are not allowed under any isolation level supported by SQL Server.

The following demo shows that the precise blocking behaviour depends on which locks are needed according to the query plan selected by the optimizer. In some cases, the update will block, in other cases, it will not. SQL Server always respects the guarantees provided by the user's isolation level, regardless of the implementation-defined locking behaviour.

Test Table and Data

CREATE TABLE dbo.Table1
(
    PKcol integer PRIMARY KEY, 
    NonPKCol integer NULL UNIQUE, 
    col1 integer NULL
);

INSERT dbo.Table1
    (PKcol, NonPKCol, col1)
VALUES
    (1,1,0),
    (2,2,0),
    (3,3,0),
    (4,4,0),
    (5,5,0);

Uncommitted Update

On a separate connection, run:

BEGIN TRANSACTION;
UPDATE dbo.Table1 SET NonPKCol = 997 WHERE PKcol = 3;
UPDATE dbo.Table1 SET NonPKCol = 998 WHERE NonPKCol = 3;
UPDATE dbo.Table1 SET NonPKCol = 999 WHERE NonPKCol = 5;

Note the lack of a COMMIT or ROLLBACK TRANSACTION.

Test Results

-- (1) Succeeds (no conflicting locks encountered)
update table1 set col1 = col1 + 1 where PKcol < 3

-- (2) Waits for an X lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where PKcol = 3

-- (3) Waits on U lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3

-- (3) Succeeds when read access is by NONCLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(2)) where NonPKcol < 3

-- (4) Blocks on U lock for NONCLUSTERED index key NonPKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol = 3

-- (5) Blocks on U lock for nonclustered index key NonPKcol = 5
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

-- (5) Succeeds when access is by CLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(1)) where PKcol < 3 and NonPKcol = 5