MySQL transaction isolation levels and implementation principles

I am kite, the public account “ancient time kite”, a not only technology public account, I have been in the app community for many years, mainly Java, Python, React also play 6 slash developer. The Spring Cloud series is complete, and you can check out the full series on my Github. You can also reply “PDF” in the public account to get the full PDF version of my elaborate tutorial.

Transaction isolation, transaction isolation and isolation level. What is transaction isolation and what is the isolation level? This article will help you comb.

MySQL transaction

In this article, MySQL transactions refer to InnoDB engine, MyISAM engine does not support transactions.

A database transaction is a set of data operations, and the operations within the transaction are either all successful or all failed, and nothing is done. In fact, not nothing is done, but some of them may be done but if one step fails, you have to roll back all the operations.

Suppose a online payment operation, the user payment involves order status updates, inventory and other series of action, which is a transaction, all will be well if all is well that, once there is a link among abnormal, the whole transaction is rolled back, always can’t update order status but hadn’t inventory, this problem is big.

Transactions have Atomicity, Consistency, Isolation and Durability (ACID), they cannot be missing either. Today is about isolation.

The concept that

The following concepts are what transaction isolation levels actually address, so you need to understand what they mean.

Dirty read

Dirty reads are read data that has not been committed by another transaction. Uncommitted means that the data may be rolled back, meaning that it may not end up in the database, meaning that it does not exist. Dirty reads are those that read data that must eventually exist.

Repeatable read

Repeatable reads mean that the data read at the beginning of a transaction is the same as the data read at any time until the end of the transaction. Usually for data ** UPDATE ** operation.

Unrepeatable read

In contrast to repeatable read, non-repeatable read means that the same batch of data may be read at different times in the same transaction and may be affected by other transactions, for example, other transactions have changed the batch of data and committed it. Usually for data ** UPDATE ** operation.

Phantom read

Phantom reads are for INSERT operations. Assume that the transaction for some lines for A change, but has not been submitted and the transaction B insert with the transaction before A change of record of the same line, and the transaction is A submit before you submit, and then, in A transaction in A query, will find that just change for some of the data did not play A role, but in fact is the transaction B just inserted in, let the user feel very magical, The feeling appears the illusion, this is called the illusion read.

Transaction isolation level

The SQL standard defines four isolation levels, all of which are supported by MySQL. The four isolation levels are:

1. READ UNCOMMITTED

2. READ COMMITTED

3. REPEATABLE READ

4. SERIALIZABLE

From the top down, the isolation strength gradually increases, and the performance gradually deteriorates. The isolation level to use is a tradeoff based on system requirements, where repeatable reads are the default level for MySQL.

Transaction isolation is designed to solve the problems mentioned above, such as dirty reads, unrepeatable reads, and phantom reads. The following shows how the four isolation levels solve these three problems.

Isolation level	Dirty read	Unrepeatable read	Phantom read
Read uncommitted	may	may	may
Read the submission	Can’t be	may	may
Repeatable read	Can’t be	Can’t be	may
serialization	Can’t be	Can’t be	Can’t be

Only the serialized isolation level solves all three problems; the other three isolation levels are flawed.

To find out

Let’s take a look at what the four isolation levels mean.

How do I set the isolation level

The isolation level of the MySQL database is REPEATABLE-READ, which is the default isolation level of MySQL.

# Check after transaction isolation level 5.7.20

show variables like 'transaction_isolation';

SELECT @@transaction_isolation



After # 5.7.20

SELECT @@tx_isolation

show variables like 'tx_isolation'



+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  | REPEATABLE-READ |

+---------------+-----------------+

Copy the code

Later, we will change the isolation level of the database, so let’s look at how.

The statement to change the isolation level is: Set [scope] Transaction Isolation Level, The SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL the READ UNCOMMITTED READ COMMITTED | REPEATABLE READ | SERIALIZABLE.

The function can be SESSION or GLOBAL, GLOBAL is GLOBAL, and SESSION is specific to the current callback window. Isolation level is the READ UNCOMMITTED READ COMMITTED | REPEATABLE READ | SERIALIZABLE these four, case-insensitive.

For example, this statement sets the global isolation level to read commit level.

mysql> set global transaction isolation level read committed; 

Copy the code

Transactions are performed in MySQL

A transaction is executed as follows: begin or start transaction, a series of operations, and a commit operation are performed to complete the transaction. Of course, if you rollback, the transaction will end.

It is important to note that the begin command does not represent the start of a transaction. The transaction begins when the first statement following the BEGIN command is executed. For example, in the following example, select * from XXX is the start of the transaction,

begin;

select * from xxx; 

commit; - or rollback.

Copy the code

In addition, you can query how many transactions are currently running by using the following statement.

select * from information_schema.innodb_trx;

Copy the code

All right, here we go. Let’s analyze the isolation levels.

