InnoDB transaction locks and isolation levels in MySQL

    Hello, Habr!
    I suggest everyone who wants to remember or understand the essence of InnoDB engine locks in MySQL.

    KDPV: deadlock performed by tropical fauna

    Basic concepts

    Everyone, I think, already knows that InnoDB uses row-level locking. Depending on the isolation level, transactions can block both rows that fall into the resulting selection and all rows that were viewed during the search. For example, in REPEATABLE READ, a blocking query without using an index will require iterating over the entire table, and therefore locking all records. Therefore, remember,% username%, the correct choice of indexes directly affects the speed of locks.

    There are two basic types of locks:
    • shared lock - a shared lock, allows other transactions to read the line and put the same shared lock on it, but it does not allow changing the line or setting an exclusive lock.
    • exclusive lock - exclusive lock, prohibits other transactions from locking the line, and can also lock the line both for writing and reading, depending on the current isolation level (about which below).

    If you dig deeper, it turns out that there are 2 more types of locks, let's call them “intentions” locks. You can’t just take and block an entry in InnoDB. The intent shared and intention exclusive locks are table-level locks and only block other locks and operations on the entire LOCK TABLE table. The imposition of such a lock by a transaction only indicates the intention of the transaction to obtain the corresponding joint or exclusive row lock.

    If the lock imposed on the row does not allow the operation to be performed, then the transaction simply waits for the interlock to be released. In the case of cross-locks, you can wait a long time, this is deadlock. There are a couple of tips in the MySQL documentation.on how to avoid deadlocks.

    InnoDB imposes locks not on the data rows themselves, but on index records. One or another lock can be imposed on:
    • record lock - lock record index
    • gap lock - lock the gap between, before or after index recording
    • next-key lock - lock index entry and the gap in front of it

    Gap blocking is necessary in order to avoid phantom records when, for example, between two identical readings of a range, a neighboring transaction manages to insert a record into this range.

    Everything described above is defined implicitly by InnoDB, you only need to imagine what is happening “under the hood”.
    About exactly which requests impose locks, you can see again in the documentation .

    Well, before moving on to isolation levels, consider the concept of consistent read .
    At the time of the first request, a database data snapshot is created in the transaction (the so-called read view), which is not affected by changes in parallel transactions, but changes in the current one affect. Reading from such a snapshot is called non-blocking consistent reading. Non-blocking - because creating such a snapshot does not require hanging locks, consistent - because no disasters in the outside world (except for DROP TABLE and ALTER TABLE) will affect the cozy world of the snapshot. InnoDB can be asked to take a snapshot before the first request in the transaction, for this you need to mention this at the start of the transaction - START TRANSACTION WITH CONSISTENT SNAPSHOT.

    InnoDB Transaction Isolation Levels

    The isolation level can be changed by querying SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.

    REPEATABLE READ (default value)

    • Consistent reading (SELECT) does not block anything; it reads lines from a snapshot that is created during the first reading in a transaction. The same queries will always return the same result.
    • For block reads (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE, and DELETE, the lock will depend on the type of condition. If the condition is unique (WHERE id = 42), then only the found index record is locked (record lock). If the condition is with a range (WHERE id> 42), then the entire range is locked (gap lock or next-key lock).


    • Consistent reading does not block anything, but each time comes from a fresh snapshot.
    • Blocking read (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE, and DELETE only locks the desired index records (record lock). Thus, it is possible to insert records in a parallel flow between the indices. Gaps are only locked when checking foreign keys and duplicate keys. Also, locks of scanned lines (record locks) that do not satisfy WHERE are released immediately after WHERE processing.

    READ UNCOMMITED (the weakest level)

    • All SELECT queries are read in a non-blocking manner. Changes to a transaction in progress can be read in other transactions, and these changes can also be subsequently rolled back. This is the so-called "dirty reading" (inconsistent).
    • Otherwise, everything is the same as with READ COMMITED.

    SERIALIZABLE (most stringent level)

    • Similar to REPEATABLE READ, except for one point. If autocommit is turned off (and when the transaction starts explicitly, it is turned off), then all simple SELECT queries implicitly turn into SELECT ... LOCK IN SHARE MODE, if enabled, each SELECT goes in a separate transaction. It is used, as a rule, to turn all read requests into SELECT ... LOCK IN SHARE MODE, if this cannot be done in the application code.

    One more thing ...

    And finally, a couple of pieces mentioned in the text about which you should know.

    SELECT ... LOCK IN SHARE MODE - locks read lines for writing.
    Other sessions can read, but wait for the transaction to complete to change the affected rows. If, at the time of such a SELECT, the row was already changed by another transaction, but not yet committed, then the request waits for the transaction to complete and then reads the latest data. This construction is needed, as a rule, in order to get the latest data (regardless of the lifetime of the transaction) and at the same time make sure that no one changes them.

    SELECT ... FOR UPDATE - blocks read lines for reading. Exactly the same lock puts a normal UPDATE when it reads data for updating.

    Please note that such queries are no longer read from a snapshot, as a simple SELECT, i.e. they will see the changes committed by another transaction after the start of the current one. This is because InnoDB can only block the latest version of a row, and in a snapshot, it will not necessarily be the last.

    For example, in a scenario, read → change → write back between read and write a parallel transaction can change data, but this change will be immediately erased by the current transaction when writing back. LOCK IN SHARE MODE in this example will not allow a neighboring transaction to wedge, it will have to wait. Note that in this case, the lock will be set twice, first joint lock when reading, then exclusive when writing. Since there are two locks, there is a theoretical chance to slip a third between them and cause deadlock.

    The difference between FOR UPDATE is that it immediately sets an exclusive lock, the same as a regular UPDATE. Thus, for the script, read → change → write back, the lock will be set only once at the time of reading. This option will reduce the likelihood of deadlocks.

    Also popular now: