Talking about the transaction isolation level of MySQL

Hopefully this article will be able to articulate four concepts related to databases: transactions, database reads, isolation levels, lock mechanisms

First, the business

First look at the Baidu Encyclopedia of the definition of database transactions:

Perform a series of operations as a single logical unit, either fully or completely without execution. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully.

A transaction has four properties, called an acid attribute:

1. atomicity (atomicity): A transaction is an atomic unit that either executes entirely or does not execute at all.

2, consistency (consistent): The beginning and end of the transaction, the data must be consistent state.

3, Isolation (Isolation): The database system provides an isolation mechanism to ensure that concurrent transactions are not interfering with each other. This means that the intermediate states in the transaction process are transparent to the other transactions.

4, Persistence (durable): After the transaction is completed, the modification of the data is permanent, even if a system failure can be maintained

A transaction is a collection of a series of SQL statements, and what happens if there are no transactions? Or that SQL can only be executed in one single piece, What will happen?

This is very simple, if there is no business, we usually live in the bank transfer can not operate.

Second, the phenomenon of database reading

　　One of the ACID properties is the isolation level, which means that concurrent transactions do not interfere with each other. Not interfering with each other is only a final state, and it consumes huge performance. In our actual application process, there is a large gray space: the level of isolation to a degree of distinction. Therefore, if the degree of isolation control is relatively weak, it will produce dirty reading , non-repeatable reading and Phantom reading phenomenon.

1. Dirty reading

The transaction T1 modifies the value of a field, and then the transaction T2 reads the value, and then T1 either revokes the update to the field or updates it to a different value to commit to the database, so that the data T2 read is invalid or incorrect. It is also wrong to cause T2 to perform operations on dirty data.

---------Crestron classmate Noon to the canteen to eat, see the window side of the seat was like flower classmate possessed, Crestron think this seat has been possessed, turned to find other seats. Unexpectedly, such as flowers classmate got up and left. The fact is: if the flower is not eating, but sitting there temporarily waiting for her date, only temporarily sit for a while, and there is no real "commit".

2. Non-repeatable reading

In database access, two of the same queries within a transactional scope return different data.

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

---------Crestron classmate Noon to eat in the canteen, see the window seat is empty, then fart on the run to play rice, back to find this seat was like flowers classmate Rob.

3. Phantom reading

The Phantom read solves the problem of non-repeatable reads, that is, within the same transaction scope, two times the same query result is the same. However, you can add data records from a table.

Phantom reading refers to the transaction T1 the data in the table, assuming that the modification involves all rows of data in the table, and that the second transaction modifies the data in the table by inserting a new piece of data into the table. After that, the user who T1 the transaction is found to have no modified data rows in the table, as if there were hallucinations.

--------Crestron classmate at noon to eat, see the window seat is empty, then fart on the side of the run to play rice, back to the window seat or empty, it is very happy to sit up and ready to start to eat, this time but found such as flower classmate moved a small bench sitting next to gobble, Crestron suddenly no appetite.

If you need to address these database reads, such as dirty reads, non-repeatable reads, and Phantom reads, you must increase the isolation level of the transaction accordingly. But the higher the isolation level of the database, the weaker the corresponding concurrency, and the worse the performance, so we need to weigh it against the specific application scenario.

Third, transaction isolation level1, not submitted to read

The lowest isolation level of a transaction under which one transaction can read uncommitted data from another transaction.

Database Lock Implementation principle:

Transaction t does not lock data when reading data, transaction T adds row-level shared locks to data when modifying data

T1 when reading data, T2 can read and modify the same data. Because T1 does not perform any lock operations, when T2 changes the record, T1 reads the data again to read the T2 modified data. Because T2 modifies data only by adding row-level shared locks, T1 can then increase the shared read lock for data read (although T2 does not commit the transaction)

As mentioned above, this isolation level can lead to dirty read behavior

2, has been submitted to read

