MySQL transaction isolation level and concurrency issues

MySQL transaction isolation level and concurrency issues

MySQL version: 8.0.27

---

One, the problems faced by concurrent execution of transactions

CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `age` tinyint(3) UNSIGNED NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

INSERT INTO `user` VALUES (10, '小明', 16);
INSERT INTO `user` VALUES (20, '小红', 15);
INSERT INTO `user` VALUES (30, '小丽', 18);
INSERT INTO `user` VALUES (40, '小梅', 21);
INSERT INTO `user` VALUES (50, '小亮', 20);

SELECT * FROM user;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 10 | 小明   |  16 |
| 20 | 小红   |  15 |
| 30 | 小丽   |  18 |
| 40 | 小梅   |  21 |
| 50 | 小亮   |  20 |
+----+--------+-----+

Set the transaction isolation level to read uncommitted for easy demonstration

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

1. Dirty Read

If transaction A reads the data modified by uncommitted transaction B, it means that a dirty read has occurred.

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id=10;	-
-	UPDATE user SET age=11 WHERE id=10;
SELECT * FROM user WHERE id=10; read the uncommitted modification of transaction B (age=11), dirty read	-
-	rollback;
SELECT * FROM user WHERE id=10; The data read has changed back (age=10)	-
commit;	-

2. Non-Repeatable Read

If transaction A reads the data modified by the committed transaction B (transaction B modifies the data read by the uncommitted transaction A), it means that a non-repeatable read has occurred.

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id=10;	-
-	UPDATE user SET age=12 WHERE id=10;
-	commit;
SELECT * FROM user WHERE id=10; read the committed modification of transaction B (age=12), and there is a non-repeatable read	-
commit;

3. Phantom Read

Transaction A first queried some records based on a certain range condition, while transaction B wrote some new records that met the conditions. When transaction A was queried again with the same conditions, a new record was queried, which means that something happened. Phenomenon of phantom reading.

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id>30;	-
-	INSERT INTO user VALUES(60,'小静', 10);
-	commit;
SELECT * FROM user WHERE id>30; reads the record inserted by transaction B, and there is a phantom read	-
commit;

---

Two, the four isolation levels in the SQL standard

1. READ UNCOMMITTED (read uncommitted)

At the READ UNCOMMITTED level, the modification in the transaction, even if it is not committed, is visible to other transactions.
That is to say, the isolation level will have dirty read , non-repeatable read and phantom read problems.

2. READ COMMITTED (read submitted)

READ COMMITTED solves the dirty read problem, which satisfies the simple definition of transaction isolation: a transaction from the beginning until the commit, any changes made are invisible to other transactions.

3. REPEATABLE READ (repeatable read)

REPEATABLE READ solves the problem of dirty read and non-repeatable read . This level ensures that the results of reading the same record multiple times in the same transaction are consistent. But theoretically, the repeatable read isolation level still cannot solve the phantom read ( Phantom Read ) problem.
InnoDB storage engine uses MVCC (multi-version concurrency control) and Next-Key (proximity key lock) to largely avoid the problem of phantom reading.
Repeatable read is MySQL's default transaction isolation level.

4. SERIALIZABLE (serialization)

SERIALIZABLE by forcing a transaction serial execution, avoiding dirty reads , non-repeatable read and phantom read problem. SERIALIZABLE will lock each row of data read, so it may cause a lot of timeout and lock contention problems.

---

3. Comparison of four isolation levels

Isolation level	Dirty read possibility	Possibility of non-repeatable reading	Possibility of phantom reading	Lock read
READ UNCOMMITTED	√	√	√	×
READ COMMITTED	×	√	√	×
REPEATABLE READ	×	×	√	×
SERIALIZABLE	×	×	×	√

---

Four, four isolation level transaction parallel examples

View the isolation level of the transaction

SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

Set the isolation level of the transaction

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL <level>;

