Detailed introduction to MySQL database transaction isolation levels

This article mainly introduces MySQL databaseRelated information on detailed explanation of transaction isolation level. Friends in need can refer to the following

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: Prevent dirty reads, the most commonly used isolation level, and the default isolation level for most databases.

3. Repeatable read: Can prevent dirty reads and non-repeatable reads.

4. Serializable: It can prevent dirty reads, non-repeatable reads and phantom reads, which (transaction serialization) will reduce the efficiency of the database.

These four levels can solve problems such as dirty reads, non-repeatable reads, and phantom reads one by one.

√: Possible×: Will not occur

Transaction level	Dirty read	Non-repeatable read	phantom reading
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

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

Dirty read, phantom read, non-repeatable read

Dirty read:

Dirty read means when a transaction is The data is accessed and modified, but the modification has not yet been submitted to the database. At this time, another transaction also accesses the data and then uses the data.

Non-repeatable read:

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

Phantom reading:

refers to a phenomenon that occurs when a transaction is not executed independently, such as the first transaction The data in a table is modified, and this modification involves all data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a row of new data into the table. Then, in the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if a hallucination has 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;

Simulated data:

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

First transaction A:

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

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 of transaction B and after update.

MySQL InnoDB storage engine implements a multi-version concurrency control protocol - MVCC (Multi-Version Concurrency Control) plus gap lock (next -key locking) strategy does not have phantom reads under the Repeatable Read (RR) isolation level. If testing phantom reading, experiment under MyISAM.

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

The above is the detailed content of Detailed introduction to MySQL database transaction isolation levels. For more information, please follow other related articles on the PHP Chinese website!