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

Database transaction (Database Transaction) refers to a series of operations performed as a single logical unit of work, either all or none of them.

For a logical unit of work to become a transaction, it must satisfy the four characteristics of the transaction (ACID). Namely atomicity (Atomic), consistency (Consistent), isolation (Insulation) and persistence (Duraction).

Atomic: a transaction is a complete operation, and each operation step of a transaction is inseparable. That is, either all or none of them are implemented.

Consistent: the result of transaction execution must change the database from one consistency state to another.

Insulation: the system must ensure that transactions are not affected by other concurrent transactions. For any pair of transactions T1 and T2, from the point of view of T1, T2 either ends before T1 starts or starts execution after T1 completes. Isolation is achieved through a concurrency control mechanism.

Duraction: once a transaction is committed to the database, its updates to the database are no longer affected by subsequent operations or failures.

If we do not consider the isolation of transactions, the following will occur:

1. Dirty reading

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

2. Non-repeatable

Non-repeatable reading means that a transaction reads data in a database many times but returns different results. This is because it was modified and committed by another transaction at the time of the query.

3. Illusory reading

Illusory reading is a phenomenon that occurs when a transaction is not executed independently. For example, transaction T1 modifies some data from "Y" to "N" for all rows in a table, while transaction T2 is inserting a record into the database with a value of "Y". Then when the user operating transaction T1 looks at the data that has just been modified again, they will find that there is still a data record with the value "Y". In fact, it is the data added by transaction T2, which creates a phantom reading.

There are four isolation levels of database transactions, which are Read uncommitted, Read committed, Repeatable read and Serializable from low to high. These four levels can solve the problems of dirty reading, unrepeatable reading and phantom reading one by one.

Dirty reading

Non-repeatable

Illusory reading

Read uncommitted

Y

Y

Y

Read committed

N

Y

Y

Repeatable read

N

N

Y

Serializable

N

N

N

Read uncommitted (read unsubmitted)

For example, A transfers 5000 yuan to B, but A has just transferred 8000 to B account, but the transaction has not been committed yet. Then B went to the account and found that it was overturned by 8000Magi A, so he rolled back the transaction and transferred 5000 to the B account, and when B checked it next time, he found that it was 5000, which led to dirty reading.

Read committed (read submit)

Singo takes the wage card to consume, the system reads that there is indeed 2000 yuan in the card, and at this time his wife also happens to be transferring 2000 yuan of singo wage card to another account, and submitted the transaction before singo. When singo deduction, the system checked that singo wage card has no money, deduction failed, singo is very puzzled, Ming card has money, why.

The above situation occurs, that is, what we call non-repeatable reading, two concurrent transactions, "transaction A:singo consumption" and "transaction B:singo 's wife online transfer". Transaction A reads the data in advance, transaction B immediately updates the data and commits the transaction, and when transaction A reads the data again, the data has changed.

When the isolation level is set to Read committed, dirty reads are avoided, but may result in unrepeatable reads.

Repeatable read (repeat read)

When the isolation level is set to Repeatable read, unrepeatable reads can be avoided. When singo takes the wage card to spend, once the system starts to read the payroll information (that is, the transaction begins), it is impossible for singo's wife to modify the record, that is, singo's wife cannot transfer money at this time.

Although Repeatable read avoids unrepeatable readings, phantom readings are also possible.

Singo's wife, who works in the banking department, often checks singo's credit card records through the bank's internal system. One day, she was inquiring that the total amount of singo's monthly credit card consumption (select sum (amount) from transaction where month = this month) was 80 yuan, while singo was just out eating Haisai and paid the bill at the cashier, spending 1000 yuan, that is, adding a spending record of 1000 yuan (insert transaction.), and submitted the transaction. Then singo's wife printed the details of singo's monthly credit card consumption on A4 paper, but found that the total consumption was 1080 yuan. Singo's wife was surprised and thought there was a hallucination, so the hallucination came into being.

Serializable (serialization)

Serializable is the highest transaction isolation level with the highest cost and low performance, so it is rarely used. At this level, sequential transaction execution can avoid not only dirty and unrepeatable reads, but also phantom reads.

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.