Detailed explanation of MySQL database transaction isolation levels

Database transaction isolation level

There are 4 isolation levels for database transactions, from low to high:

1. Read uncommitted: Dirty reads are allowed.
2. Read committed: Prevents dirty reads, the most commonly used isolation level, and is the default isolation level for most databases.
3. Repeatable read: It can prevent dirty read and non-repeatable read.
4. Serializable: It can prevent dirty reads, non-repeatable reads and phantom reads, and (transaction serialization) will reduce the efficiency of the database.
   

These four levels can solve the problems of dirty read, non-repeatable read, and phantom read one by one.

√: May appear ×: Will not appear

Note: We discuss the isolation level scenario mainly when multiple transactions are concurrent.

Dirty reads, phantom reads, and non-repeatable reads

Dirty read:

Dirty read means that when a transaction is accessing data and modifying the data, and this modification has not been submitted to the database, another transaction also accesses the data and uses the data.

Non-repeatable read:

It means reading the same data multiple times within a transaction. Before this transaction ends, another transaction also accesses the same data. Therefore, between the two reads in the first transaction, the data read twice by the first transaction may be different due to the modification of the second transaction. This means that the data read twice in a transaction is different, so it is called non-repeatable read. (That is, the same data content cannot be read)

Phantom reading:

It refers to a phenomenon that occurs when transactions are not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all data rows in the table. At the same time, the second transaction also modifies the data in this table by inserting a new row of data into the table. Then, it may happen that the user operating the first transaction finds that there are still unmodified data rows in the table, as if he were hallucinating.

example:

surface:

CREATE TABLE `cc_wsyw126_user_test_isolation_copy` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `password` varchar(64) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ix_age` (`age`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

Simulated data:

INSERT INTO `cc_wsyw126_user_test_isolation_copy` (`password`, `age`) 
VALUES 
('1', 1),
('twenty two),
('3', 3),
('4', 4);

The first transaction A:

start transaction 
insert into cc_wsyw126_user_test_isolation_copy (password, age) values ​​('5',5)
commit

The second transaction B:

start transaction 
update cc_wsyw126_user_test_isolation_copy set age = 2 where password >='2'
select * from cc_wsyw126_user_test_isolation_copy where password >= '2';
commit

Steps to reproduce:

As long as the insert statement of transaction A is before the select statement and after the update statement of transaction B, it will be fine.

The MySQL InnoDB storage engine implements a multi-version concurrency control protocol - MVCC (Multi-Version Concurrency Control) plus a next-key locking strategy to prevent phantom reads at the Repeatable Read (RR) isolation level. If you want to test phantom reads, experiment with MyISAM.

In a clustered index (primary key index), if there is a unique constraint, InnoDB downgrades the default next-key lock to a record lock.

Thank you for reading, I hope it can help you, thank you for your support of this site!