Four features of database transactions, transaction isolation level, and transaction isolation level

Four features of database transactions, transaction isolation level, and transaction isolation level

This article describes the four major features of transactions in the database (ACID), and details the isolation level of transactions.

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

(1) Atomicity)

Atomicity means that all operations contained in a transaction are either successful or rolled back. This is the same concept as the functions of transactions described in the previous two blogs, therefore, if the transaction operation succeeds, it must be fully applied to the database. If the operation fails, it cannot have any impact on the database.

(2) Consistency)

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

Assume that the sum of money between user A and user B is 5000, no matter how much money is transferred between user A and user B, it is transferred several times, after the transaction ends, the sum of the two users should be 5000, which is the transaction consistency.

(3) Isolation)

Isolation: when multiple users access the database concurrently, such as operating the same table, the transactions opened by the database for each user cannot be affected by the operations of other transactions, multiple concurrent transactions must be isolated from each other.

That is, to achieve this effect: For any two concurrent transactions T1 and T2, T2. in transaction T1, T2, or start after T1, so that every transaction does not feel any other transactions are being executed concurrently.

The transaction isolation database provides multiple isolation levels, which will be introduced later.

(4) Durability)

Durability means that once a transaction is committed, the changes to the data in the database are permanent. Even if the database system encounters a fault, the transaction commit operations will not be lost.

For example, when we use JDBC to operate the database, after the transaction method is submitted, the user is prompted to complete the transaction operation. After the execution of our program is completed until the prompt is displayed, the transaction can be identified and correctly committed, even if a problem occurs in the database at this time, we must fully execute our transactions. Otherwise, we will see a prompt that the transaction has been processed, however, the database failed to execute a major transaction error.

The preceding sections describe the four features of transactions (ACID for short). Now we focus on the isolation of transactions. When multiple threads enable the data in the transaction operation database, the database system must be able to perform isolation operations to ensure the accuracy of the data obtained by each thread. Before introducing the various isolation levels provided by the database, let's take a look at the isolation of transactions without consideration, several problems may occur:

1. Dirty read

Dirty reading refers to reading data from another uncommitted transaction during the transaction processing process.

When a transaction is modifying a data multiple times and the modification has not been committed, a concurrent transaction accesses the data, this will cause data inconsistency 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'; (in this case, A notifies B) update account set money = money-100 where name = 'a ';

When only the first SQL statement is executed, A notifies B to check the account. B finds that the money has been paid (dirty read occurs at this time), and then whether or not the second SQL statement is executed, as long as the transaction is not committed, all operations will be rolled back. When B checks the account again later, it will find that the money has not actually been transferred.

2. Repeated read is not allowed.

Non-repeated read refers to the fact that different data values are returned for multiple queries within a transaction range for a certain data in the database. This is because the data is modified and committed by another transaction at the query interval.

For example, when transaction T1 reads a certain data, and transaction T2 immediately modifies the data and submits the transaction to the database, transaction T1 reads the data again and gets different results, the email is sent and cannot be read repeatedly.

The difference between non-repeated reads and dirty reads is that a transaction reads the uncommitted dirty data of another transaction, while non-repeated reads read the data committed by the previous transaction.

In some cases, repeatable reading is not a problem. For example, when we query a data multiple times, the final query result is the main one. However, in other cases, problems may occur. For example, the query of the same data A and B may be different, and the query of A and B may start ......

3. Virtual read (phantom read)

Phantom read is a phenomenon that occurs when a transaction is not executed independently. For example, transaction T1 modifies a data item of all rows in a table from "1" to "2". At this time, transaction T2 inserts a row of data in the table, the value of this data item is "1" and submitted to the database. If a user who operates transaction T1 looks at the modified data, he will find that there is still a row that has not been modified. In fact, this row is added from transaction T2, as if it were an illusion, this is phantom reading.

Both phantom read and non-repeated read another committed transaction (which is different from dirty read). what is different is that non-repeated read queries are the same data item, phantom read targets a batch of data (such as the number of data ).

 

Now let's take a look at the four isolation levels provided by the MySQL database:

① Serializable: it can avoid dirty reads, repeated reads, and Phantom reads.

② Repeatable read: This prevents dirty reads and repeated reads.

③ Read committed (Read committed): prevents dirty reads.

④ Read uncommitted (Read uncommitted): the lowest level, which cannot be guaranteed in any situation.

 

Among the above four isolation levels, the highest is the Serializable level, and the lowest is the Read uncommitted level. Of course, the higher the level, the lower the execution efficiency. The level like Serializable is to lock the table (similar to the lock in Java multithreading) so that other threads can only wait outside the lock, so what isolation level should be selected at ordinary times according to the actual situation. In MySQL databases, the default isolation level is Repeatable read (Repeatable read ).

In MySQL databases, the preceding four isolation levels are supported. The default value is Repeatable read (Repeatable read). in Oracle databases, only Serializable (Serializable) is supported) level and Read committed (Read committed) level. The default value is Read committed level.

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

    select @@tx_isolation;

Set the transaction isolation level in the MySQL database:

Set [glogal | session] transaction isolation level name; set tx_isolation = 'isolation level name ;'

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

　　

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

　　

Or:

　　

Remember: Before starting a transaction, you must set the database isolation level!

If JDBC is used to set the isolation level for database transactions, it should also be before the setAutoCommit (false) method of the Connection object is called. Call setTransactionIsolation (level) of the Connection object to set the isolation level of the current link. As for the level parameter, you can use the Connection object field:

　　

Code for setting the isolation level in JDBC:

　　

Note: The isolation level setting is only valid for the current link. For the MySQL Command window, a window is equivalent to a link, and the isolation level set in the current window is only valid for transactions in the current window; For the JDBC operation database, A Connection object is equivalent to a link, and the isolation level set for the Connection object is only valid for this Connection object, and has nothing to do with other Connection objects.