Mysql transaction isolation level and locking mechanism

Writing in the front

Mysql index (Mysql index) Let’s summarize some of mysql’s transaction and locking mechanisms. During the development and use of mysql, there are concurrent operations. Multiple transactions are executed concurrently to add, delete, change, and check the same batch of data, which may cause dirty read, dirty write, unrepeatable read, and magic read problems. In order to solve the concurrency problem between multiple transactions, the database designed transaction isolation mechanism, lock mechanism, MVCC multi-version concurrency control isolation mechanism to solve the concurrency problem of multiple transactions. First, let’s look at transactions and ACID.

Transactions and ACID

A transaction is a logical processing unit consisting of a set of SQL statements. A transaction has the following four properties, called ACID.

• Atomicity: A transaction is an atomic operation, an operation on data that either performs all or none.
• Consistent: Data must be Consistent at the beginning and completion of a transaction.
• Isolation: The database system provides an Isolation mechanism. The operations of different transactions do not affect each other.
• Durable: Changes to data after transactions are finished are permanent.

Problems with concurrent transaction processing

Update missing (dirty write)

When multiple transactions update and change rows on the same row, dirty write problems occur because transactions are isolated from each other and unaware of the existence of other transactions, and the last update overwrites updates made by other transactions. The first transaction updates the data value 3+1=4. The second transaction also updates the data value 3+2=5. The second transaction overwrites the value 5 of the first transaction. If the first transaction is updated 3+1=4, then the second transaction is updated 4+2=6.

Dirty read

A transaction is a record of changes, the transaction is completed, but haven’t submit, the record is in an inconsistent state, another transaction at this time to read a record, the same if uncontrolled, the second transaction reads the “dirty” data (namely data has been modified, but uncommitted data), and on the basis of further processing, Uncommitted data dependencies are created. If transaction B rolls back, the data read by transaction A is invalid and does not meet the consistency requirements.

Unrepeatable read

At some point after reading some data, a transaction reads the previously read data again and finds that the data has been modified or deleted. (In English: the same query statement in transaction A has different results at different times)

Phantom read

A transaction reads a previously read record under the same query criteria and finds that another transaction inserts new data that meets its query criteria. (In plain English: transaction A has read the new data submitted by transaction B, which does not comply with isolation)

Transaction isolation level

Isolation level	Dirty read	Unrepeatable read	Phantom read
Read uncommitted	may	may	may
Reading has been submitted	Can’t be	may	may
Repeatable read	Can’t be	Can’t be	may
serializable	Can’t be	Can’t be	Can’t be

The more stringent the transaction isolation level of the database, the fewer concurrency side effects, but the higher the cost, because transaction isolation essentially serializes transactions to some extent. Check the current database transaction isolation level: show variables like’ TX_ISOLATION ‘; Set transaction isolation level: set tx_isolation=’REPEATABLE-READ’; Mysql’s default transaction isolation level is repeatable read. When developing programs with Spring, if the isolation level is not set, the default isolation level is set by Mysql.

The lock

• In terms of performance, it is divided into optimistic locks and pessimistic locks
• The type of operation on the database can be divided into read lock and write lock (pessimistic lock).

Read lock (shared lock, S lock): Multiple read operations can be performed simultaneously on the same data without affecting each other. Write lock (exclusive lock, X lock): The current write operation blocks other write locks and read locks

• The granularity of operations on data is divided into table locks and row locks

Table lock Locks the entire table with each operation. Low overhead, fast lock; No deadlocks occur; The granularity of locks is large, the probability of sending lock conflicts is high, and the concurrency is low. It is used in the scenario of table data migration. Row locking Locks one row of data per operation. High overhead, slow lock, deadlock, small lock granularity, low probability of lock conflict, high concurrency.

Example of row locks and transaction isolation levels

Read uncommitted

1. Open client A and set parametersset tx_isolation=’read-uncommitted’;(read uncommitted), query the value of the table account.

2. Before the transaction is committed on client A, open client B and update the value of the account table.

3. Query data in the Account table on client A and find that uncommitted transactions can be queried on client B.



4. After client B rolls back the data, all operations are cancelled and the data queried by client A becomes dirty



5. On client A, run the update statement update Account set balance = balance-50 where ID =1. The value does not change to 350. This is because it is used when updating the databasebalance = balance – 50Balance is the latest data in the database, so it will not be 350.In the application code, the value of balance is found and subtracted by 50. The value is 400, and the application does not know that the MySQL transaction is rolled back. Subtract 50, and it becomes 350.Dirty reading.

Reading has been submitted

1. Enable the Settings on client Aset tx_isolation=’read-committed’;Read committed, query table records.

2. Start client B and update records before client A submits the transaction.

3. The transaction of client B has not been submitted. Therefore, client A cannot query the updated data of client BDirty reads the question

4. Client B commits the transaction.

5. The query result executed by client A is inconsistent with the last query resultThe unrepeatable read problem.

Repeatable read

1. On client A, set the parametersset tx_isolation=’repeatable-read’;Repeatable read, query records.

2. Client B updates and submits records.

3. On client A, the query results are the same.Solved the unrepeatable read problem.



4. On client A, continue the operationupdate account set balance = balance – 50 where id = 1Update operation, balance does not become 400-50=350, becomes 300, data consistency is not broken.The MVCC mechanism is used at the isolation level of repeatable reads. The select operation does not update the version number and is a snapshot read. Insert, update, and delete update the version number and are currently read.



5. Client B, restart a transaction. And insert data



6. On client A, no new data or phantom read is displayed.

Clearance lock

A gap lock locks the space between two values. MySQL’s default isolation level isRepeatable read



Update account set name=’smart’ where ID >7 and ID <18; update account set name=’smart’ where ID >7 and ID <18 There’s no way any other session can do thatThe range contains all row records, including gap row records, and the gap in which the row record residesInsert or modify task data in. That is, the data cannot be modified even if the id is in the interval (3,20).Gap locking takes effect only at the repeatable read isolation level

Key in the lock

A critical lock is a combination of a row lock and a gap lock. For example, the entire interval above (3,20) could be called a temporary key lock

A no-index row lock is upgraded to a table lock

Lock Optimization Suggestions

• As much as possible, make all data retrievals through indexes to avoid non-indexed row locks upgrading to table locks
• Design indexes properly to minimize the scope of locks
• Minimize the range of search conditions to avoid gap locking
• Try to control transaction size, reduce the amount of locked resources and length of time, and try to execute SQL related to transaction locking at the end of the transaction
• The lowest possible level of transaction isolation