In-depth understanding of Mysql transaction isolation level and locking mechanism issues

Overview

Databases generally execute multiple transactions concurrently. Multiple transactions may concurrently perform add, delete, modify, and query operations on the same batch of data, which may result in dirty reads, dirty writes, non-repeatability, and phantom reads . The essence of these problems is the problem of multi-transaction concurrency in the database. In order to solve the problem of transaction concurrency, the database has designed a transaction isolation mechanism, a lock mechanism, and an MVCC multi-version concurrency control isolation mechanism, using a complete set of mechanisms to solve the problem of multi-transaction concurrency .

Transactions and their ACID properties

Atomicity: indivisibility of operations;

Consistency: consistency of data;

Isolation: Transactions do not interfere with each other;

Persistence: Data modifications are permanent;

Problems with concurrent transaction processing

Dirty write: lost updates, the last update overwrites updates made by other transactions;

Dirty read: Transaction A reads data that has been modified but not committed by transaction B;

Non-repeatable read: The same query within a transaction has different results at different times, which is aimed at data update and deletion operations;

Phantom read: Transaction A reads the newly added data submitted by transaction B that started later; this is for data insertion;

Transaction Isolation Level

READ-UNCONMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

View the transaction isolation level of the current database:

show variables like 'tx_isolation'

Set the transaction isolation level:

set tx_isolation='REPEATABLE-READ';

The default transaction isolation level of MySQL is repeatable read. When developing a program with Spring, if the isolation level is not set, the isolation level set by MySQL is used by default. If Spring is set, the set isolation level is used.

Lock Details

A lock is a computer mechanism that coordinates multiple processes or threads to access a resource concurrently.

Lock classification

From the performance point of view, it can be divided into: optimistic locking (implemented by version comparison) and pessimistic locking;

According to the type of database operation: read-write and write lock (pessimistic lock);

Read lock (shared lock, S lock (Shared)): for the same data, multiple read operations can be performed simultaneously without affecting each other;

Write lock (exclusive lock, X lock (exclusive)): blocks other write locks and read locks before the current write operation is completed;

From the granularity of database operations: table locks and row locks

Table lock: locks the entire table in each operation, with low overhead and fast locking. Deadlock will not occur. The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest. It is generally used in the scenario of whole table data migration.

# Manually add table lock lock table table name read(write), table name 2 read(write);
# View the locks added to the table show open tables;
# Delete table lock unlock tables;

Row lock: Each operation locks a row of data. The overhead is high and locking is slow; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

The biggest differences between InnoDB and MYISAM: 1. InnoDB supports transactions; 2. InnoDB supports row-level locks.

Summarize:

MyISAM automatically adds a read lock to the table involved before executing a query statement; it adds a write lock when executing update, insert, and delete operations;

InnoDB does not lock rows before executing a query statement (non-serial isolation level); row locks are added when executing update, insert, and delete operations.

A read lock blocks writes but not reads. The write lock will block both reading and writing.

Row Locks and Transaction Isolation Levels Case Study

mysql prepare a table

1. Dirty read: Transaction A reads data that has been modified but not committed by another transaction. This situation is simple and will not be explained in detail. The corresponding transaction isolation level is read uncommitted.

2. Non-repeatable read, corresponding transaction isolation level: read committed

Transaction A:

set session transaction isolation level read committed;
 
start transaction;
 
select * from t_user;

Transaction B:

set session transaction isolation level read committed;
 
start transaction;
 
-- insert into t_user values ​​(1,'张',8);
update t_user set age = 9 where id = 1;
 
commit;

The first time transaction A executes a query statement, the result is as follows:

At this point, transaction B has been completed, but transaction A has not yet ended. We continue to execute a query, and the results are as follows:

A non-repeatable read problem occurs. The data results of two queries within a transaction are inconsistent, and data that has been submitted by other transactions is read.

3. Repeatable read, set the transaction isolation level to repeatable read;

The results of the first execution of transaction A are as follows:

Transaction B executes, modifies, updates age=8 and commits. The results are as follows:

On the left is transaction A. The query result is the same as at the beginning, which solves the problem of non-repeatable read. Direct query, age=8 at this time.

The MVCC (multi-version concurrency control) mechanism is used under the repeatable read isolation level. The select operation will not update the version number and is a snapshot read (historical version); the insert, update, and delete operations will update the version number and are current reads (current version).

4. Phantom read, in 3, add a new piece of data as follows

At this time, transaction A queries again and the results are as follows:

The result is still the same as at the beginning. In this scenario, the repeatable read isolation level effectively prevents the problems of non-repeatable reads and phantom reads.

If transaction A executes an unconditional update after the first query, the update will affect all rows, including the newly added data by transaction B. At this point, execute the query again, the results are as follows:

Phantom reads occur . The official explanation for phantom reads given by MySQL is: as long as there is an extra row calculation phantom read in the second select in a transaction.

5. Serializable, InnoDB queries will also be locked. If the query is a range, all rows in the range, including the gap range where each row of records is located, will be locked, even if the row of data has not been inserted yet.

Gap Lock

Session_1 executes update t_user set name = '哈哈' where id>8 and id<18; then other sessions cannot insert or modify any data in all rows and gaps within this range.

Gap locks only take effect under the repeatable read isolation level

Next-key Locks

Next-key Locks are a combination of row locks and gap locks. In the range of gap lock (8,18), we will actually find existing values. For example, the ID closest to this interval is 3,20; then the range (3,20] is actually within the row lock range.

Non-index row locks are upgraded to table locks

Locks are mainly added to indexes. If non-index fields are updated, row locks may become table locks.

InnoDB's row lock is a lock on the index, not a lock on the record. And the index cannot be invalid, otherwise it will be upgraded from row lock to table lock

You can also use lock in share mode (shared lock) and for update (exclusive lock) to lock a row.

in conclusion:

Since the Innodb storage engine implements row-level locking, although the performance loss caused by the implementation of the locking mechanism may be higher than that of table-level locking, it is far superior to MYISAM's table-level locking in terms of overall concurrent processing capabilities.

However, Innodb's row-level locking also has its fragile side. If used improperly, it may make the overall performance worse.

Row lock analysis

Analyze row lock contention on the system by checking the InnoDB_row_lock status variable

show status like 'innodb_row_lock%'; 

The more important ones are:

Innodb_row_lock_time_avg (average waiting time)

Innodb_row_lock_waits (total number of waits)

Innodb_row_lock_time (total waiting time)

When the number of waits is high and the duration of each wait is not small, it is necessary to analyze why there are so many waits in the system and develop an optimization plan based on the analysis results.

Deadlock

set session transaction isolation level repeatable read;
 
start transaction;
 
select * from t_user where id = 2 for update;
select * from t_user where id = 1 for update;

Transaction A first locks id=1, then locks id=2; Transaction B has the opposite order, resulting in a deadlock. The results are as follows:

In most cases, MySQL can automatically detect deadlocks and roll back the transaction that caused the deadlock, but in some cases there is no solution.

View recent deadlock log information:

show engine innodb status\G;

Lock optimization suggestions:

1. Try to complete all data retrieval through indexes to avoid upgrading non-index row locks to table locks;

2. Design the index reasonably to minimize the scope of the lock;

3. Reduce the scope of index conditions as much as possible to avoid gap locks;

4. Try to control the transaction size, reduce the amount of locked resources and the length of time, and try to execute the SQL involving transaction locking at the end of the transaction;

5. Isolate transactions at the lowest level possible

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