Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the four isolation levels of MySQL ACID". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what are the four isolation levels of MySQL ACID"?

The following is a classic example of a banking application that explains the necessity of transactions. Suppose a bank's database has two tables: a cheque statement (checking) and a savings statement (savings). Now to transfer $200 from user Jane's checking account to her savings account, you need at least three steps:

1. Check that the checking account balance is greater than or equal to $200.

2. Subtract $200 from the checking account balance.

Add 200 US dollars to the savings account balance.

The operations of the above three steps must be packaged in a transaction, and if any of the steps fail, all steps must be rolled back.

You can start a transaction with a START TRANSACTION statement, and then either use COMMIT commit to persist the modified data or use ROLLBACK to undo all changes. The sample transaction SQL is as follows:

1. Start transaction

2. Select balance from checking where customer_id = 10233276

3. Update checking set balance = balance-200.00 where customer_id = 10233276

4. Update savings set balance = balance + 200.00 where customer_id = 10233276

5. Commit

ACID stands for atomicity, consistency, isolation, and durability. A good transaction processing system must have these standard features:

Atomicity (atomicity)

A transaction must be regarded as an indivisible minimum unit of work, and all operations in the whole transaction either commit successfully or fail to roll back. For a transaction, it is impossible to perform only part of the operations. This is the atomicity of the transaction.

Consistency (consistency)

A database always transitions from one consistent state to another. (in the previous example, consistency ensures that even if the system crashes between the third and fourth statements, $200 will not be lost in the checking account because the transaction is not committed in the end. so changes made in the transaction are not saved to the database. )

Isolation (isolation)

In general, changes made by one firm are not visible to other transactions until they are finally committed. (in the previous example, when the third statement and the fourth statement were not started, another account aggregator started, and it saw that the checking account balance had not been subtracted by $200. )

Persistence (durability)

Once a transaction commits, its changes are not permanently saved to the database. (even if the system crashes, the modified data will not be lost. Persistence is a vague concept, because there are actually many different levels of persistence. Some persistence policies provide very strong security, while others may not, and it is impossible to have a policy that can achieve 100% persistence assurance. )

Isolation level:

READ UNCOMMITTED (read not submitted)

At the READ UNCOMMITTED level, changes in a transaction are visible to other transactions, even if they are not committed. Transactions can read uncommitted data, which is also known as Dirty Read. This level can cause a lot of problems. In terms of performance, READ UNCOMMITTED is not much better than other levels, but it lacks many of the benefits of other levels, unless there is a really necessary reason to rarely use it in practical applications.

READ COMMITTED (submit for read)

The default isolation level for most database systems is READ COMMTTED (but MySQL is not). READ COMMITTED satisfies the simple definition of isolation mentioned earlier: at the beginning of a transaction, you can only "see" the changes made by the committed transaction. In other words, any changes made by one transaction from the beginning to the commit are invisible to other transactions. This level is sometimes called unrepeatable nonrepeatble read, because executing the same query twice may get different results.

REPEATABLE READ (repeatable)

REPEATABLE READ solves the problem of dirty reading. This isolation level ensures that the results of reading the same records multiple times in the same transaction are consistent. But in theory, the repeatable isolation level still doesn't solve another Phantom Read problem. The so-called illusory reading means that when a transaction reads a record in a certain range, another transaction inserts a new record in that range, and when the previous transaction reads the record in that range again, it will produce a Phantom Row. InnoDB and XtraDB storage engines solve the problem of phantom reading through multi-version concurrency control (MVCC,Multiversion Concurrency Control).

SERIALIZABLE (serializable)

SERIALIZABLE is the highest isolation level. It avoids the problem of phantom reading mentioned earlier by forcing the serial execution of transactions. Simply put, SERIALIZABLE locks every row of data it reads, so it can cause a lot of timeouts and lock contention problems. This isolation level is also rarely used in practical applications, and it is considered only when there is a great need to ensure data consistency and it is acceptable that there is no concurrency.

Ticking indicates that this situation still exists at the isolation level, and ticking X indicates that the isolation level has resolved the situation:

At this point, I believe you have a deeper understanding of "what are the four isolation levels of MySQL ACID". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.