Database transaction ISOLATION LEVEL __ Database

This article is reproduced, the original address: http://singo107.iteye.com/blog/1175084

There are 4 isolation levels for database transactions, from low to high, read uncommitted, Read committed, Repeatable read, Serializable, which can resolve the problems of dirty reading, non-repeatable reading, and phantom reading, respectively.

√: May appear x: does not appear

	Dirty Read	Do not read repeatedly	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Note: We discuss isolation-level scenarios, mainly in cases where multiple transactions are concurrent, so the next explanation revolves around transactional concurrency. READ UNCOMMITTED not submitted

The company wages, the leader to 5000 yuan to Singo's account, but the transaction has not been submitted, and Singo just to check the account, found that wages have to account, is 5000 yuan whole, very happy. Unfortunately, the leader found that the amount of wages issued to Singo is not 2000 yuan, so quickly rolled back the transaction, the amount of modification, the transaction will be submitted, finally Singo actual wages only 2000 yuan, Singo empty joy.

This is what we call dirty reading, two concurrent transactions, "transaction A: Leadership to Singo Payroll", "Transaction B:singo query Payroll account", and transaction B reads data not yet submitted by transaction A.

When the isolation level is set to read UNCOMMITTED, dirty reads may occur and how to avoid dirty reads, see the next isolation level. Read Committed submit

Singo holding a Pay card to consumption, the system read to carry does have 2000 yuan, and at this time her wife is also just online transfer, the Singo Pay Card 2000 yuan to another account, and in Singo before the transaction, when Singo deduction, System Check to Singo Pay Card has no money, deduction failure, Singo very puzzled, clearly card money, why ...

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

Dirty reads are avoided when the isolation level is set to read committed, but may result in non repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. How to troubleshoot the problem of non-repeatable reads, look at the next isolation level. REPEATABLE Read repeat reading

You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo with a pay card to spend, once the system began to read the Payroll card information (that is, the transaction began), Singo's wife can not modify the record, that is, Singo wife can not transfer money 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 looks through the bank's internal system to check Singo's credit card consumption records. One day, she was querying the total consumption amount of the credit card for the month of Singo (select SUM (amount) from transaction where month = this month) was 80 yuan, while Singo was at the checkout at the cashier's desk after eating the sea stopper, consuming 1000 yuan , which adds a 1000-dollar consumption record (insert Transaction ...). ), and submitted the transaction, then Singo wife will Singo monthly credit card consumption details printed to the A4 paper, but found that the total consumption of 1080 yuan, Singo wife very surprised, thought that there was hallucinations, the illusion of reading this produced.

Note: The default isolation level for MySQL is repeatable read. Serializable Serialization

Serializable is the highest transaction isolation level, at the expense of the highest cost, performance is very low, generally rarely used, at this level, transaction order execution, not only to avoid dirty read, not repeatable read, but also avoid phantom reading.