MySQL acid and four isolation levels explained

The following is from the third edition of High-performance MySQL, where understanding the acid of transactions and the four isolation levels helps us better understand the workings of the transaction.

Here is a classic example of a banking application that explains the necessity of a transaction. If a bank's database has two tables: the cheque list (checking) and the savings Table (savings). Now to transfer $200 from the user Jane's chequing account to her savings account, it will take at least three steps:

1. Check the balance of the checking account is above or equal to 200 USD.

2. Subtract $200 from the balance of the checking account.

3. Increase the balance of savings account by $200.

The three steps above must be packaged in one transaction, and any one step fails, you must roll back all the steps.

You can start a transaction with the start TRANSACTION statement, and then either use commit commit to persist the modified data or use rollback to undo all modifications. A sample of transactional 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 represents atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability). A good transaction processing system must have these standard features:

atomicity (atomicity)

A transaction must be considered an indivisible minimum unit of work, all operations in the entire transaction either commit successfully or all fail back, and for a transaction it is not possible to perform only a subset of these operations, which is the atomicity of the transaction

consistency (consistency)

The database is always transitioning from one consistent state to another consistent state . (In the previous example, consistency ensured that, even if the system crashed between the execution of the third to fourth statement, there would be no loss of $200 in the checking account because the transaction was eventually not committed, so the changes made in the transaction were not saved to the database.) ）

isolation (Isolation)

in general, changes made by a firm are not visible to other transactions until they are finally submitted . (In the previous example, when the third statement was executed and the fourth statement did not begin, another account rollup started running, and the balance of the checking account was not subtracted by $200.) ）

Persistence (Durability)

　　once a transaction commits, its modifications are not persisted to the database . (This time, even if the system crashes, the modified data is not lost.) Persistence is a vague concept because there are actually many different levels of persistence. Some persistence strategies can provide very strong security, while others may not, and there is no strategy to achieve a 100% durability guarantee. ）

Isolation level:

READ UNCOMMITTED (UNCOMMITTED)

　　At the READ uncommitted level, modifications 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 reads (Dirty read). This level can cause a lot of problems, and in terms of performance, READ uncommitted is not much better than the other levels, but it lacks many of the benefits of other levels, unless there is really a very necessary reason to use it rarely in practical applications.

Read COMMITTED (submit reading)

The default isolation level for most database systems is read commtted (but MySQL is not). READ committed satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only "see" the changes that have been made to the firm that has been submitted. In other words, any changes you make to a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called non-repeatable read (nonrepeatble read), because two times the same query is executed, you may get a different result

REPEATABLE READ (Repeatable Read)

Repeatable read resolves the problem of dirty reads. This isolation level guarantees that the same record is read multiple times in the same transaction. In theory, however, it is not possible to repeat the isolation level to solve the problem of another phantom read (Phantom read). The so-called Phantom reading refers to when a transaction reads a range of records, another transaction inserts a new record within that range, and when the previous transaction reads the record for that range again, a magic line (Phantom row) is generated. The InnoDB and XTRADB storage engines solved the problem of phantom reading through multi-version concurrency control (mvcc,multiversion Concurrency control).

SERIALIZABLE (Serializable)

The serializable is the highest isolation level. It is executed serially by forcing the transaction to avoid the above-mentioned phantom-reading problem. Simply put, serializable will lock up every row of data, so it can cause a lot of timeouts and lock contention issues. This isolation level is rarely used in real-world applications, and is only considered when there is a need to ensure that data is consistent and acceptable without concurrency.

The hook indicates that this isolation level still exists, and hitting x means that the isolation level has resolved this situation:

MySQL acid and four isolation levels explained