There are 4 optional values for level:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Modifying the global isolation level requires exiting the session and reconnecting to MySQL to take effect.

Initial data

CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `age` tinyint(3) UNSIGNED NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

INSERT INTO `user` VALUES (10, '小明', 16);
INSERT INTO `user` VALUES (20, '小红', 15);
INSERT INTO `user` VALUES (30, '小丽', 18);
INSERT INTO `user` VALUES (40, '小梅', 21);
INSERT INTO `user` VALUES (50, '小亮', 20);

1. READ UNCOMMITTED (read uncommitted)

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+-----------------+

• Dirty read

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id=10;	-
-	UPDATE user SET age=11 WHERE id=10;
SELECT * FROM user WHERE id=10; read the uncommitted modification of transaction B (age=11), dirty read	-
-	rollback;
SELECT * FROM user WHERE id=10; The data read has changed back (age=16)	-
commit;	-

2. READ COMMITTED (read submitted)

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+-----------------+

• Non-repeatable

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id=10;	-
-	UPDATE user SET age=12 WHERE id=10;
SELECT * FROM user WHERE id=10; uncommitted modification of transaction B cannot be read (age=16), and there is no dirty read	-
-	commit;
SELECT * FROM user WHERE id=10; reads the committed modification of transaction B (age=12), and there is a non-repeatable read	-
commit;

3. REPEATABLE READ (repeatable read)

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

• Repeatable

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id=10;	-
-	UPDATE user SET age=13 WHERE id=10;
SELECT * FROM user WHERE id=10; uncommitted modification of transaction B cannot be read (age=12), and there is no dirty read	-
-	commit;
SELECT * FROM user WHERE id=10; committed modification of transaction B (age=12) cannot be read, and there is no non-repeatable read	-
commit;

• Phantom reading

Because MySQL's InnoDB storage engine uses MVCC (multi-version concurrency control) and Next-Key (proximal key lock) to a large extent avoid the phantom read problem, so it is impossible to demonstrate most of the phantom read engine InnoDB It cannot completely prohibit phantom reading.

Transaction A	Transaction B
begin;	begin;
SELECT * FROM user WHERE id>30;	-
-	INSERT INTO user VALUES(60,'小静', 10);
-	commit;
SELECT * FROM user WHERE id>30; The record inserted by transaction B cannot be read, and there is no phantom read	-
UPDATE user SET age=11 WHERE id=60;	-
SELECT * FROM user WHERE id>30; read the record inserted by transaction B, and there is a phantom read	-
commit;

4. SERIALIZABLE (serialization)

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+-----------------+

SERIALIZABLE dirty read , non-repeatable read and phantom read problem by forcing the transaction to execute serially.

• When transaction A reads a record, this record will be read lock (shared lock), other transactions can query this record, but it cannot be modified

Transaction A	Transaction B
begin;
SELECT * FROM user WHERE id=10;	-
-	SELECT * FROM user WHERE id=10; successfully executed
-	UPDATE user SET age=14 WHERE id=10; blocked

• When transaction A modifies a record, this record will be given a write lock (exclusive lock), and other transactions cannot query and modify this record

Transaction A	Transaction B
begin;	-
UPDATE user SET age=14 WHERE id=10;	-
-	SELECT * FROM user WHERE id=10; blocking
commit;	-
-	Execution complete

• When transaction A reads a range record, the range will be read lock (shared lock), other transactions cannot add or modify records in this range, nor can they modify records outside the range into records that meet the range conditions.

Transaction A	Transaction B
begin;	-
SELECT * FROM user WHERE id>30;	-
-	INSERT INTO user VALUES(70,' ', 10); 16189d45e0a988 blocked
commit;	-
-	Execution complete

Transaction A	Transaction B
begin;
SELECT * FROM user WHERE id>30;	-
-	UPDATE user SET id=31 WHERE id=10; blocked
commit;	-
-	Execution complete