InnoDB transaction locks and isolation levels in MySQL
Hello, Habr!
I suggest everyone to remember or learn the essence of InnoDB engine locks in MySQL.
KDPV: deadlock performed by tropical fauna
')
Everyone, I think, already knows that InnoDB uses row level locks. Depending on the isolation level, transactions can be blocked either by the rows in the resulting selection or by all the rows that were searched 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 right choice of indexes directly affects the speed of the locks.
There are two basic types of locks:
If you dig deeper, it turns out that there are 2 more types of locks, let's call them locks “about intentions”. You can't just take and block an entry in InnoDB. Intentional locks and intention exclusive locks are table-level locks and block only the creation of other locks and operations on the entire table of type LOCK TABLE. The imposition of such a transaction blocking only indicates the intention of the transaction to obtain the appropriate joint or exclusive row lock.
If the lock imposed on the string does not allow the operation to be performed, then the transaction simply waits for the interlocking lock to be removed. In the case of cross locks, you can wait for a long time, this is a deadlock. MySQL documentation has a couple of tips on how to avoid deadlocks.
InnoDB locks not on the data lines themselves, but on index entries. A lock can be imposed on:
Interval locking is needed in order to avoid the appearance of phantom records when, for example, between two identical readings of a range, a neighboring transaction has time to insert a record into this range.
Everything described above is determined by InnoDB implicitly, you only need to imagine what is happening "under the hood".
About exactly what requests impose blocking, you can see again in the documentation .
Well, before moving on to the isolation levels, consider the concept of consistent read .
At the time of the first request, a transaction creates a database snapshot (so-called read view), which is not affected by changes in parallel transactions, but is affected by changes in the current one. Reading from such a snapshot is called non-blocking consistent reading. Non-blocking - because to create such a snapshot, no locking is required, consistent - because no cataclysms in the outside world (except DROP TABLE and ALTER TABLE) will affect the cozy snapshot world. InnoDB can be asked to make a snapshot and before the first request in the transaction, for this you need to mention this during the start of the transaction - START TRANSACTION WITH CONSISTENT SNAPSHOT.
The isolation level can be changed using the SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.
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 until the end of the transaction to change the affected rows. If, at the time of such a SELECT, the line is already changed by another transaction, but not yet fixed, then the request waits for the transaction to end and then reads fresh data. This design is needed, as a rule, in order to obtain the latest data (regardless of the transaction lifetime) and at the same time make sure that no one changes them.
SELECT ... FOR UPDATE - locks readable lines. Exactly the same lock puts a regular UPDATE when it reads data for an update.
Note that such queries are no longer read from a snapshot as a simple SELECT, i.e. they will see the changes recorded by another transaction after the start of the current one. This is because InnoDB can only block the latest version of the string, and in the snapshot it will not necessarily be the last.
For example, in the scenario read → change → write back between the read and write parallel transactions can change the data, but this change will immediately be overwritten by the current transaction when writing back. LOCK IN SHARE MODE in this example will not allow the next transaction to break into, she will have to wait. Note that in this case, the lock will be placed twice, first a joint lock when reading, then an exclusive lock when writing. Since there are two locks, there is a theoretical chance to slip a third between them and cause a deadlock.
The difference between FOR UPDATE is that it immediately puts an exclusive lock, the same as regular UPDATE. Thus, for the scenario, read → change → write back, the lock will be placed only once at the moment of reading. This option will reduce the likelihood of deadlocks.
I suggest everyone to remember or learn 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 locks. Depending on the isolation level, transactions can be blocked either by the rows in the resulting selection or by all the rows that were searched 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 right choice of indexes directly affects the speed of the locks.
There are two basic types of locks:
- shared lock - joint lock, allows other transactions to read a string and put on it the same joint lock, but does not allow changing the string or setting an exclusive lock.
- exclusive lock - exclusive lock, prohibits other transactions from blocking a line, and can also block a line for both 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 locks “about intentions”. You can't just take and block an entry in InnoDB. Intentional locks and intention exclusive locks are table-level locks and block only the creation of other locks and operations on the entire table of type LOCK TABLE. The imposition of such a transaction blocking only indicates the intention of the transaction to obtain the appropriate joint or exclusive row lock.
If the lock imposed on the string does not allow the operation to be performed, then the transaction simply waits for the interlocking lock to be removed. In the case of cross locks, you can wait for a long time, this is a deadlock. MySQL documentation has a couple of tips on how to avoid deadlocks.
InnoDB locks not on the data lines themselves, but on index entries. A lock can be imposed on:
- record lock - index entry lock
- gap lock - locks the gap between, before or after the index entry
- next-key lock - lock the index entry and the gap in front of it
Interval locking is needed in order to avoid the appearance of phantom records when, for example, between two identical readings of a range, a neighboring transaction has time to insert a record into this range.
Everything described above is determined by InnoDB implicitly, you only need to imagine what is happening "under the hood".
About exactly what requests impose blocking, you can see again in the documentation .
Well, before moving on to the isolation levels, consider the concept of consistent read .
At the time of the first request, a transaction creates a database snapshot (so-called read view), which is not affected by changes in parallel transactions, but is affected by changes in the current one. Reading from such a snapshot is called non-blocking consistent reading. Non-blocking - because to create such a snapshot, no locking is required, consistent - because no cataclysms in the outside world (except DROP TABLE and ALTER TABLE) will affect the cozy snapshot world. InnoDB can be asked to make a snapshot and before the first request in the transaction, for this you need to mention this during the start of the transaction - START TRANSACTION WITH CONSISTENT SNAPSHOT.
InnoDB transaction isolation levels
The isolation level can be changed using the SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.
REPEATABLE READ (default)
- Consistent reading (SELECT) does not block anything; it reads lines from a snapshot that is created when it is first read in a transaction. Same requests always return the same result.
- For blocking read (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE and DELETE blocking will depend on the type of condition. If the condition is unique (WHERE id = 42), then only the record index found is locked. If a condition with a range (WHERE id> 42), the whole range is locked (gap lock or next-key lock).
READ COMMITED
- Consistent reading does not block anything, but every time it comes from a fresh snapshot.
- Block reading (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE and DELETE blocks only the desired index record (record lock). Thus it is possible to insert a parallel stream of records in the intervals between the indices. Gaps are locked (gap lock) only when checking foreign keys and duplicate keys. Also, locks of scanned lines (record lock) that do not satisfy WHERE are removed immediately after processing WHERE.
READ UNCOMMITED (the weakest level)
- All SELECT queries are read in a non-blocking manner. Changes to an incomplete transaction can be read in other transactions, and these changes can also be subsequently rolled back. This is the so-called “dirty reading” (uncoordinated).
- Otherwise, everything is the same as with READ COMMITED.
SERIALIZABLE (the most stringent level)
- Same as REPEATABLE READ, except for one point. If autocommit is turned off (and with an explicit start of a transaction it is turned off), then all simple SELECT queries implicitly turn into SELECT ... LOCK IN SHARE MODE, if enabled, each SELECT goes into 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 until the end of the transaction to change the affected rows. If, at the time of such a SELECT, the line is already changed by another transaction, but not yet fixed, then the request waits for the transaction to end and then reads fresh data. This design is needed, as a rule, in order to obtain the latest data (regardless of the transaction lifetime) and at the same time make sure that no one changes them.
SELECT ... FOR UPDATE - locks readable lines. Exactly the same lock puts a regular UPDATE when it reads data for an update.
Note that such queries are no longer read from a snapshot as a simple SELECT, i.e. they will see the changes recorded by another transaction after the start of the current one. This is because InnoDB can only block the latest version of the string, and in the snapshot it will not necessarily be the last.
For example, in the scenario read → change → write back between the read and write parallel transactions can change the data, but this change will immediately be overwritten by the current transaction when writing back. LOCK IN SHARE MODE in this example will not allow the next transaction to break into, she will have to wait. Note that in this case, the lock will be placed twice, first a joint lock when reading, then an exclusive lock when writing. Since there are two locks, there is a theoretical chance to slip a third between them and cause a deadlock.
The difference between FOR UPDATE is that it immediately puts an exclusive lock, the same as regular UPDATE. Thus, for the scenario, read → change → write back, the lock will be placed only once at the moment of reading. This option will reduce the likelihood of deadlocks.
Source: https://habr.com/ru/post/238513/
All Articles