Briefly describe the four transaction isolation levels of MySql

Isolation Level:

Isolation is more complicated than you might think. The SQL standard defines four isolation levels, which determine which modifications made in each transaction are visible within and between transactions, and which are invisible. Lower levels of isolation generally allow for higher concurrency and lower system overhead.

The following is a brief introduction to the four isolation levels.

1.READ UNCOMMITTED (uncommitted read)

At the READ UNCOMMITTED level, changes made in a transaction, even if not committed, are visible to other transactions. Transactions can read uncommitted data, which is also called dirty read. This level can cause many problems. In terms of performance, READ UNCOMMITTED is not much better than other levels, but it lacks many of the benefits of other levels. Unless there is a very necessary reason, it is rarely used in actual applications.

(1) All transactions can see the execution results of other uncommitted transactions

(2) This isolation level is rarely used in actual applications because its performance is not much better than other levels.

(3) The problem caused by this level is dirty read: uncommitted data is read

#First, change the isolation level set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+#Transaction A: Start a transaction start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction B: also start a transaction (then the two transactions intersect)
Execute the update statement in transaction B without committing start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction A: Can transaction A see the updated data at this time?
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 | ---> You can see it! This means we have read data that transaction B has not yet committed. | 2 | 2 |
| 3 | 3 |
+------+------+#Transaction B: Transaction B is rolled back, but rollback is still not committed;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction A: The data seen in transaction A is also the data that B has not submitted. select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 | ---> Dirty read means that I am in this transaction (A), although transaction B has not been committed, I can see any data changes in it!
| 2 | 2 |
| 3 | 3 |
+------+------+

2.READ COMMITTED (read committed)

The default isolation level of most database systems is READ COMMITTED, but MySQL is not. READ COMMITTED meets the simple definition of isolation mentioned above: when a transaction starts, it can only "see" the changes made by transactions that have been committed. In other words, any changes made by a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called nonrepeatable read, because executing the same query twice may produce different results.

(1) This is the default isolation level for most database systems (but not for MySQL).

(2) It satisfies the simple definition of isolation: a transaction can only see changes made by transactions that have already been committed.

(3) The problem with this isolation level is non-repeatable read: Non-repeatable read means that we may see different results when executing exactly the same select statement in the same transaction.

This may be caused by:

(1) There is a cross transaction with a new commit, which causes the data to change;

(2) When a database is operated by multiple instances, other instances of the same transaction may have new commits during the processing of this instance.

#First change the isolation level set tx_isolation='read-committed';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+#Transaction A: Start a transaction start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction B: also start a transaction (then the two transactions intersect)
Data is updated in this transaction, and the start transaction is not committed;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction A: Can we see the data changes in transaction A at this time?
select * from tx; --------------->
+------+------+ |
| id | num | |
+------+------+ |
| 1 | 1 |--->Can't see it! |
| 2 | 2 | |
| 3 | 3 | |
+------+------+ |——>Same select statement, but different results|
#Transaction B: What if transaction B is committed? |
commit; |
|
#Transaction A: |
select * from tx; --------------->
+------+------+
| id | num |
+------+------+
| 1 | 10 |--->Because transaction B has been committed, we can see the data changes in A | 2 | 2 |
| 3 | 3 |
+------+------+

3. REPEATABLE READ

REPEATABLE READ solves the dirty read problem. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. However, in theory, the Repeatable Read isolation level still cannot solve another problem: Phantom Read. The so-called phantom read refers to the situation where when a transaction is reading records in a certain range, another transaction inserts new records in the range. When the previous transaction reads the records in the range again, phantom rows will be generated. InnoDB and XtraDB storage engines solve the problem of phantom reads through multiversion concurrency control (MVCC, Multiversion Concurrency Control).

(1) This is the default transaction isolation level of MySQL

(2) It ensures that multiple instances of the same transaction see the same rows when reading data concurrently.

(3) Problems that may occur at this level - Phantom Read: When a user reads a range of data rows, another transaction inserts new rows into the range. When the user reads the data rows in the range again, new phantom rows are found.

(4) InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control (MVCC) mechanism

#First, change the isolation level set tx_isolation='repeatable-read';
select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+#Transaction A: Start a transaction start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction B: Start a new transaction (then the two transactions intersect)
Update data in transaction B and commit start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
commit;#Transaction A: Even if transaction B has been committed, can A see the data changes?
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 | ---> Still can't see it! (This is different from level 2, which also shows that level 3 solves the non-repeatable read problem)
| 2 | 2 |
| 3 | 3 |
+------+------+#Transaction A: Only when transaction A is also committed can it see the data changes commit;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+

4.SERIALIZABLE

SERIALIZABLE is the highest isolation level. It avoids the phantom read problem mentioned above by forcing transactions to be executed serially. In short, SERIALIZABLE will lock each row of data read, so it may cause a lot of timeouts and lock contention problems. This isolation level is rarely used in actual applications. It is only considered when it is extremely necessary to ensure data consistency and no concurrency is acceptable.

(1) This is the highest isolation level

(2) It solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it adds a shared lock on each row of data read.

(3) At this level, a large number of timeouts and lock contention may occur

#First modify the isolation level set tx_isolation='serializable';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+#Transaction A: Start a new transaction start transaction;#Transaction B: Before A is committed, this cross transaction cannot change the data start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The above is a brief description of the details of the four isolation levels of MySql. For more information about MySQL isolation levels, please pay attention to other related articles on 123WORDPRESS.COM!