About MySQL transaction isolation level and MySQL transaction isolation level

About MySQL transaction isolation level and MySQL transaction isolation level

This article elaborates four concepts related to databases: transactions, database read phenomena, isolation levels, and lock mechanisms.

I. Transactions

Let's take a look at Baidu encyclopedia's definition of database transactions:

Perform a series of operations as a single logical unit, either completely or completely. Transaction Processing ensures that data-oriented resources are not updated permanently unless all operations in the transaction unit are successfully completed.

A transaction has four attributes, called ACID attributes:

1. Atomicity: a transaction is an atomic unit, either executed in full or not.

2. Consistent: the start and end of the transaction, and the data must be Consistent.

3. isolation: the database system provides an isolation mechanism to ensure that concurrent transactions do not interfere with each other. This means that the intermediate state in the transaction processing process is transparent to other transactions.

4. Durable: after the transaction is completed, the modification to the data is permanent and can be maintained even if a system failure occurs.

A transaction is a collection of SQL statements. If there is no transaction, what will happen? In other words, the SQL statement can only be executed one by one. What are the problems?

This is very simple. If there is no transaction, bank transfers in our daily life will be unable to be performed.

Ii. Database reading

One of the ACID attributes is the isolation level, that is, the concurrent transactions do not interfere with each other. Mutual interference is only the ultimate state, and requires a huge amount of performance. In our practical application, there is a large gray space: Isolation levels are differentiated to a certain extent. Therefore, if the degree of isolation is relatively weakDirty read,Non-repeated readAndPhantom read.

1. Dirty read

Transaction T1 modifies the value of a field, and then transaction T2 reads the value. After that, T1 unupdates the field or updates it to another value to commit it to the database, in this way, the data read by T2 is invalid or incorrect. The operations performed by T2 Based on dirty data are also incorrect.

Si Cong went to the dining room for dinner at noon and saw the window seat occupied by Ru Hua. Si Cong thought the seat had been occupied and turned to find another one. Unexpectedly, huhua got up and left. The fact is that, for example, flowers are not for dinner, but temporarily sitting there to wait for her appointment object. They just sit down for a while, and there is no real "commit ".

2. Non-repeated read

During database access, two identical queries within a transaction range return different data.

Transaction T1 reads a data, and transaction T2 reads and modifies the data. T1 re-reads the data to verify the read value, but finds different results.

Si Cong went to the dining room for dinner at noon. When he saw that the window was empty, he ran to dinner. When he came back, he found that the seat had been snatched by huhua.

3. Phantom read

Phantom read solves the problem of non-repeated reads, that is, within the same transaction range, two identical queries have the same results. However, you can add data records in tables.

Phantom read refers to transaction T1 modifying the data in the table. Assume that the modification involves all the data rows in the table, and the second transaction also modifies the data in the table, this modification inserts a new data into the table. The user who operates the T1 transaction will find that there are still unmodified data rows in the table, as if there were an illusion.

Si Cong went to the dining room for dinner at noon. When he saw that the window was empty, he ran to dinner. When he came back, the window was still empty, so he was very happy to sit up and prepare for dinner, at this time, I found that Si Cong had no appetite when she moved a small bench and sat down next to it.

If you need to solve dirty reads, non-repeated reads, Phantom reads, and other database read phenomena, you must increase the isolation level of transactions accordingly. However, the higher the database isolation level, the weaker the corresponding concurrency capability, and the worse the performance. Therefore, we need to weigh against specific application scenarios.

Iii. transaction isolation level

1. uncommitted read

The lowest isolation level of a transaction. At this isolation level, a transaction can read uncommitted data from another transaction.

Database lock implementation principle:

Transaction T does not lock the data while reading the data, and transaction T adds a row-level shared lock to the data when modifying the data.

When T1 reads data, T2 can read and modify the same data. Because T1 does not perform any lock operation, when T2 modifies the record, T1 reads data again and can read the data after T2. Because T2 only adds row-level shared locks for data modification, T1 can add shared read locks for Data Reading (although T2 does not commit transactions)