Next, I will use a table to verify that the table structure is simple as follows:

CREATE TABLE `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(30) DEFAULT NULL,

  `age` tinyint(4) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Copy the code

There is only one initial record:

mysql> SELECT * FROM user;

+----+-----------------+------+

| id | name            | age  |

+----+-----------------+------+

| | the ancient kite | 1 | 1

+----+-----------------+------+

Copy the code

Read uncommitted

MySQL transaction isolation is implemented by locking, which naturally causes performance loss. The read uncommitted isolation level is unlocked, so it provides the best performance without the performance overhead of locking and unlocking. But there are pros and cons, this is basically equivalent to running naked ah, so it can’t even solve the problem of dirty reading.

Any changes made to data by a transaction are immediately exposed to other transactions, even if the transaction has not yet committed.

Let’s do a simple experiment to verify. First, set the global isolation level to read uncommitted.

set global transaction isolation level read uncommitted;

Copy the code

After the configuration is complete, the configuration takes effect only on newly started sessions. If you are using a shell client you need to reconnect to MySQL and if you are using Navicat you need to create a new query window.

SQL > alter table A; update user; age = 10; update user; age = 10; Transaction B may take the modified age=10 and do something else. In the process of transaction B’s operation, it is very likely that transaction A for some reason, the transaction rollback operation, in fact, transaction B got dirty data, with dirty data to carry out other calculations, then the result must be also wrong.

Follow the timeline to indicate the order of execution of the operations in the two transactions, focusing on the value of the AGE field in the figure.

Read uncommitted means that you can read uncommitted data from other transactions, but there is no guarantee that the data you read will be the committed data. If there is a rollback in the middle, there will be dirty data. Read uncommitted does not solve the dirty data problem. Not to mention repeatable and illusory, don’t even think about it.

Read the submission

Since read uncommitted doesn’t solve the problem of dirty data, there is read commit. Read commit is when a transaction can only read data that has already been committed by another transaction, that is, after the other transaction has called commit. The dirty data problem was solved.

Read commit transaction isolation level is the default transaction isolation level for most popular databases, such as Oracle, but not MySQL.

Let’s go ahead and verify by first changing the transaction isolation level to read commit level.

set global transaction isolation level read committed;

Copy the code

Then you need to reopen a new session window, that is, a new shell window.

Update (id=1); update (id=1); update (id=1); At this time, we use the SELECT statement in transaction B to query, and we find that before transaction A is committed, the age of the records queried in transaction B is always 1 until transaction A is committed. At this time, we select query in transaction B and find that the age value is already 10.

In the same transaction (transaction B in this example), the same query conditions at different times of the transaction will result in different records. The submission of transaction A will affect the query results of transaction B, which is the non-repeatable read, also known as the isolation level of read submission.

Each SELECT statement has its own snapshot, rather than one per transaction, so the queried data may be inconsistent at different times.

Read commits solve the problem of dirty reads, but they do not make repeatable reads, nor do they solve phantom reads.

Repeatable read

Repeatable is contrasted with non-repeatable, which means that the data values read at different times may be different for the same thing. Repeatable read means that a transaction will not read the changes made to the existing data by other transactions, even after the other transactions have committed. That is, the value of the existing data read at the beginning of the transaction is the same at any time until the transaction commits. However, the newly inserted data is readable for other transactions, which causes the illusion problem.

Also, change the global isolation level to repeatable read level.

set global transaction isolation level repeatable read;

Copy the code

At this isolation level, two transactions are started and both transactions are started simultaneously.

First, we can see the effect of repeatable reads. After transaction A starts, the data is modified and committed before transaction B. Transaction B reads the same data at both the start of transaction and after transaction A commits, so we can see the effect of repeatable reads.

Repeatable reads work, only for changes to existing rows, but not for newly inserted rows, where magic reads are created. Let’s take a look at the process:

Update (age = 1); update (age = 1);

Insert (age =1, name = ancient); insert (age =1, name = ancient);

Select * from A where age=1; select * from B where age=1; select * from B where age=1;

It should be noted that when you test phantom reads in MySQL, the above result does not appear. Phantom reads do not occur. MySQL’s repeatable read isolation level actually addresses phantom reads, which will be explained later

serialization

Serialization is the most effective of the four transaction isolation levels. It solves the problems of dirty reads, repeatable reads, and phantom reads, but it is the least effective. It changes the execution of transactions into sequential execution, which is like a single thread compared to the other three isolation levels.

How is transaction isolation implemented in MySQL

First of all, read uncommitted is the best performance method, but it is also the most barbaric method, because it is not locked at all, so there is no isolation at all.

Serialization. Read with a shared lock, that is, other transactions can be read concurrently, but not written. An exclusive lock is added when writing. Other transactions cannot be written or read concurrently.

Finally, read commit and repeatable read. These two isolation levels are complex, allowing for a certain amount of concurrency while simultaneously trying to solve the problem.

Implement repeatable reads

To solve non-repeatable reads, or to achieve repeatable reads, MySQL adopts MVCC (Multi-version Concurrency Control).

A row that we see in a database table may actually have multiple versions. Each version of the record has the data itself and a field representing the version, called Row trx_id, which is the ID of the transaction that generated it. Transaction ID is called Transaction ID. It applies to the transaction system at the beginning of a transaction, increasing in chronological order.

For example, if transaction ID of transaction A is 100, row TRx_id of version 1 will be 100, and row trx_id of version 2 and 3 will be 100.

In described above to submit and repeatable read all mentioned a word, is called a snapshot, scientific name called consistent view, this is the key to repeatable read and not repeatable read, repeatable read at the beginning of the transaction is to generate a snapshot of the current transaction global and read every time is executing statements submitted to regenerate a snapshot.

For a snapshot to be able to read that version data, follow the following rules:

1. Updates within the current transaction can be read;
2. Version not submitted, cannot read;
3. Version committed, but committed after snapshot creation, cannot be read;
4. The version is submitted before snapshot creation and can be read.

Using the rule above, it is clear to go back and apply it to the read commit and repeatable read diagrams. Again, the main difference is that in snapshot creation, repeatable reads are created only once at the start of a transaction, whereas read commits are recreated each time a statement is executed.

Concurrent write problem

There are cases where two transactions are making changes to the same data. The final result should be the result of which transaction, it must be the later one. In addition, the data must be read before the update. The read before the update is called “current read”, which is always the current version of the data, that is, the latest version committed.

If transaction A performs an update operation, the update operation requires A row lock on the modified row, which will be released after the commit. And before the transaction is A submission, transaction B also want to update the row data, then apply for row locks, A possession, but because has been affairs transaction B is not to apply for, at this point, the transaction B will have been waiting, until the transaction commit, A transaction B can continue, if the transaction A too long, the transaction is likely to see B timeout exception. As shown in the figure below.

Locking can be indexed or unindexed, as in the following statement

update user set age=11 where id = 1

Copy the code

Select * from table where id = primary key; select * from table where id = primary key; select * from table where id = primary key;

And the following statement

update user set age=11 where age=10

Copy the code

The age field is not indexed in the table, so MySQL cannot locate this row directly. So what to do, of course, not add a table lock. MySQL will lock all rows in this table, yes, all rows. However, after adding a row lock, MySQL performs a filter and releases the lock if it finds an unqualified row, leaving only qualified rows. Although only the eligible rows are locked, the process of releasing the lock can have a significant impact on performance. Therefore, if the table is large, it is recommended to design the index properly, if this is the case, it is difficult to guarantee concurrency.

Solve the phantom read

In the case of repeatable reads above, the phantom indicated in the diagram does not actually occur in MySQL, MySQL has solved the phantom problem at repeatable read isolation level.

MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL

Suppose you now have two records in the table, and the age field has been indexed, with values of 10 and 30.

At this point, a SET of B+ trees is maintained for the index in the database to quickly locate row records. The B+ index tree is ordered, so it splits the index of the table into intervals.

As shown in the figure, it is divided into three intervals, (minus infinity,10], (10,30) and (30, plus infinity], in which gap locks can be added.

After that, I demonstrate the locking process with the following two transactions.

The insert operation of transaction B has to wait until transaction A commits, which is where the gap lock comes in. Update user set name=’ age ‘where age = 10; Where age =10, the database not only adds A row lock on the row where age =10, but also adds A gap lock on both sides of the record, namely (minus infinity,10], (10,30]), so that the transaction B insert operation cannot be completed and can only wait for transaction A to commit. Not only do records with age = 10 need to wait for transaction A to commit, but records with age<10, 10

This is the case with indexes. If age is not an indexed column, then the database places a gap lock on the entire table. Therefore, if there is no index, regardless of whether age is greater than or equal to 30, it will wait for transaction A to commit before successfully inserting.

conclusion

MySQL’s InnoDB engine only supports transactions, where repeatable reads are the default isolation level.

Read uncommitted and serialization are basically isolation levels that don’t need to be considered; the former is unlocked, and the latter is equivalent to single-threaded execution and inefficient.

Read commits solve the problem of dirty reads and row locks solve the problem of concurrent updates. And MySQL solves the illusory problem at the repeatable read level through the combination of row lock and gap lock next-key lock.

Creation is not easy, small praise, big warm, warm me quickly. You’re welcome. Like me!

I am kite, public id “ancient Time kite”, I have been working in the application circles for many years, mainly Java, Python, React also play very 6 slash developer. Can add my friends in the public account, into the group of small partners exchange learning, a lot of dachang students are also in the group yo.