MySQL transaction isolation level and locks

Transfer: developer.ibm.com/zh/technolo…

Transactions and their characteristics

Database transaction (referred to as transaction) is a logical unit in the execution of database management system. It consists of a limited sequence of database operations. The use of transactions is one of the important characteristics that distinguish database management systems from file systems.

Transactions have four important features: Atomicity, Consistency, Isolation, Durability, commonly referred to as ACID feature. Let me explain them one by one.

IBM Compose for MySQL

The Compose for MySQL database service available on the IBM Cloud helps you better manage MySQL with features such as self-scaling deployment, high availability, and automated non-stop backup.

Atomicity

After the transaction starts, all operations are either finished or not done. It is impossible to stop in the middle link. If an error occurs during transaction execution, it will be rolled back to the state before the transaction began, and all operations will appear as if they did not happen. For example, if a transaction needs 100 new records but fails after 10 new records are added, the database rolls back the 10 new records. In other words, the transaction is an indivisible whole, just like the atom in chemistry, which is the basic unit of matter.
Consistency

A transaction that changes a database from one state to another consistent state. The database integrity constraints are not broken before and after the transaction. For example, an id has a unique attribute. If the id becomes non-unique after a transaction that changes the id, consistency is broken.
Isolation

Each read-write transaction object is required to be separated from the operation objects of other transactions, that is, the transaction is not visible to other transactions until committed. It can also be understood that when multiple transactions are accessed concurrently, transactions are isolated, and one transaction should not affect the running effect of other transactions. This refers to the fact that in a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space. Changes made by concurrent transactions must be isolated from changes made by any other concurrent transactions. For example, when a user updates his personal information, he cannot see that the system administrator is also updating the user’s personal information (at this time, the update transaction has not been committed).

Note: MySQL uses a locking mechanism to ensure transaction isolation.
“Durability”

Once a transaction is committed, its results are permanent. Even in the event of an outage, the database can recover the data, which means that after the transaction completes, all updates made by the transaction to the database are saved to the database and cannot be rolled back. This is just a guarantee from the perspective of the transaction itself, eliminating RDBMS (relational database management system, such as Oracle, MySQL, etc.) itself.

Note: MySQL uses redo logs to ensure transaction persistence.

The isolation level of the transaction

The SQL standard defines four isolation levels used by ANSI (American National Standards Institute) and ISO/IEC (international standards), each of which has varying degrees of impact on transaction processing capabilities.

The four isolation levels, from high to low concurrency, are described and demonstrated in code in MySQL 5.7.

READ UNCOMMITTED

Transactions at this isolation level read data from other uncommitted transactions, a phenomenon also known as dirty reads.

Prepare two terminals, named mysql terminal 1 and mysql terminal 2, and prepare a test table, write a test data and set the isolation level to READ UNCOMMITTED. Run the test on either terminal.
```
SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1);
Copy the code
```
Show more

begin; update test set id = 2 where id = 1; select * from test; -- a record with ID 2 is displayedCopy the code

Log in to mysql terminal 2, start a transaction, and view the data in the table.
```
use test; begin; select * from test; -- a record with ID 2 is displayedCopy the code
```
Show more

The last step reads uncommitted transactions (no COMMIT actions) from mysql Terminal 1, resulting in dirty reads. Dirty reads are not allowed in most business scenarios, but the concurrency of the database at this isolation level is optimal.

READ COMMITTED

One transaction can read another committed transaction, and multiple reads result in different results. This phenomenon is called the non-repeatable read problem, which is the default isolation level between Oracle and SQL Server.

Prepare two terminals, named mysql terminal 1 and mysql terminal 2, and prepare a test table, write a test data and set the isolation level to READ COMMITTED. Run the test on either terminal.
```
SET @@session.transaction_isolation = 'READ-COMMITTED';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1);
Copy the code
```
Show more
Log in to mysql terminal 1, start a transaction, update record 1 to 2, and confirm that the number of records has changed.
```
begin; update test set id = 2 where id = 1; select * from test; -- see a record of 2Copy the code
```
Show more
Log in to mysql terminal 2, start a transaction, and view the data in the table.
```
use test; begin; select * from test; Look at a record with ID 1Copy the code
```
Show more
Log in to mysql terminal 1 and commit the transaction.
```
commit;
Copy the code
```
Show more

