MySQL transaction isolation level for production environments
The default isolation level of MySQL database is RR (repeatable), but many large companies have changed the isolation level to RC (read submitted), mainly to increase concurrency and reduce deadlock probability
In order to solve the problem of phantom reading, RR has more gap locks and next-keylocks than RC. In RC, only row locks are used to modify data, and the lock range is smaller, so the concurrency of RC is higher than that.
Create the following table and insert some records
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
SQL-A is executed at this time and the transaction is not submitted
UPDATE t SET b = 5 WHERE b = 3;
In the case where RR can repeat isolation level, which pieces of data will be locked?
The answer is that all rows that meet the criteria will be locked, and to prevent phantom reading, the gap between these rows will be locked.
x-lock(2,3) and x-lock(4,3) -- These two lines will be updated and retainedXLock。 x-lock(1,2)、x-lock(3,2) and x-lock(5,2) -- Although these lines do not meet the update conditions，但由于间隙Lock的存在，它们也会被Lock定以防止其他事务插入新的行
You can see that the full lock is in place. Execute SQL-B at this time:
UPDATE t SET b = 4 WHERE b = 2;
It will be blocked.
Therefore, at the RR isolation level,UPDATE t SET b = 5 WHERE b = 3;All rows will be locked, including those that do not meet the update criteria.
When executing SQL-A, which pieces of data will be locked under the RC-read submission isolation level?
x-lock(1,2); unlock(1,2) x-lock(2,3); update(2,3) to (2,5); reserve x-lock x-lock(3,2); unlock(3,2) x-lock(4,3); update(4,3) to (4,5); reserve x-lock x-lock(5,2); unlock(5,2) -- Only rows that meet the update conditions will be locked，Right now b = 3 The way，becauseRCLevel does not use gap lock。
It can be seen that only two pieces of data are locked. What happens when SQL-B is executed?
x-lock(1,2); update(1,2) to (1,4); reserve x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); reserve x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); reserve x-lock
You can see that only data with b=2 is locked.Perfectly avoid the locks added by SQL-A
At this time, some students may have questions: Shouldn’t SQL-B be blocked by locks from (2, 3) line?
Semi-consistent read ("semi-consistent" read)
This is actually an optimization made by InnoDB.
- When executing update, the scan finds that the current row has been locked. It will perform semi-consistent read operations to obtain the latest version of the current data (the latest version of the row locked by SQL-A in the above, b is 5), to determine whether it matches the where conditions of the current (SQL-B) update
- If it matches, it means that the current update also needs to lock this line.
- Therefore, it takes a wait. If the mismatch means there is no correlation between them, so there is no need to wait for locks. This optimization improves concurrency.
soRC + semi-consistent readIt can further improve the concurrency of SQL execution.
Moreover, the granularity of RC locks is smaller, which means that the probability of deadlock will be lower, but the disadvantage is that phantom reading may occur, which requires the business to evaluate phantom reading itself (in most cases it has no impact).
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.