Transaction isolation level is the core of database concurrency control, defining how transactions are isolated from each other and the extent to which one transaction can see changes made by other transactions. Based on the sql_store database, this article explains in detail the four standard transaction isolation levels supported by MySQL and their impacts through code examples, illustrates the role of isolation levels with scenarios, and analyzes deadlocks and their avoidance methods to help readers gain a deep understanding of transaction concurrency management.
Learning content
1. Overview of transaction isolation levels
MySQL supports four standard transaction isolation levels, from low to high:
The higher the isolation level, the lower the concurrency performance, but the higher the data consistency. In ATM transfer scenarios, an appropriate isolation level can ensure that account balance updates are not interfered with by other transactions.
2. Viewing and Setting Isolation Level
Use SHOW VARIABLES LIKE 'transaction_isolation' to view the current isolation level, which defaults to REPEATABLE-READ. The isolation level can be set using the following statement:
3. Concurrency Issues and Isolation Levels
Different isolation levels have varying capabilities in controlling dirty reads, non-repeatable reads, and phantom reads:
4. Deadlock
Deadlock occurs in concurrent transactions when two or more transactions wait for each other to release resources, preventing them from proceeding. MySQL detects deadlocks and terminates one transaction, reporting an error. To reduce deadlocks, maintain a consistent resource access order or shorten transaction lengths.
Example code and explanation
1. View isolation level
SHOW VARIABLES LIKE 'transaction_isolation';
Displays the current isolation level, with the default value of REPEATABLE-READ, which is the standard configuration for MySQL InnoDB.
2. Set the isolation level
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
sets the isolation level of the current session to SERIALIZABLE to ensure that subsequent transactions are executed serially and to avoid all concurrency issues.
3. Read Uncommitted (READ UNCOMMITTED)
Console 1 :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points FROM customers WHERE customer_id = 1;
Console 2：
START TRANSACTION;
UPDATE customers SET points = 20 WHERE customer_id 1;
-- Not committed
in console 1 set READ UNCOMMITTED after, query customer 1's points (originally 2303). Console 2 updates the points to 20 but not committed, console 1 queries again will see 20, reflecting dirty read. If console 2 rolls back, console 1's query result is invalid, may mislead business decisions.
4. Read committed (READ COMMITTED)
Console 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT points FROM customers WHERE customer_id = 1;
Console 2：
START TRANSACTION;
UPDATE customers SET points = 20 WHERE customer_id
= 1;
COMMIT;
After setting READ COMMITTED in Console 1, query the points. If Console 2 does not commit the changes, Console 1 still sees the original value 2303, avoiding dirty reads. After Console 2 commits, Console 1 queries to get 20. However, if multiple queries are made within the same transaction, the results may be inconsistent due to other transactions committing, leading to non-repeatable reads.
Console 1 (Non-repeatable read example) :
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
-- assume returns 20
SELECT points FROM customers WHERE customer_id = 1;
-- return 30
COMMIT;
Console 2：
START TRANSACTION;
UPDATE customers SET points = 30 WHERE customer_id
= 1;
COMMIT;
Console 1 first query gets 20, Console 2 commits and updates to 30, Console 1 second query gets 30, demonstrating non-repeatable read, data consistency may be affected.
Repeatable Read
Console 1：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
-- returns 30
SELECT points FROM customers WHERE customer_id = 1;
-- still returns 30
COMMIT;
Console 2 ：
START TRANSACTION;
UPDATE customers SET points = 40 WHERE customer_id
= 1;
COMMIT;
in REPEATABLE READ below, the console 1 creates a consistent view when the transaction starts, based on the snapshot at the time of transaction start (score is 30). Console 2 commits the change to 40, but the second query of console 1 still returns 30, avoiding non-repeatable read. MVCC ensures data consistency within the transaction.
Example of phantom read:
Console 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'CO';
-- return 1 record
COMMIT;
Console 2：
START TRANSACTION;
UPDATE customers SET state = 'CO' WHERE customer_id
= 1;
COMMIT;
Console 1 Query state = 'CO' 's customers, initially only 1 record. Console 2 Change the status of customer 1 to 'CO' and commit. After console 1 commits the transaction and queries again, it still retrieves 1 record, not reflecting the latest database status, resulting in phantom reads. InnoDB's gap locks can partially mitigate phantom reads.
6. Serialization (SERIALIZABLE)
Console 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'CO';
COMMIT;
Console 2:
START TRANSACTION;
UPDATE customers SET state = 'CO' WHERE customer_id = 2;
COMMIT;
Under SERIALIZABLE isolation level, Console 1 queries customers with state = 'CO' (initially 2). Console 2 attempts to change the status of customer 2 to 'CO' but does not commit, blocking Console 1's transaction until Console 2 commits, preventing phantom reads. After commit, Console 1 queries correctly reflect the latest data.
7. Deadlock
Console 1：
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id
= 1;
UPDATE orders SET status = 1 WHERE order_id
= 1;
COMMIT;
Console 2:
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id
= 1;
UPDATE customers SET state = 'VA' WHERE customer_id
= 1;
COMMIT;
Console 1 Locked customers , attempting to lock orders; Console 2 locks orders, attempting to lock customers, forming a circular dependency, leading to deadlock. MySQL terminates a transaction upon detecting deadlock and reports an error. To avoid deadlock, the order of resource access must be unified or the transaction length shortened.
summarizes
transaction isolation levels determine the visibility and consistency of data for concurrent transactions. READ UNCOMMITTED has high performance but carries significant risks, SERIALIZABLE is secure but has low performance, REPEATABLE READ is the default compromise solution for MySQL InnoDB. The ATM transfer scenario illustrates the importance of isolation levels for data consistency. This article is based on sql_store database, explaining four isolation levels and deadlock issues through code examples. Subsequent content will explore index design or query optimization, stay tuned.
This content is automatically aggregated by InertiaRSS (RSS Reader) for reading reference only. Original from — Copyright belongs to the original author.