Four levels of data isolation and four levels of isolation

Four levels of data isolation and four levels of isolation

There are four levels of thing isolation in the database. According to isolation, the order from low to high is: 1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable

The order of concurrency is the opposite.

√: Possible occurrence ×: No

 

	Dirty read	Non-repeated read	Phantom read
Read uncommitted (uncommitted Read)	√	√	√
Read committed (Read committed)	×	√	√
Repeatable read (Repeatable read)	×	×	√
Serializable (Serializable)	×	×	×

 

1. Read uncommitted

When the read is not submitted, the SQL query reads the uncommitted data. In mysql, the default transaction level is Repeatable read. By default, the automatic submission mode is enabled. The following changes are made,

(1) disable automatic submission

mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in set (0.00 sec)mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | OFF   |+---------------+-------+1 row in set (0.01 sec)

(2) modify the transaction level to Read uncommitted.

mysql> show variables like 'tx_isolation';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)

Mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

Mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

Currently, two sessions are opened. In the first session, a row of data is inserted to table test, but the data has not been submitted. In this case, the data can be queried through the second session.

After the transaction rollback in the first session, the second session cannot query the newly inserted data '3'

2. Read Committed

Read commit. Modify the transaction to Read Committed several times.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.00 sec)

At this time, a piece of data is inserted in session0 and no commit is made. It cannot be found in session1.

After session0 is commit, session1 can query data normally.

 

3. Repeatable read

Repeatable read. At this time, the transaction level is modified to be Repeatable read.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)

In this case, two pieces of data exist in the test table: 2 and 4, as shown below:

mysql> select * from test where col1 <'5';+------+| col1 |+------+| 2    || 4    |+------+2 rows in set (0.00 sec)

The steps are as follows: 1. Start a transaction in session0;

2. Insert a piece of data (5) in session1 and commit;

3. In session0, no data inserted in session1 is found to be queried.

Next, perform the following operations: 1. commit in session0;

2. Perform the query operation in session0, and find that 5 of insert in session1 can be queried again.

 

4. Serializable

Serializable words are the highest isolation level, that is, each row of data read will be locked and the transaction is executed in sequence. So there will be lock timeout and other issues, which are rarely used in actual business.