preface

I recently studied chapter 1 of High Performance MySQL and learned about the concept of transaction isolation levels. I felt that this area was relatively easy to verify experimentally. The expected results of each level were clear, so I conducted a small experiment to verify each isolation level.

Introduction to isolation Levels

The SQL standard defines four isolation levels for transactions, which are:

  • READ UNCOMMITTED: Changes in a transaction, even if they are not committed, are visible to other transactions.
  • READ COMMITTED: A transaction can only see changes made by COMMITTED transactions.
  • REPEATABLE READ: Guarantees that the results of multiple reads of the same record in a transaction are consistent.
  • SERIALIZABLE: Forces a transaction to be executed serially, with a lock placed on each row read.

preparation

Configuration used

The database is Tencent cloud MySQL, version 5.7; MySQL storage engine is InnoDB; The client is Navicat and the version is 11. The runtime opens two command-line interfaces in Navicat, one called A and one called B, to simulate operations:

The structure of the table

The table used is relatively simple, as follows:

View and change isolation levels

First of all useshow variables like 'tx_isolation';To view the current transaction isolation level as follows:

Transaction Isolation Level set transaction Isolation Level set transaction isolation Level set transaction isolation Level set transaction Isolation Level set transaction Isolation Level Transaction isolation level is the English word for the four levels mentioned above.

Set Session Transaction Isolation Level read uncommitted; A session-level change was made to change the transaction isolation level to ‘Read uncommitted’J. The following can be easily changed.

Next, verify the four transaction isolation levels.

Uncommitted read

First run the statement to change the transaction isolation level, as shown above, but not described here, as follows. Initially, both client queries are the same

Start transaction in A and run update Person set name = ‘Bob’ where ID = 1 change record, but do not commit.

Then look at the record in B.

There is no transaction committed in A, but B reads the changes in A.

After performing rollback in USER A, user B changes to the previous record.

This is an uncommitted read, in which a transaction is not committed and changes are visible to other transactions. This is also known as a ‘dirty read’.

Submit to read

Initial data is as follows:

Update person set name = ‘Bob’ where id = 1 Changes the record, but does not commit.

Then look at the record in B.

As can be seen, the uncommitted transaction statement has no effect on B’s query. Next commit A’s transaction.

At this point, the data in B is changed, which is called commit read. Only the changes committed by other transactions can be seen, solving the ‘dirty read’ problem. However, different data is read twice in the same transaction, which is called ‘non-repeatable reads’.

Repeatable read

Initial data is as follows:

Start transaction in A and run update Person set name = ‘rose’ where ID = 1 change record but do not commit.

Then look at the data in B.

In B, the change is not seen and the transaction in A is committed.

Even though A’s transaction has been committed, B still doesn’t see it. So repeatable read levels solve the problem of dirty reads and unrepeatable reads.

In the book High Performance MySQL, the problem of dirty and unrepeatable reads is solved at the repeatable read level, but the problem of illusory reads remains. The so-called ‘phantom read’ is that when something reads a record in a range, another transaction inserts a new record in that range. When the previous transaction reads the record in that range again, phantom rows will be generated. The book also says that InnoDB solves this problem with MVCC and gap locking. Let’s see if this is the case. Insert into person values(3,’rose’); To insert a record and submit:

View it in B

No new record is read in B. Insert into person values(3,’ Bob ‘)

The primary key of the inserted record is duplicate. Of course, this situation may not be called ‘magic reading’.

serializable

After the read operation is performed on USER A, the update statement is executed on user B.

After submitting A’s transaction, B succeeds immediately.

Note Even the read will be locked, almost impossible to multi-transaction concurrency.

conclusion

This article has carried on the experiment verification to the MySQL each isolation level, has intuitively experienced the characteristics of each isolation level. After that, the principle of MVCC and gap lock should be explored.