Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the concept and four characteristics of database transaction and isolation level". In daily operation, I believe many people have doubts about the concept and four characteristics of database transaction as well as isolation level. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the concept and four characteristics of database transaction and isolation level". Next, please follow the editor to study!

Concept

A transaction refers to a series of operations that satisfy the characteristics of ACID. In the database, you can commit a transaction through Commit, or you can roll back using Rollback.

Four characteristics

1. Atomicity: transactions are treated as indivisible minimum units, and either all commit successfully or all fail to roll back. two。 Consistency: the consistency state is maintained before and after the transaction is executed. In a consistent state, all transactions read the same data. 3. Isolation: changes made by one transaction are not visible to other transactions until they are finally committed. 4. Durability: once a transaction commits, its changes are saved to the database forever. Even if the system crashes, the result of transaction execution cannot be lost. Persistence can be ensured through database backup and recovery.

After understanding the four major features of the database, let's discuss the isolation level of the database. Before that, we consider the problems that may occur in multi-user concurrent operations without database isolation.

Concurrency consistency problem

In a concurrent environment, if one transaction is affected by another transaction, then the transaction operation can not meet the consistency condition.

1. Lost modification: multiple transactions read a certain data at the same time, and one transaction successfully processed the data and was written back to the original value by another transaction, resulting in the loss of the first transaction update. For example, both T1 and T2 transactions modify a data. T1 modifies first, T2 then modifies, and T2 changes overwrite T1 modifications. two。 Dirty reading: dirty reading refers to reading data from another uncommitted transaction during one transaction. For example, T1 modifies a data, and T2 then reads that data. If T1 undoes this modification, the data read by T2 is dirty. 3. Non-repeatable read: non-repeatable read means that for some data in the database, multiple queries within the scope of a transaction return different data values. This is due to being modified and committed by another transaction during the query interval. For example, T2 reads a piece of data and T1 modifies it. If T2 reads this data again, the result of the read at this time is different from that of the first time. 4. Illusory reading (virtual reading): phantom reading is a phenomenon that occurs when a transaction is not executed independently. For example, T1 reads a certain range of data, T2 inserts new data in this range, and T1 reads this range of data again, and the result is different from that of the first time.

Solution method

The main reason for the concurrency inconsistency is to destroy the isolation of transactions, and the solution is to ensure isolation through concurrency control. Concurrency control can be achieved through blocking, but locking operations have to be controlled by users themselves, which is quite complex. The database management system provides the isolation level of transactions, allowing users to deal with concurrency consistency issues in an easier way.

Now let's take a look at the four isolation levels of transactions.

Isolation level

1. Uncommitted reads (READ UNCOMMITTED): changes in a transaction that are visible to other transactions even if they are not committed. At the lowest level, there is no guarantee under any circumstances. two。 Commit READ COMMITTED: a transaction can only read changes made by committed transactions. In other words, changes made by one firm are not visible to other transactions until they are committed. Dirty reading can be avoided. 3. REPEATABLE READ: ensures that the result of reading the same data multiple times in the same transaction is the same. The occurrence of dirty and unrepeatable reading can be avoided. 4. Serializable (SERIALIXABLE): forces transactions to be executed serially. It can avoid dirty reading, unrepeatable reading and phantom reading.

Comparison of four isolation levels

Isolation level dirty read non-repeatable uncommitted read YESYESYES committed read NOYESYES repeatable readable NONOYES serializable NONONO

The highest of the above four isolation levels is the SERIALIXABLE (serializable) level, and the lowest is the READUNCOMMITTED (uncommitted read) level. Of course, the higher the level, the lower the execution efficiency. A level like SERIALIXABLE (serializable) is to lock the table so that other threads can only wait outside the lock, so the isolation level you usually choose should be based on the actual situation.

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 SERIALIXABLE (serialization) level and READCOMMITTED (read committed) level are supported, where the default is the READCOMMITTED level.

At this point, the study on "the concept and four characteristics of database transactions and isolation levels" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.