MySQL database transaction isolation level details, mysql details

MySQL database transaction isolation level details, mysql details

Database transaction isolation level

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

1. Read uncommitted: Dirty Read is allowed.
2. Read committed: it is the most common isolation level to prevent dirty reads and is the default isolation level for most databases.
3. Repeatable read: prevents dirty reads and non-repeated reads.
4. Serializable: it can prevent dirty reads, non-repeated reads, and Phantom reads, which will reduce the database efficiency.
   

These four levels can solve dirty reads, non-repeated reads, and Phantom reads one by one.

√: Possible occurrence ×: No

Transaction Level	Dirty read	Non-repeated read	Phantom read
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

Note: we will discuss isolation-level scenarios, mainly in the case of multiple transaction concurrency.

Dirty read, phantom read, and non-repeated read

Dirty read:

Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

Repeatable read:

A transaction reads the same data multiple times. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read. (That is, you cannot read the same data content)

Phantom read:

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. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion.

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);

First transaction:

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

Reproduction steps:

Only the insert Statement of transaction A can be performed before and after the select statement of transaction B.

MySQL InnoDB Storage engine implements Multi-Version Concurrency Control protocol-MVCC (Multi-Version Concurrency Control) plus the next-key locking policy in Repeatable Read (RR) phantom read does not exist at the isolation level. If you want to test phantom reading, experiment in MyISAM.

If a clustered index (primary key index) has a unique constraint, InnoDB will downgrade the default next-key lock to record lock.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!