Shulou(Shulou.com)05/31 Report--

This article mainly talks about "the four characteristics of database transactions and what is the isolation level of transactions". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the four characteristics of database transactions and what is the isolation level of transactions".

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

⑴ atomicity (Atomicity)

Atomicity means that all operations contained in a transaction either succeed or fail to roll back, which is the same concept as the function of the transaction described in the previous two blogs, so if the operation of the transaction is successful, it must be fully applied to the database. If the operation fails, it cannot have any impact on the database.

⑵ consistency (Consistency)

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

Take the transfer, for example, assuming that the total money of user An and user B adds up to 5000, then no matter how the money is transferred between An and B, the money of the two users should add up to 5000 after the end of the transaction, which is the consistency of the transaction.

⑶ isolation (Isolation)

Isolation means that when multiple users access the database concurrently, such as operating the same table, the transactions opened by the database for each user cannot be disturbed by the operations of other transactions, and multiple concurrent transactions should be isolated from each other.

That is, to achieve such an effect: for any two concurrent transactions T1 and T2, from the point of view of transaction T1, T2 either ends before T1 starts or starts after T1 ends, so that each transaction does not feel that other transactions are executing concurrently.

The isolation database for transactions provides a variety of isolation levels, which will be described later.

⑷ persistence (Durability)

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

For example, when we use JDBC to operate the database, after committing the transaction method, we prompt the user that the transaction operation is completed, and when our program completes execution until we see the prompt, we can identify the transaction and commit it correctly. Even if there is a problem with the database at this time, our transaction must be fully executed, otherwise we will see that the prompt transaction is completed. But the database failed to execute the transaction because of a failure.

After the above introduction of the four major features of transactions (referred to as ACID), let's focus on the isolation of transactions. When multiple threads open the data in the transaction operation database, the database system should be able to perform isolation operations to ensure the accuracy of data acquisition by each thread. Before introducing the various isolation levels provided by the database, let's take a look at several problems that may occur without considering the isolation of transactions:

1 dirty reading

Dirty reading refers to reading data from another uncommitted transaction during one transaction.

When a transaction is modifying a data many times, and the many modifications in the transaction have not been committed, then a concurrent transaction to access the data will cause the data obtained by the two transactions to be inconsistent. For example, user A transfers 100RMB to user B, and the corresponding SQL command is as follows

Update account set money=money+100 where name='B'; (A notifies B at this time) update account set money=money-100 where name='A'

When only the first SQL is executed, An informs B to check the account, and B finds that the real money has arrived in the account (dirty reading occurs at this time), and then, regardless of whether the second SQL is executed or not, as long as the transaction is not committed, all operations will be rolled back, so when B checks the account again later, it will find that the money has not actually been transferred.

2 non-repeatable reading

Unrepeatable reading means that for a certain data in the database, multiple queries within a transaction range return different data values, which is due to being modified and committed by another transaction during the query interval.

For example, transaction T1 is reading a certain data, and transaction T2 immediately modifies the data and commits the transaction to the database. Transaction T1 reads the data again and gets different results, sending non-repeatable reads.

The difference between unrepeatable reading and dirty reading is that dirty reading is that one transaction reads dirty data not committed by another transaction, while unrepeatable reading reads data committed by the previous transaction.

In some cases, unrepeatable reading is not a problem, for example, we query a data many times, of course, mainly based on the results of the final query. But in other cases, problems may occur, for example, for the same data An and B query may be different, An and B may fight.

3 virtual reading (phantom reading)

Illusory reading 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", and transaction T2 inserts a row of data items into the table, and the value of this data item is still "1" and submitted to the database. On the other hand, if the user who operates transaction T1 looks at the data that has just been modified, he will find that there is still one line that has not been modified. In fact, this line is added from transaction T2, which is like an illusion, which is a hallucination.

Both phantom reading and unrepeatable reading read another committed transaction (this is different from dirty reading), except that the unrepeatable query is the same data item, while phantom reading is for a batch of data as a whole (such as the number of data).

Now let's take a look at the four isolation levels that the MySQL database provides for us:

① Serializable (serialization): can avoid dirty reading, unrepeatable reading, and phantom reading.

② Repeatable read (repeatable): can avoid dirty reading and non-repeatable reading.

③ Read committed (read submitted): can avoid dirty reading.

④ Read uncommitted (read unsubmitted): lowest level, no guarantee under any circumstances.

The highest level of the above four isolation levels is the Serializable level, and the lowest is the Read uncommitted level. Of course, the higher the level, the lower the execution efficiency. A level like Serializable means that other threads can only wait outside the lock in the way of locking tables (similar to locks in Java multithreading), so which isolation level should be chosen according to the actual situation. The default isolation level in the MySQL database is Repeatable read (repeatable).

In the MySQL database, the above four isolation levels are supported, and the default is Repeatable read (repeatable read), while in the Oracle database, only the Serializable (serialization) level and Read committed (read committed) level are supported, where the default is the Read committed level.

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

Select @ @ tx_isolation

Set the isolation level for transactions in the MySQL database:

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

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

Example 2: set the isolation level of the transaction to Read uncommitted level:

Or:

Remember: set the isolation level of the database before you start the transaction!

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

Part of the code to set the isolation level in JDBC:

At this point, I believe you have a deeper understanding of the "four characteristics of database transactions and what is the isolation level of transactions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.