During a transaction modifying data, other transactions cannot read the data if the transaction is not committed

Database Lock Implementation principle:

Transaction T increases row-level shared locks when reading data, and is immediately released when the read is finished, and transaction T increases row-level exclusive locks when the data is modified until the end of the transaction is released.

T1 in the process of reading data, T2 can also read the same data, but can not be modified (T1 added is a shared lock, T2 can also increase the share lock, but cannot increase the exclusive lock). T1 the shared lock is released immediately after the read is finished, the T2 can add an exclusive lock to modify the data, and T1 cannot read the data or modify it until the T2 transaction ends.

As mentioned above, this isolation level resolves the dirty read problem, but does not resolve the non-repeatable read behavior.

3. Repeatable READING

Transaction T when the data is read, the row-level shared lock must be increased until the end of the transaction, and when transaction T modifies the data, it must increase the row-level exclusive lock until the data ends.

Database Lock Implementation principle:

T1 in the process of reading data, T2 can also read the same data, but can not be modified (T1 added is a shared lock, T2 can also increase the share lock, but cannot increase the exclusive lock). The shared lock is not released until the T1 transaction is complete, and the T2 can add an exclusive lock to modify the data.

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

T1 query, read 10 records, and 10 records added row-level lock, at this time T2 is unable to modify the 10 rows of data, but because there is no table-level lock, it can add a record to meet the T1 query criteria. Then T1 in the query, it will find that although 10 records did not change, but suddenly a record.

4. Serialization

Phantom reads are generated because no range locks are added when no scope query is made.

Database Lock Implementation principle:

When a transaction t reads data, it must first increase the table-level shared lock until the end of the transaction, and when transaction T modifies the data, it must first increase the table-level exclusive lock until the end of the transaction is released.

T1 the table-level shared lock is added when reading a table, T2 can also read a table, but cannot modify any data until the T1 transaction ends. The T2 can then increase the table-level exclusive lock on table A, at which point T1 cannot read any of the data in table A, and cannot be modified.

As mentioned above, the serializable solution reads such as dirty read, non-repeatable read, Phantom Read, but the isolation level is getting higher and lower in concurrency.

Iv. Operational practice of transactions

By default, MySQL is automatically committed, which means that when we execute an UPDATE statement, MySQL automatically helps us to submit it, as soon as we do not show the Execute commit command. But this applies only to the execution of a single SQL.

If we want to execute multiple SQL at the same time, and there are SQL execution exceptions during execution, we need to roll back the previously executed SQL or eventually roll back all, you must display the usage transaction.

1. Start a transaction: Start TR ansaction or begin;

2, Commit the transaction: commit;

3, ROLLBACK TRANSACTION: rollback;

4, after the transaction commits the operation: chain;

5, after the transaction rollback operation: release;

6, modify the current connection of the submission method: Set autocommit, if set autocommit=0, then all transactions after the set will need to explicitly pass the command to commit or rollback.

Querying the transaction isolation level of the current session

Querying the transaction isolation level of the current system

Modify the transaction isolation level for the current session

Submit a read demo

Client A opens the transaction and updates the data

At this point, the transaction is not committed, open client B, and query, the data is not updated before the

Client a commits the transaction, and then client B queries, at this time the latest data

Commit and Chain demo

If you use commit and chain at commit time, start a new transaction immediately after committing

A after the transaction is committed, B then queries

open transactions are implicitly unlocked

When a new transaction is started with the start transaction command during the lock table, the unlock is implicitly executed tables

A write-lock operation on a table

At this point B query: Because a lock table, so the query is blocked

A open a transaction

Because a opens the transaction, implicitly frees the write lock, so B's query is no longer blocked

Use of SavePoint

You can specify a part of a rollback transaction by defining SavePoint in a transaction
A open the transaction and insert a record, and set the SavePoint

b query, query to open the transaction before the data

A again inserts a piece of data and then rolls back to SavePoint

B to query

Talking about the transaction isolation level of MySQL