4 Isolation levels for understanding transactions

There are 4 isolation levels for database transactions, from low to high for READ UNCOMMITTED, Read committed, Repeatable read, Serializable. Also, dirty reads may occur in concurrent operations of a transaction, not read repeatedly, and Phantom reads. Their concepts and relationships are described below through case one by one.

Read UNCOMMITTED

READ UNCOMMITTED, as the name suggests, is a transaction that can read data from another uncommitted transaction.

Example: The boss will pay the programmer, the programmer's salary is 36,000/month. But the boss accidentally presses the wrong number when he's paid. Press into 39,000/month, the money has been hit the programmer's account, but the transaction has not yet been submitted, at this time, the programmer to check their salary this month, found that more than usual 3,000 yuan, thought the salary rose very happy. But the boss in time to find the wrong, immediately roll back almost on the transaction submitted, the number to 36,000 and then submitted.

Analysis: The actual programmer's salary is 36,000 this month, but the programmer sees 39,000. What he saw was that the boss had not yet submitted the transaction data. This is dirty reading.

Then how to solve dirty reading. Read committed. Read submit, can solve dirty read problem.

Read committed

Read submissions, as the name suggests, is a transaction to wait for another transaction to be committed before the data can be read.

Example: The programmer takes a credit card to enjoy life (Cary is only 36,000), and when he pays the bill (programmer affairs opens), the billing system detects 36,000 of his cards in advance. The programmer's wife had to turn all the money out of the house as a home and submit. When the charging system is ready to deduct, then check the amount of Cary, found that there is no money (the second Test amount of course to wait for the wife to transfer the amount of the transaction submitted). Programmers will be very depressed, obviously Cary is rich ...

Analysis: This is read submission, if there is a transaction to update the data (update) operation, read operation transaction to wait for the update operation transaction to read data, can solve dirty read problem. In this case, however, a two identical query in the scope of the transaction has been returned with different data, which is not repeatable reading.

How do you solve the problem of possible non repeatable reading? REPEATABLE read.

REPEATABLE READ

Repeat read, which is no longer allowed to modify the operation when reading data (transaction Open)

Example: The programmer takes credit card to enjoy life (Cary is only 36,000), when he pays the bill (transaction open, do not allow other transactions update modification operation), the billing system in advance detected that his card has 36,000. His wife could not turn out the money at this time. Then the charging system can be deducted.

Analysis: Repeat reading can solve the problem of non repeatable reading. What should be understood here is that non-repeatable reading corresponds to the modification, the update operation. But there may be a problem with the Phantom reading. Because the Phantom-reading problem corresponds to inserting the insert operation instead of the update operation.

When will there be phantom reading.

Example: Programmers spend 2000 of dollars a day on spending, then his wife went to check out his consumption record today (full table scan FTS, wife affairs Open), see It is really spent 2000 yuan, at this time, the programmer spent 10,000 to buy a computer, that is, add a consumption record, and submit. When the wife prints the programmer's List of consumer records (wife's business submission), it turns out that it took 12,000 of dollars to appear to be hallucinating, which is the illusion of reading.

How to solve the problem of phantom reading. Serializable.

Serializable serialization

Serializable is the highest transaction isolation level at which transactions are serialized sequentially to avoid dirty reads, non repeatable reads, and Phantom reads. However, this transaction isolation level is inefficient and consumes database performance and is generally not used.

It is worth mentioning that the default transaction isolation level for most databases is read committed, such as SQL Server, Oracle. The default isolation level for MySQL is repeatable read.