Switch to mysql terminal 2.

select * from test; -- a record with ID 2 is displayedCopy the code

Mysql terminal 1 (ID = 1); mysql terminal 2 (ID = 1); mysql terminal 1 (ID = 2); Indicates that committed transactions have been read at this isolation level.

REPEATABLE READ (REPEATABLE READ)

This isolation level is the default isolation level of MySQL. In the same transaction, the result of the select operation is the state at the start of the transaction. Therefore, the same select operation will read the same result, but there will be phantom read phenomenon. MySQL’s InnoDB engine can avoid phantom reads through the next-key locks mechanism (see the algorithm for row locks section below).

Prepare two terminals, named mysql terminal 1 and mysql terminal 2, prepare a test table test and change the isolation level to REPEATABLE READ, execute on either terminal.
```
SET @@session.transaction_isolation = 'REPEATABLE-READ';
create database test;
use test;
create table test(id int primary key,name varchar(20));
Copy the code
```
Show more
Log in to mysql terminal 1 and start a transaction.
```
begin; select * from test; - no recordCopy the code
```
Show more
Log in to mysql terminal 2 and start a transaction.
```
begin; select * from test; - no recordCopy the code
```
Show more

Switch to mysql terminal 1, add a record and commit.

insert into test(id,name) values(1,'a');
commit;
Copy the code

Switch to MSYQL terminal 2.
```
select * from test; -- The query is still blankCopy the code
```
Show more

This step shows that no other committed transactions can be read at this isolation level. If you want to see the transaction submitted by mysql terminal 1, you can read the transaction submitted by mysql terminal 2 after the current transaction is committed. Let’s experiment to see if there are additional problems at this isolation level.

Insert data into mysql terminal 2.

insert into test(id,name) values(1,'b'); This error indicates primary key conflictCopy the code

At this point, you may have a question in your mind. Clearly there is no data in step 5, why is an error reported here? This is actually a problem that can occur at this isolation level, which MySQL calls phantom reads. Note that I’m emphasizing MySQL databases here, and Oracle databases may have different definitions for phantom reads.

SERIALIZABLE

At this isolation level transactions are executed sequentially, and the MySQL database’s InnoDB engine implicitly assigns a read shared lock to read operations to avoid dirty reads, unrepeatable rereads, and phantom reads.

Mysql Terminal 1 and mysql terminal 2, log in to mysql respectively, prepare a test table test and change the isolation level to SERIALIZABLE.
```
SET @@session.transaction_isolation = 'SERIALIZABLE';
create database test;
use test;
create table test(id int primary key);
Copy the code
```
Show more
Log in to mysql terminal 1, start a transaction, and write a piece of data.
```
begin;
insert into test(id) values(1);
Copy the code
```
Show more

begin; select * from test; -- it will stay stuckCopy the code

Immediately switch to mysql terminal 1 and commit the transaction.
```
commit;
Copy the code
```
Show more

Once the transaction commits, mSYQL terminal 2 will immediately return a record with ID 1, otherwise it will be stuck until it times out, which is controlled by Innodb_lock_WAIT_TIMEOUT. Because every SELECT statement is locked, this isolation level has the weakest database concurrency, but some data suggests that this is not necessarily true, so you can do your own stress test if you are interested.

Table 1 summarizes some of the issues that arise at each isolation level.

Table 1. Some of the issues at each isolation level

MySQL in the lock

Locking is also one of the important features that distinguish database management systems from file systems. The locking mechanism ensures the integrity and consistency of data when the database is accessed concurrently. For the implementation of locks, database vendors will have different implementation methods. This article discusses locks for the InnoDB engine in MySQL.

The type of lock

InnoDB implements two types of row-level locking:

Shared lock (also known as S lock) : Allows a transaction to read a row of data.

Select * from tableName where… lock in share mode; Manually add S lock.
Exclusive lock (also known as X lock) : Allows a transaction to delete or update a row of data.

Select * from tableName where… for update; Manually add X lock.

The S lock is compatible with the S lock, while the X lock is incompatible with other locks. For example, if transaction T1 acquires the S lock on row R1, and transaction T2 immediately acquires the S lock on row R1, then both T1 and T2 acquire the S lock on row R1, this situation is called lock compatibility. However, if another transaction T2 wants to acquire the X lock of row R1 at this time, it must wait for the release of the lock of row R by T1, which also becomes a lock conflict.

