Detailed Database transactions

I. What is a database transaction

A database transaction is a set of operations against a database that either succeeds or fails altogether.

Ii. Four characteristics of database transactions

The four characteristics of a transaction are commonly said acid, the corresponding English and Chinese meanings are:

Atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability)

Third, the isolation of the transaction

Let's take a look first. Problems that occur if the isolation of a transaction is not considered

A, dirty read

The so-called dirty read refers to a transaction T1 read the transaction T2 uncommitted data, if the T2 transaction is not committed at the end, then T1 later read the data is not the same

B, non-repeatable read

The so-called non-repeatable read refers to a transaction T1 read transaction T2 data, many times within the data is not the same

C, Phantom Read (virtual Read)

The so-called Phantom reading refers to the transaction T1 to own a certain item of data all modified T2 and the data to modify, T1 in reading will find a different

For these issues, there is a different level of transaction isolation, which is described in a table below

Note: This is the transaction isolation level for MySQL, and Oracle has only read commited and serializable two types. The default isolation level for MySQL is repeatable read,oracle, which is read comiitted.

Here are some examples of living things to explain the isolation level of a transaction:

READ UNCOMMITTED not submitted


The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.


When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

Read Committed reading Commit


Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...


The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.


When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads.


To resolve the issue of non-repeatable reads, see the next isolation level.

REPEATABLE READ repeat


You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads. Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Serializable Serial Read


Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.