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

Here's what MySQL's transaction isolation level can solve. The secret of the text lies in its proximity to the topic. So, gossip will not talk about, we directly look at the following bar, I believe that after reading MySQL transaction isolation level can solve what problem this article you will certainly benefit.

I. PRINCIPLES OF BUSINESS

Transactions must satisfy ACID principles, which are:

1. Atomicity: After the transaction starts, all operations are either completed or not done, and it is impossible to stop at the intermediate link. An error during transaction execution rolls back to the state before the transaction started, and all operations are as if they never happened. That is, the transaction is an indivisible whole.

2. Consistency: The integrity constraints of the database are not broken before and after the transaction begins. For example, A transfers money to B, and A cannot deduct money, but B does not receive it.

3. Isolation: Only one transaction is allowed to request the same data at a time, and different transactions do not interfere with each other. For example, A is withdrawing money from a bank card, and B cannot transfer money to the card until A's withdrawal process is complete.

4. Durability: After a transaction completes, all updates to the database are saved to the database and cannot be rolled back.

II. Problems caused by concurrent transactions

1. Dirty read: Transaction A reads the updated data of transaction B, and then B rolls back the operation, then the data read by A is dirty data. For example:

Transaction B updates the user's age to 22, but the transaction has not yet been committed. At this point transaction A reads the user's age, reads 22 years old, and then goes to do its own processing. Transaction B then rolls back the user's age to 21. In this example, transaction A reads 22 years old as dirty data. http://www.chacha8.cn/detail/1132398235.html

2. Non-repeatable reads: Transaction A reads the same data multiple times, and transaction B updates and commits the data during the multiple reads of transaction A, resulting in inconsistent results when transaction A reads the same data multiple times. For example:

Transaction B first updates the user's balance to 100 yuan, transaction A reads 100 yuan when it reads, and then transaction B updates the balance to 90 yuan. At this time, transaction A reads 90 yuan when it reads, that is, the data read by transaction A is inconsistent.

3. Phantom read: Transaction A modifies the data in the table, involving all rows in the table. At the same time, transaction B modifies the data in this table, inserting a new row into the table. Transaction A then discovers that there are rows in the table that it has not modified, as if an illusion had occurred. For example:

A clears everyone's balance first, and B inserts a piece of data with a balance of 100 into the table. After submitting it, I found that there were still 100. It's like a hallucination. It's called hallucination.

MySQL transaction isolation level

MySQL has four levels of transaction isolation:

1. Read-uncommitted: The ability to read uncommitted data. There will be dirty reading, non-repeatable reading and phantom reading.

2. Read-committed: Read submitted data. There will be unrepeatable readings and phantom readings.

3. Repeatable-read: mysql default transaction isolation level, query is the data at the beginning of the transaction. There's only phantom reading.

Serializable: Completely serialized reading, locking the table every time, reading and writing block each other. The highest isolation level, no dirty reading, no repeat reading, phantom reading. But it will greatly affect the performance of the system, generally not.

Is there anything you don't understand about MySQL's transaction isolation level? Or if you want to know more about it, you can continue to pay attention to our industry information section.

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.