MySQL Database Transaction Isolation Levels Explanation

Database transaction isolation level

There are four isolation levels for database transactions, which are from low to high

Read uncommitted: Dirty reading allowed. Read committed: Prevention of dirty reading, most commonly used isolation level, and the default isolation level for most databases. Repeatable read: Dirty reading and non-repeatable reading can be prevented. Serializable: Dirty, non-repeatable, and phantom reads are prevented, and (transaction serialization) reduces database efficiency.

These four levels can solve the problems of dirty reading, unrepeatable reading and imaginary reading one by one.

Note: May appear ×: will not appear

事务级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrent.

Dirty reading, illusion reading, and non-repetitive reading

Dirty reading:

A dirty read is when one transaction is accessing data and making modifications to the data that have not yet been committed to the database, then another transaction accesses the data and then uses the data.

Non-repeatable reading:

Refers to reading the same 1 data multiple times within 1 transaction. Before this transaction ends, another transaction also accesses the same data. Then, between the two readings of data in the first transaction, due to the modification of the second transaction, the data read by the first transaction may not be the same. What happens is that the data read twice in a transaction is not identical, so it is called non-repeatable reading. (That is, the same data content cannot be read)

Fantasy reading:

It refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table by inserting 1 row of new data into the table. Then, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if hallucination 1 had occurred.

example:

Table:

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;

Analog data:

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

Transaction 1 A:

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

Transaction 2 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

Reproduction steps:

As long as the insert statement of the A transaction comes before the select transaction and after the update.

MySQL InnoDB storage engine is based on multi-version concurrency control protocol-MVCC (Multi-Version Concurrency Control) plus gap lock (next-key locking) strategy, and there is no magic read under Repeatable Read (RR) isolation level. If you test phantom reading, experiment under MyISAM.

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

Thank you for reading, hope to help everyone, thank you for your support to this site!