Four isolation levels for MySQL transactions

The MySQL standard defines 4 isolation levels that define which changes within and outside a transaction are visible and which are not.

Low isolation levels generally support higher concurrency and lower system overhead.

Isolation levels from low to High: READ UNCOMMITTED < read Committed < repeatable read < Serializable.

First: READ UNCOMMITTED (read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted (commit) transactions.

This isolation level is rarely used in real-world applications because it has no better performance than other levels.

READ UNCOMMITTED data, also known as dirty Read (Dirty read).

[Window A]:

mysql> set GLOBAL tx_isolation= ' read-uncommitted ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+------------------+

| @ @tx_isolation |

+------------------+

| read-uncommitted |

+------------------+

1 row in Set (0.00 sec)

mysql> use test;

Database changed

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

+----+------+

2 rows in Set (0.00 sec)

[Window B]:

Mysql> SELECT @ @tx_isolation;

+------------------+

| @ @tx_isolation |

+------------------+

| read-uncommitted |

+------------------+

1 row in Set (0.00 sec)

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into Test.user values (3, ' C ');

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

+----+------+

3 Rows in Set (0.00 sec)

So far, window B has not been a commit;

[Window A]:

Mysql> select * from user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

+----+------+

3 Rows in Set (0.00 sec)

Second: Read COMMITTED (reads the submission content)

This is the default isolation level for most databases (but not MySQL default).

It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm.

This isolation level also supports the so-called non-re-read (Nonrepeatableread) because other instances of the same transaction are processed in that instance

There may be new commits in between, so the same select may return different results.

[Window A]:

mysql> SET GLOBAL tx_isolation= ' read-committed ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+----------------+

| @ @tx_isolation |

+----------------+

| read-committed |

+----------------+

1 row in Set (0.00 sec)

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

+----+------+

2 rows in Set (0.00 sec)

[Window B]:

mysql> SELECT @ @tx_isolation;

+----------------+

| @ @tx_isolation |

+----------------+

| read-committed |

+----------------+

1 row in Set (0.00 sec)

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

+----+------+

2 rows in Set (0.00 sec)

Mysql> Delete from Test.user where id=1;

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

+----+------+

1 row in Set (0.00 sec)

[Window A]:

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

+----+------+

2 rows in Set (0.00 sec)

[Window B]:

Mysql> commit;

Query OK, 0 rows affected (0.02 sec)

[Window A]:

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

+----+------+

1 row in Set (0.00 sec)

Third: Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data.

In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read).

To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found.

InnoDB and Falcon storage engines with multi-version concurrency control (mvcc,multiversion Concurrency control)

mechanism solves the problem.

[Window A]:

mysql> SET GLOBAL tx_isolation= ' repeatable-read ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+-----------------+

| @ @tx_isolation |

+-----------------+

| Repeatable-read |

+-----------------+

1 row in Set (0.00 sec)

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

[Window B]:

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+-----------------+

| @ @tx_isolation |

+-----------------+

| Repeatable-read |

+-----------------+

1 row in Set (0.00 sec)

mysql> INSERT INTO Test.user values (4, ' d ');

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

| 4 | D |

+----+------+

2 rows in Set (0.00 sec)

[Window A]:

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

+----+------+

1 rows in Set (0.00 sec)

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

| 4 | D |

+----+------+

2 rows in Set (0.00 sec)

IV: Serializable (serialized execution)

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another.

In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

[Window A]:

mysql> SET GLOBAL tx_isolation= ' SERIALIZABLE ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+----------------+

| @ @tx_isolation |

+----------------+

| SERIALIZABLE |

+----------------+

1 row in Set (0.00 sec)

Mysql> select * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

| 4 | D |

+----+------+

2 rows in Set (0.00 sec)

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into Test.user values (5, ' e ');

Query OK, 1 row Affected (0.00 sec)

[Window B]:

Mysql> quit;

Bye

[[email protected] ~]# mysql-uroot-pxxxx (re-login)

mysql> SELECT @ @tx_isolation;

+----------------+

| @ @tx_isolation |

+----------------+

| SERIALIZABLE |

+----------------+

1 row in Set (0.00 sec)

Mysql> select * from Test.user;

ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

[Window A]:

Mysql> commit;

Query OK, 0 rows affected (0.01 sec)

[Window B]:

Mysql> mysql> SELECT * from Test.user;

+----+------+

| ID | name |

+----+------+

| 2 | B |

| 4 | D |

| 5 | e |

+----+------+

3 Rows in Set (0.00 sec)

This article is from the "12024114" blog, please be sure to keep this source http://12034114.blog.51cto.com/12024114/1857750

Four isolation levels for MySQL transactions