In-depth explanation of MySQL isolation level and locking mechanism

Brief description:

Our MySQL usually executes multiple transactions concurrently, and multiple transactions may concurrently perform CRUD operations on the same data or the same batch of data; this may lead to the problems we usually call dirty reads, non-repeatable reads, and phantom reads.

The essence of these problems is MySQL multi-transaction concurrency. In order to solve the problem of multi-transaction concurrency, MySQL has designed a lock mechanism, MVCC multi-version concurrency control isolation mechanism, and transaction isolation mechanism, using a complete set of mechanisms to solve the problems caused by multi-transaction concurrency.

1. Four characteristics of transactions

2. Problems caused by multiple concurrent transactions

3. Transaction isolation level

Dirty reads, non-repeatable reads, and phantom reads are actually MySQL read consistency problems, which must be solved by the database providing a certain transaction isolation mechanism.

View the transaction isolation level of the current database: show variables like 'tx_isolation';

Set the transaction isolation level: set tx_isolation='isolation level'

4. Demonstrate problems with different isolation levels

mysql version: 5.7.34

Tables involved:

Two MySQL clients

Client A <===================> Client B (The two clients in each picture below are named after the first picture)

Read Uncommitted

1.1 Set the transaction isolation level set tx_isolation='read-uncommitted';

1.2 Client A and Client B each open a transaction.

1.3 Client A only queries, and client B modifies the record with id = 1;

1.4 When both transactions are not committed, transaction A reads the data modified by transaction B

1.5 Once the transaction of client B is rolled back for some reason, the data queried by client A is actually dirty data, which does not meet the consistency requirements.

Read Committed

2.1 Set the isolation level to read committed: set tx_isolation='read-committed';

2.2 Client A and Client B each open a transaction.

2.3 Client A only queries, and client B modifies the record with id = 1;

2.4 When client B has not submitted the transaction, client A cannot query the data that client B has not submitted, thus solving the dirty read problem

2.5 After client B commits the transaction, client A queries the table again, and the result is inconsistent with the previous step, which means that a non-repeatable read problem occurs, which does not meet the isolation requirement.

Repeatable Read

3.1 Set the isolation level to repeatable read: set tx_isolation='repeatable-read';

3.2 Client A and Client B each open a transaction.

3.3 Client B modifies the data in the table and submits it;

3.4 Client A queries the data in the table and finds no inconsistency with the previous step, thus solving the problem of non-repeatable reads

3.5 In client A, execute update account set balance = balance - 100 where id = 1; balance does not become 800-100=700; instead, it is calculated using the data submitted by client B, so it is 600; data consistency is not destroyed; the MVCC mechanism is used under the repeatable read isolation level, the select operation will not update the version number, it is a snapshot read (historical version), ensuring repeatable read under the same transaction; insert/update/delete will update the version number, it is a current read (current version) to ensure data consistency

3.6 Client B reopens a transaction, inserts a piece of data, and commits

3.7 Requery the table data in client A, and the data just added by client B does not appear, and no phantom read occurs

3.8 Verify phantom read: In client A, modify the data with id = 4; the update is successful; query again to find the new data added by client B, which indicates a phantom read problem and does not meet the isolation requirement.

Serialization

4.1 Set the isolation level to serializable: set tx_isolation='serializable';

4.2 Client A and Client B each open a transaction.

4.3 Client A first queries the data in the table with id = 1

4.4 When client A's transaction is not committed, client B updates the data in the table with id = 1. Since client A's transaction is not committed, client B's update action will be blocked until client A commits the transaction or times out. If the timeout occurs, the SQL error is: Lock wait timeout exceeded; try restarting transaction

4.5 The data with id = 2 can be updated successfully in client B, which means that under the serialized isolation level, the innodb query will also be locked.

4.6 If client A executes a range query, all rows in the range, including the gap interval range where each row record is located (even if the row has not been inserted, it will be locked, this is called gap lock), will be locked . At this time, if client B performs any operation on the data in the range, it will be blocked; thus, phantom reads are avoided;

4.7 Serialization This isolation level has extremely low concurrency, so it is rarely encountered in real development. This is also an important reason why MySQL uses repeatable read as the default isolation level.

5. Locking mechanism

The default isolation level of MySQL is repeatable read, but phantom reads may still occur; gap locks can solve phantom reads in some cases;

Gap Lock

Overview: Gap lock locks the gap between two values.

Assume that the data in the table is as follows:

Then there are three gaps: (4,10), (10,15) and (15, positive infinity);

1.1 Set the isolation level to repeatable read: set tx_isolation='repeatable-read';

1.2 Client A and Client B each open a transaction.

1.3 On client A, execute update account set balance = 1000 where id > 5 and id < 13;

1.4 When client A has not submitted the request, client B cannot perform insert/update operations on all rows in the range (including gap rows) and the gaps where the rows are located . That is, data cannot be modified in the range of 4<id<=15, and id = 15 cannot be modified either.

1.5 Gap locks are only effective at the repeatable read isolation level

Temporary lock

Overview: Temporary lock is a combination of row lock and gap lock. For example, the 4<id<=15 above is a temporary lock.

Non-index row locks are upgraded to table locks

3.1 Client A and Client B each open a transaction.

3.2 On client A, execute update account set balance = 1000 where name = 'Li Si';

3.3 When client A has not submitted, client B executes update account set balance = 800 where id = 15; it will also be blocked until client A submits or times out;

3.4 The locks in MySQL are mainly loaded on index fields. If used on non-index fields, the row lock will be upgraded to a table lock;

Exclusive lock

4.1 Client A and Client B each open a transaction.

4.2 Execute select * from account where id = 1 for update on client A;

4.3 When client A has not submitted, client B executes update account set balance = 800 where id = 1; it will be blocked until client A submits or times out;

Conclusion: Innodb engine implements row locks. Although the performance loss caused by the implementation of row lock mechanism may be higher than that of table lock, the overall concurrent processing capability is definitely stronger than that of table lock. When the system concurrency is high, row locks will have obvious advantages over table locks. However, row locks are more complicated to use than table locks. If we use them improperly, the performance of row locks may not only be no better than that of table locks, but may even be worse.

Why is the overhead of row locks greater than that of table locks when the granularity of row locks is small?

Because table-level locks only need to find the current table to lock, row locks need to scan the records in the table until the row to be locked is found, so the cost of row locks is greater than that of table-level locks.

Some suggestions for lock optimization in real development situations:

• Reasonably use index field locks to narrow the lock scope
• Add all locks to index fields as much as possible to avoid escalating non-index row locks to table locks.
• Minimize the query scope as much as possible to avoid gap locks with large gaps
• Minimize transaction isolation
• Try to control the transaction size as much as possible, reduce the amount of locked resources, and put the SQL involved in transaction locking at the end of the transaction to reduce the locking time.

Summarize

This is the end of this article about MySQL isolation levels and locking mechanisms. For more information about MySQL isolation levels and locking mechanisms, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!