Generation of isolation level
In the case of serial execution, the order of data modification is fixed and predictable, while in the case of concurrent execution, data modification is unpredictable and unfixed. In order to achieve fixed and predictable data modification results in the case of concurrent execution, isolation level is generated.
So the function of isolation level is to balance the concurrent access and data consistency of database.
Four isolation levels of transactions
Not submitted for reading? Uncommitted reading can read uncommitted data. Read has been submitted for reading. For locked read (select with for update or for share), update and delete statements,? InnoDB only locks index records, not the gaps between them, so it allows new records to be inserted freely next to the locked records. ? Gap locking is only used for foreign key constraint checking and duplicate key checking. Repeatable reading can be repeated reading, and consistent reading in a transaction reads the snapshot created by the first reading of a transaction. Serializable serialize
After understanding the requirements of four isolation levels, we need to understand the locked objects (data itself &; Gap), and understand the complete set of the entire data range.
Complete combination of data range
The SQL statement judges the data range that does not need to be scanned according to the conditions (without locking);
The data range that may need to be locked according to the scanning conditions of SQL statements;
Taking a single data range as an example, a complete data range set includes: (the data range may not necessarily be composed of continuous values, but may also be composed of interval values)
1. Data has filled the entire data area: (The data area has been completely filled with no data gap)
Shaping, the data range with unique constraint on numerical value is 1 ~ 5,
Existing data 1, 2, 3, 4, 5, at this time, the data range has been completely filled;
Integer, data range 1 and 5, unique constraint on values,
Existing data 1, 5, when the data range has been completely filled; ?
2. The data has filled a part of the data area: (For the data area that is not completely filled, there is a data gap)
The data range of shaping is 1 ~ 5.
There are data 1, 2, 3, 4, 5, but because there is no unique constraint,
Therefore, the data range can continue to fill in the data of 1 ~ 5 repeatedly;
The only constrained data range is 1 ~ 5.
There are data 2 and 5. At this time, the data range is not completely filled, so 1, 3, 4 can be filled.
3. There is no data in the data area (there is a gap)
As follows:
The data range of shaping is 1 ~ 5, and there is no data in the current data range.
After understanding the composition of the complete data set, let's look at the problems caused by concurrent transactions.
Problems caused by uncontrolled concurrency
Concurrent transactions will bring some problems if they are not controlled, mainly including the following situations.
The existing data changes within the range of 1. are caused by the following reasons:
Loss of update: When multiple transactions select the same row and then update the row according to the originally selected value,
Because nothing knows the existence of other transactions, the last update will overwrite the updates made by other transactions;
Dirty reading: A transaction is modifying a record, and the record will be in an inconsistent state until the transaction is committed.
At this point, another transaction also reads the same record. If it is not controlled,
The second transaction reads these "dirty" data and does further processing according to it, which will produce the submitted data dependency.
This phenomenon is called "dirty reading".
2. Changes in the amount of data within the scope will lead to:
Non-repeatable reading: a transaction reads the previously read data again at some time after reading some data.
Only to find that the read data has changed or some records have been deleted.
This phenomenon is called "unrepeatable reading".
Phantom reading: the transaction re-reads the previously retrieved data according to the same query conditions.
However, it is found that other transactions have inserted new data that meet their query conditions. This phenomenon is called "phantom reading".
It can be simply considered that the amount of data that meets the conditions has changed.
Because uncontrolled concurrency will bring a series of problems, resulting in unsatisfactory results. Therefore, we need to control concurrency to achieve the desired result (isolation level).
Implementation of MySQL isolation level
InnoDB supports these isolation levels through locking policies.
The row lock contains:
Record lock
Index record lock, which always locks index records, even if there is no index defined in the table.
In this case, InnoDB creates a hidden clustered index and uses it for record locking.
Clearance lock
A gap lock is a lock that indexes the gap between records, or a lock before the first record or after the last record.
Gap locking is part of the trade-off between performance and concurrency.
Data areas without gaps do not need gap locks because there are no gaps.
Next key lock
Combination of record lock on index record and gap lock before index record.
Suppose that the exponent contains 10, 1 1, 13 and 20.
Possible next-key locks include the following intervals, where brackets indicate that interval endpoints are not included and brackets indicate that interval endpoints are included:
(negative infinity, 10)( 10,11) (113) (13,20).
Swipe left and right to view.
The value of the "supremum" pseudo record is higher than any actual value in the index.
The supremum is not a real index record, so in fact this next-key only locks the gap after the maximum index value.
Based on this, when the obtained data range has filled all the data ranges, there is no gap at this time and there is no need for gap lock.
If there is a gap in the data range, it is necessary to confirm whether to lock the gap according to the isolation level.
The default isolation level of repeatable reading. In order to ensure repeatable reading, in addition to locking the data itself, it is also necessary to lock the data gap.
READ COMMITTED has been committed for reading, and the record lock of the unmatched row is released after MySQL evaluates the where condition.
For the update statement, InnoDB performs a "semi-consistent" read to return the latest submitted version to MySQL.
So that MySQL can determine whether the row matches the updated where condition.
Summary &; Extension:
Unique indexes have unique constraints, so if the principle of unique constraints is violated, the changed data will fail.
When the where condition uses the secondary index to filter data, it will lock the entries hit by the secondary index and the corresponding clustered index; Therefore, when other transactions change the clustered index that hits the lock, they will wait for the lock.
The increase of row locks is line by line, so it may lead to deadlock in the case of concurrency.
For example,
When Session A locks the qualified clustered index, Session B may already hold the record lock of the clustered index, while Session B is waiting for the record lock of the clustered index that Session A already holds.
Session A and Session B are clustered indexes located by two unrelated auxiliary indexes.
Session a passes index idA, and session b passes index idB.
When there is no gap in the data obtained by the where condition, there will be no gap locking regardless of the isolation level of rc or rr.
For example, a completely filled data range is obtained by a unique index, and a gap lock is not needed at this time.
The purpose of gap lock is to prevent data from being inserted into the gap, so the existence of data in the gap, whether caused by insertion or update, will be prevented.
In rc isolation mode, query and index scanning will disable gap locks, which are only used for foreign key constraint checking and duplicate key checking (mainly uniqueness checking).
In rr mode, in order to prevent misreading, a gap lock will be added.
In a transaction, locks are added at the beginning of SQL and released at the end of the transaction.
As far as lock types are concerned, there should be optimized locks and lock upgrades. For example, rr mode does not use index query, whether it can be directly upgraded to table lock.
As far as the lock application scenario is concerned, in the playback scenario, if it is determined that transactions can be concurrent, you can consider not locking and speed up the playback.
Locks are only a granularity of concurrency control, only a small part:
Whether it is necessary to control concurrency from different scenarios, (given the change of data without intersection and sequence, concurrent playback of multiple transactions of the same previous transaction in MySQL MTS)
The granularity of concurrency control (lock is a logical granularity, which may have a logical granularity or mode such as physical layer)
Optimization under the same granularity, (the lock itself has optimization, such as IX and IS type optimization locks)
Security of Granular Loading &; Performance (such as obtaining the page lock first, then obtaining the row lock, and releasing the page lock after obtaining the row lock, whether it is successful or not).