The isolation level of the database is always mentioned in every interview or technical sharing related to the database. Do you really understand the characteristics of each isolation level and how to choose the appropriate thing isolation level? (This article is for MySQL database only)

1 What problem does MySQL’s transaction isolation level solve?

The transaction isolation level addresses the issue of transaction concurrency.

In fact, the concurrency problem is similar to the thread-safety problem when we write concurrent programs. Thread-safety problem is that multiple threads modify a variable at the same time. If you don’t lock, you can have problems like oversold. Transaction concurrency, in which multiple clients connect to modify a single record in the database at the same time, can also cause similar problems, and the transaction can be rolled back, which further complicates the problem.

We sum up the concurrent problems of things, that is, the following kinds:

  • Dirty read: Transaction A reads the data modified by transaction B, and then transaction B rolls back, so transaction A reads dirty data
  • Non-repeatable read: Transaction A reads the same data for many times. When transaction B reads the same data for many times, it updates the data and commits the data. As A result, the same data read by transaction A is inconsistent.
  • Phantom reading: Object A modifies all the data in the database to another form, but object B inserts A new data at this time. After object A finishes the modification, it finds that there is still one record that has not been modified, just like an illusion. This is called phantom reading.

Conclusion:

It’s easy to confuse unrepeatable and phantom reads, where unrepeatable reads focus on modification and phantom reads focus on addition or deletion.

To solve the problem of unrepeatable reads, you only need to lock the rows that meet the condition. To solve phantom reads, you need to lock the entire table.

2 What isolation levels are available in MySQL?

The SQL standard defines four isolation levels,

MySQL provides several solutions to this problem, including the following:

Transaction isolation level Dirty read Unrepeatable read Phantom read
Read uncommitted (read-uncommitted) Square root Square root Square root
Read committed (read-committed) Square root Square root
Repeatable readThe default (fx)
Serializable

Each of these isolation levels is essentially a level of locking, where everything is ignored that is, nothing is locked, and everything is resolved by converting parallel execution to serial execution.

As the degree of locking becomes stronger, the concurrency performance of the database becomes weaker.

3 What locks are added to each isolation level of MySQL?

  • Read uncommitted (good performance, lots of problems)

    Locking situation:

    • Transactions read data without locking it.
    • Transactions only add row-level shared locks to data when they modify it.

  • Read committed (Isolation level commonly used over the Internet, excellent performance)

    Read committed is actually for things, do not read things that are not committed, so there is no dirty read.

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

    Locking: Only a Record Lock is added to the index

    • The transaction holds a row-level shared lock on the currently read data (the lock is held only when read) and releases the row-level shared lock as soon as the row is read.
    • The moment a transaction updates data (that is, the moment the update occurs), it must be locked exclusively at row level until the end of the transaction.

  • Repeatable read

    Repeatable reads lock up the read data to ensure that the read data is consistent within the transaction.

    MySQL has added Gap Lock and next-key Lock (configurable via configuration file) to solve phantom read problems.

    Add Lock: in order to solve the magic read problem, in support of Record Lock at the same time, also support Gap Lock and next-key Lock;

    • At the moment when a transaction reads some data (that is, the moment when it starts reading), a row-level shared lock is added and released until the end of the transaction.
    • A row-level exclusive lock is added at the moment a transaction updates data (that is, the moment the update occurs) and is not released until the end of the transaction.

  • serialization

    Direct parallel into serial, equivalent to a single thread to perform tasks, performance is very low, generally not used.

    Locking situation:

    • When a transaction reads data, a table-level shared lock must be added to it and released until the end of the transaction.
    • When a transaction updates data, a table-level exclusive lock must be placed on it until the end of the transaction.

Recommend two previous posts:

If you are not familiar with the concept of MySQL lock, you can read MySQL Lock Introduction.

In order to optimize the performance of locking, MySQL adopts MVCC multi-version concurrency control. .

4 summarizes

The isolation level of things is that the database gives us a choice, in terms of performance and data accuracy, depending on the business.

For MySQL, the InnonDB engine only has the term transaction and transaction isolation level.