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

This article will explain in detail the transaction and isolation level of the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Business

A transaction refers to a set of operations that satisfy the ACID feature, either by committing a transaction through Commit or by rolling back using Rollback.

ACID

1. Atomicity (Atomicity)

The transaction is regarded as an indivisible minimum unit, and all operations of the transaction are either committed successfully or all failed rollback. Rollback can be implemented with a rollback log, which records the changes performed by the transaction, which can be performed in reverse during the rollback.

two。 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.

3. 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.

4. 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.

Concurrency consistency problem

1. Dirty reading

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

T1 modifies a data, and T2 then reads the data. If T1 undoes this modification, the data read by T2 is dirty.

two。 Non-repeatable

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.

3. Illusory 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).

T1 reads a certain range of data, T2 inserts new data in this range, and T1 reads this range of data again, and the reading result is different from that of the first reading.

MySQL isolation level

1. Serializable (serialization): forces the serial execution of transactions.

It can avoid dirty reading, unrepeatable reading and phantom reading.

2. Repeatable read (repeatable readable): ensure 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.

3. Read committed (read committed): a transaction can only read changes made by committed transactions.

Dirty reading can be avoided.

4. 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.

The highest isolation level is the Serializable level, and the lowest is the Read uncommitted level. The higher the isolation 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).

Only Serializable (serialization) level and Read committed (read committed) level are supported in Oracle database, where the default is Read committed level.

So much for sharing the transaction and isolation level of the database. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.