In order to implement the multi-granularity locking mechanism, InnoDB also has two types of intentional locks for internal use, which InnoDB automatically adds, and both are table-level locks.

Intended Shared lock (IS) : a transaction IS about to set a shared lock on each row in a table. The transaction must obtain the IS lock on the table before it can lock the row S.
Intentional exclusive lock (IX) : a table IX lock must be acquired before a transaction can lock rows in a table with an X lock.

The main purpose of intent locks is to allow row and table locks to coexist. Table 2 lists the compatibility of row-level and table-level intent locks.

Table 2. Compatibility of row-level locks and table-level intent locks

Algorithm for row locking

InnoDB storage engine uses three row locking algorithms to meet transaction isolation levels.

Record Locks

This lock is on the index record. If there is no index defined in the table, InnoDB creates a hidden clustered index for the table by default and locks the record with this index.
Gap Locks

The lock locks a range, but not the record itself. You can change the isolation level to READ COMMITTED or set the innodb_locks_unSAFE_for_binlog parameter to ON.
Next-key Locks

This lock is a combination of Record Locks and Gap Locks, which Locks a range and Locks the Record itself. InnoDB uses next-key Locks to solve phantom Locks. Note that InnnoDB automatically degrades next-key Locks to Record Locks if the index has unique attributes. For example, if an index has 1, 3, 5 three values, the index of locking range for (- up, 1), (1, 3], (3, 5), (5, + up).

A deadlock

A deadlock is a phenomenon in which two or more processes are blocked during execution, either by competing for resources or by communicating with each other, and cannot proceed without external action. The system is said to be in a deadlock state or a deadlock occurs in the system. These processes that are always waiting for each other are called deadlocked processes.

InnoDB uses a wait-for graph method to automatically detect deadlocks and roll back a transaction if a deadlock is found.

Let’s take a look at deadlocks with an example.

Mysql Terminal 1 and mysql terminal 2, log in to mysql respectively, prepare a test table test, write two test data, and change the isolation level to SERIALIZABLE, either terminal can execute.
```
SET @@session.transaction_isolation = 'REPEATABLE-READ';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1),(2);
Copy the code
```
Show more
Log in to mysql terminal 1, start a transaction, and manually add X lock to mysql terminal 1.
```
begin;
select * from test where id = 1 for update;
Copy the code
```
Show more
Log in to mysql terminal 2, start a transaction, manually add X lock to mysql terminal 2.
```
begin;
select * from test where id = 2 for update;
Copy the code
```
Show more
Innodb_lock_wait_timeout = innodb_lock_wait_timeout = innodb_lock_wait_timeout = innodb_lock_wait_timeout = innodb_lock_wait_timeout
```
select * from test where id = 2 for update;
Copy the code
```
Show more
InnoDB engine immediately detects deadlock and automatically rolls back a transaction to prevent deadlock from holding resources. If InnoDB does not use a transaction, InnoDB will automatically roll back a transaction to prevent deadlock from holding resources.
```
select * from test where id = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the code
```
Show more

At this point, you can use the show Engine Innodb status\G command to see information about LATEST DETECTED DEADLOCK, which indicates that a DEADLOCK has occurred; Or you can print deadlock information to the MySQL error log by setting innodb_print_all_deadlocks (available starting with MySQL 5.6.2) to ON.

Lock optimization recommendations

If the lock is not well used, it will cause a lot of lag phenomenon to the business. After understanding some knowledge points related to the lock, we can consciously avoid some problems caused by the lock.

Design indexes properly so that InnoDB can lock index keys as accurately as possible, narrow the lock range as much as possible, and avoid unnecessary locks that affect the execution of other queries.
Minimize range-based data retrieval filtering criteria to avoid locking records that should not be locked due to the negative impact of gap locking.
Try to control the size of transactions and reduce the amount of locked resources and the length of locked time.
Use transaction isolation at lower levels whenever possible in the business environment to reduce the additional costs MySQL incurs from implementing transaction isolation levels.

conclusion

This article gives you a basic understanding of database transactions and transaction isolation levels. It also introduces you to some of the locks in MySQL’s InnoDB engine, allowing you to design a more robust business model using a relational database system.