As mentioned above, this isolation level will cause dirty reading.

2. Committed read

When a transaction modifies data, if the transaction is not committed, other transactions cannot read the data.

Database lock implementation principle:

Transaction T adds a row-level shared lock when reading data. Once the read ends, it is immediately released. Transaction T adds a row-level exclusive lock when modifying data until the transaction ends.

T1 can also read the same data while reading data, but it cannot be modified. (T1 adds a shared lock and T2 also adds a shared lock, but cannot add exclusive locks ). After reading T1, the shared lock is released immediately. At this time, T2 can add an exclusive lock to modify the data. At this time, T1 neither can read nor modify the data, until the T2 transaction ends.

As mentioned above, this isolation level solves the dirty read problem, but cannot solve the problem of non-repeated read.

3. Repeatable read

When transaction T reads data, it must add a row-level shared lock until the transaction ends. When transaction T modifies data, it must add a row-level exclusive lock until the data ends.

Database lock implementation principle:

T1 can also read the same data while reading data, but it cannot be modified. (T1 adds a shared lock and T2 also adds a shared lock, but cannot add exclusive locks ). The shared lock is released until the transaction T1 ends. At this time, the exclusive lock can be added to T2.

As mentioned above, this isolation level solves the problem of non-repeated reading, but this isolation level does not solve the problem of phantom reading:

T1 queries, reads 10 records, and adds row-level locks to the 10 records. At this time, T2 cannot modify the 10 rows of data, but because there is no table-level locks, it can add a record that meets the T1 query conditions. Later, when T1 queries, it will find that although 10 records have not changed, there is suddenly another record.

4. serialization

Phantom read is generated because no range lock is added when range query is not performed.

Database lock implementation principle:

When transaction T reads data, it must first add a table-level shared lock until the transaction ends. When transaction T modifies data, it must first add a table-level exclusive lock, it is not released until the transaction ends.

When table T1 reads Table A, it adds A table-level shared lock. At this time, table T2 can also read Table A, but no data can be modified until the transaction of table T1 ends. Then T2 can add an exclusive lock to Table A. At this time, table T1 cannot read any data in Table A, and cannot be modified.

As mentioned above, serialization solves dirty reads, non-repeated reads, Phantom reads, and other read phenomena, but the isolation level is getting higher and higher, and the concurrency is getting lower and lower.

Iv. Transaction operation practices

By default, MYSQL is automatically submitted, which means that MYSQL automatically submits an update statement. As soon as possible, the commit command is not displayed. However, this method is only applicable to the execution of a single SQL statement.

If we want to execute multiple SQL statements at the same time and there is an SQL Execution exception in the execution process, we need to roll back the SQL statements that have been successfully executed or eventually want to roll back all of them, the transactions that must be displayed.

Query the transaction isolation level of the current session

Query the transaction isolation level of the current system

Modify the transaction isolation level of the current session

Read submission demo

Client A starts transactions and updates data

At this time, the transaction has not been committed. Enable client B and perform the query. At this time, the data is still not updated before

Client A submits transactions and then client B queries the latest data.

Demonstration of commit and chain

If you use commit and chain when submitting, start a new transaction immediately after committing.

After A commits the transaction, B queries it again.

Opening a transaction will be implicitly unlocked

When you use the start transaction command to start a new transaction during table lock, unlock tables is implicitly executed.

A. Write locks the table

At this time, B queries: the query is blocked because the table is locked by.

A. Start A transaction.

Because A starts the transaction and implicitly releases the write lock, the query of B is no longer blocked.

Use of SAVEPOINT

In a transaction, you can define a SAVEPOINT to specify a part of the rollback transaction.
A. Start the transaction, insert A record, and set the savepoint.

B. query the data before the transaction is started.

A inserts another piece of data and rolls back to the savepoint.

B. Query

Author: dongguacai
Original article: http://www.cnblogs.com/dongguacai/p/7114885.html

More Mysql reference: http://www.roncoo.com/article/index? Tn = Mysql