This article describes the four properties of transactions in a database (ACID) and explains the isolation level of transactions in detail.

If a database claims to support transactional operations, it must have the following four features:

⑴ Atomicity

Atomicity refers to the fact that all operations involved in a transaction either succeed or fail and are rolled back. This is the same concept as the previous two blogs introduced the functionality of transactions, so the operation of a transaction must be fully applied to the database if it succeeds, and the operation must not have any impact on the database if it fails.

⑵ Consistency

Consistency means that a transaction must move the database from one consistent state to another, that is, a transaction must be in a consistent state both before and after execution.

In the case of transfer, if user A and user B have A total of 5000, then no matter how many transfers are made between user A and user B, the sum of the two users should still be 5000 at the end of the transaction. This is the consistency of the transaction.

⑶ Isolation

Isolation means that when multiple users concurrently access the database, for example, when operating on the same table, the transactions initiated by the database for each user cannot be disturbed by the operations of other transactions, and multiple concurrent transactions must be isolated from each other.

The effect is that for any two concurrent transactions T1 and T2, from transaction T1’s point of view, T2 either ends before T1 begins or starts after T1 ends, so that each transaction does not feel that any other transaction is executing concurrently.

Databases on transaction isolation provide multiple isolation levels, which will be described later.

(4) Durability.

Persistence means that once a transaction is committed, the changes made to the data in the database are permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

When we use JDBC database operation, for example, after the commit the transaction method, prompt the user transaction completion, when the program execution through until we see after the prompt, it can be concluded that the transaction and submit correctly, even though by this time there was a problem in database, must also will be our transaction completes completely, otherwise it will cause we see prompt transaction processing to complete, But the database failed to execute the transaction because of a failure.

  

Above the four characteristics of the transaction (hereinafter referred to as ACID), now the key to illustrate the isolation of the transaction, when multiple threads are all open transaction data in the database, the database system to able to operate in isolation, to ensure the accuracy of the thread to get the data, introduces the database provided by various isolation level before, Let’s start by looking at some of the problems that can occur if transaction isolation is not considered:

1, dirty reads

A dirty read is one that reads data from another uncommitted transaction during a transaction.

When a transaction is modifying data multiple times and the changes have not yet been committed, a concurrent transaction accessing the data will result in inconsistencies between the two transactions. For example, if user A transfers 100 YUAN to user B, the corresponding SQL command is as follows

Update account set money=money+100 where name= 'B'; Update account set money= money-100 where name= 'A';Copy the code

When only the first SQL is executed, A notifies B to look at the account and B finds that the money has indeed been transferred to the account (A dirty read occurs). However, regardless of whether the second SQL is executed or not, as long as the transaction is not committed, all operations will be rolled back and when B looks at the account again later, the money has not actually been transferred.

2. Cannot be read repeatedly

Non-repeatable reads are those that return different values from multiple queries within a transaction scope for a particular data in the database because it was modified and committed by another transaction during the query interval.

For example, if transaction T1 reads some data, and transaction T2 immediately modifies the data and commits the transaction to the database, transaction T1 reads the data again and gets a different result, sending an unrepeatable read.

The difference between an unrepeatable read and a dirty read is that a dirty read reads uncommitted dirty data from another transaction, while an unrepeatable read reads data from a previous transaction.

In some cases, unrepeatable reads are not a problem, such as when we query the data multiple times and the result of the last query is the main result. But in other cases, problems may occur. For example, for the same data, A and B may be queried in different order, and A and B may fight…

3. Imaginary reading

Phantom reading is a phenomenon that occurs when a transaction does not execute independently. For example, transaction T1 changes an item from “1” to “2” in all rows of a table, and then transaction T2 inserts a row entry into the table with the value of “1” and commits it to the database. If a user of transaction T1 looks at the newly modified data, he or she will find that there is still a row that has not been modified. In fact, this row was added from transaction T2 as if it had been hallucinated.

Unreal and unrepeatable reads both read another transaction that has already been committed (dirty reads are different). The difference is that unrepeatable reads query for the same data item, whereas unreal reads are for a batch of data as a whole (such as the number of data).

 

Now let’s look at the four isolation levels that the MySQL database provides:

① Serializable: can avoid dirty read, unrepeatable read, phantom read.

Repeatable read: can avoid the occurrence of dirty read and unrepeatable read.

③ Read COMMITTED: This can avoid dirty reads.

④ Read uncommitted: The lowest level, cannot be guaranteed in any case.

 

The highest isolation level is Serializable and the lowest is Read Uncommitted. Of course, the higher the isolation level, the lower the execution efficiency. A level like Serializable locks a table (similar to Java multithreading locks) so that other threads can only wait outside the lock, so the isolation level you choose should depend on the actual situation. The default isolation level in the MySQL database is Repeatable Read.

In MySQL database, the above four isolation levels are supported. Repeatable Read is the default; In Oracle databases, only the Serializable level and Read COMMITTED level are supported. The default level is Read COMMITTED.

Check the isolation level of the current transaction in MySQL database:

    select @@tx_isolation;
Copy the code

Set transaction isolation level in MySQL database:

The set [glogal | session] transaction isolation level isolation level name; Set tx_ISOLATION = 'Isolation level name; 'Copy the code

Example 1: View the isolation level of the current transaction:

　　

Example 2: Set the isolation level of a transaction to Read uncommitted:

　　

Or:

　　

Remember: Always set the isolation level of your database before starting a transaction!

If you are using JDBC to set the isolation level for database transactions, this should also be done before calling the setAutoCommit(false) method of the Connection object. To set the isolation level of the current link, call setTransactionIsolation(level) of the Connection object. For the level parameter, use the Connection object’s field:

　　

Part of the code for setting the isolation level in JDBC:

　　

Postscript: Isolation level Settings only apply to the current link. For MySQL command Windows, a window is equivalent to a link, and the isolation level set for the current window only applies to transactions in the current window; For a JDBC operation database, a Connection object is equivalent to a link, and the isolation level set for a Connection object only applies to that Connection object, not to other Connection objects.

 

Reference blog:

www.zhihu.com/question/23…

Dev.mysql.com/doc/refman/…

www.cnblogs.com/xdp-gacl/p/…