The isolation level of a database defines the visibility of a transaction that may be to other concurrent transactions and how they may affect the database. The choice of isolation level affects concurrency performance and data consistency. Different isolation levels can prevent different degrees of concurrency problems, such as Dirty Reads, Nonrepeatable Reads, and Phantom Reads.
The SQL standard defines four isolation levels:
1. Read Uncommitted
This is the lowest isolation level, and transactions can read changes to other uncommitted transactions. This means that dirty reads may occur, i.e. one transaction may read data that is not committed by another transaction.
example:
Transaction A modified a row of data but has not yet committed. At this time, Transaction B can see this modification at this isolation level.
-- TransactionsA BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- TransactionsB BEGIN; SELECT balance FROM accounts WHERE id = 1; -- 可以看到TransactionsAUncommitted changes
2. Read Committed
At this isolation level, a transaction can only read changes committed by other transactions. This avoids dirty reads, but still can not be repeated, i.e., two identical queries in the same transaction may return different results.
example:
-- TransactionsA BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT; -- TransactionsB BEGIN; SELECT balance FROM accounts WHERE id = 1; -- First query -- TransactionsASubmit changes SELECT balance FROM accounts WHERE id = 1; -- Second query，You may see different results COMMIT;
3. Repeatable Read
Under this isolation level, it is ensured that the same data will be read multiple times in the same transaction and the same result will be seen, that is, the phenomenon of non-repeatable reading is prevented. However, a phantom read may occur, i.e., a newly inserted row of another transaction is read in the same transaction.
example:
-- TransactionsA BEGIN; SELECT * FROM accounts WHERE balance > 1000; -- First query -- TransactionsB BEGIN; INSERT INTO accounts (id, balance) VALUES (3, 2000); COMMIT; -- TransactionsA SELECT * FROM accounts WHERE balance > 1000; -- 第二次查询可能包含了TransactionsBNewly inserted row COMMIT;
4. Serializable
This is the highest isolation level, which completely isolates transactions so that they cannot be executed in parallel. Any concurrency will be blocked, thus avoiding dirty reading, non-repeatable reading and phantom reading. But this also greatly reduces concurrency performance.
example:
-- TransactionsA BEGIN; SELECT * FROM accounts WHERE balance > 1000; -- Lock the result set -- TransactionsB BEGIN; -- Try inserting a new line or updating it to satisfy the aboveSELECTThe rows of the condition will be blocked，直到TransactionsASubmit or rollback INSERT INTO accounts (id, balance) VALUES (3, 2000); -- Blocked
When choosing an isolation level, it is often necessary to make a trade-off between concurrency performance and data consistency. If a degree of inconsistency is acceptable for higher concurrency performance, a lower isolation level can be selected (such as read not committed or read commit). If data consistency is critical, you may need to choose a higher isolation level (such as repeatable read or serialized).
This is the article about checking online data and paying attention to the isolation level of the database. For more related content on mysql isolation level, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!