Four characteristics and isolation levels of database transactions

A database transaction is a series of operations performed as a single logical unit of work, either Transaction or none.

To become a transaction, a logical unit of work must satisfy the four characteristics (ACID) of the transaction. That is, atomicity (Atomic), consistency (consistent), isolation (insulation), and persistence (duraction).

Atomicity (Atomic): A transaction is a complete operation, and the individual steps of a transaction are non-divided. That is either all executed or not executed.

Consistency (consistent): The result of transactional execution must change the database from one consistent state to another .

Isolation (insulation): The system must ensure that transactions are not affected by other concurrent execution transactions. For any pair of transactions T1 and T2, in T1 's view, T2 either ended before the T1 started, or started after the T1 was completed. Isolation is achieved through the concurrency control mechanism .

Persistence (duraction): Once a transaction is committed to a 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 conditions will occur:

1. Dirty Reading

Dirty reads are data that reads another uncommitted transaction during one transaction.

2. Non-repeatable reading

Non-repeatable reading refers to the fact that a transaction is read multiple times in a database, but returns a different result. This is due to the fact that another transaction was modified to commit when the query was made.

3. Phantom Reading

Phantom reading is a phenomenon that occurs when a transaction is not executed independently. For example, a transaction T1 a data from "Y" to "N" for all rows in a table, at which point the transaction T2 is inserting a record into the database and the value is "Y". After the user who is working on the transaction T1 again views the data that has just been modified, it will find a data record with a value of "Y". is actually the data that the transaction T2 adds, which produces the Phantom read.

There are 4 isolation levels for database transactions, from low to high, READ UNCOMMITTED,Read Committed,repeatable read,Serializable , these four levels can solve the problems of dirty reading , non-repeatable reading , and Phantom reading One after the other.

TD width= "163" valign= "Top" >n

	dirty read	non-repeatable read	Phantom read
read Uncommi tted	y	y	y
Read committed	n	y	y
repeatable read	n	N	y
serializable	n	n /td>

READ UNCOMMITTED (not submitted)

For example, a to B transfer 5000 yuan, but a has just started to transfer 8000 to the B account, but has not submitted the transaction. And now B go to the account inside to find more 8000,a behind found more turn, so on the rollback of the transaction turned 5000 to B account, and then B next time to check the time found to be 5000, which produced a dirty read.

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.

REPEATABLE Read (repeat Read)

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 (serialized)

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.

This article is from the "Java to school on Java" blog, please be sure to keep this source http://zhangyh8856643.blog.51cto.com/8846643/1837426

Four characteristics and isolation levels of database transactions