Four isolation levels of Mysql transactions-php手册-php.cn

Four isolation levels of Mysql transactions

WBOY

Release： 2016-08-04 08:56:16

Original

1061 people have browsed it

We will use transactions in development work, so do you know what types of transactions are there?
The MYSQL standard defines four types of isolation levels, which are used to limit which changes inside and outside the transaction are visible and which are invisible.
Lower isolation levels generally support higher concurrency and have lower system overhead.
Isolation levels from low to high: Read Uncommitted < Read Committed < Repeatable Read < Serializable.
Read Uncommitted (read uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions.
This isolation level is rarely used in practical applications because its performance is not much better than other levels.
Reading uncommitted data is also called dirty read. [Window A]: mysql> set GLOBAL tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) 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 committed; [Window A]: mysql> select * from user ; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)Read Committed (read submitted content) This is the default isolation level for most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable read, because other instances of the same transaction may have new commits during the processing of this instance, so the same select may return different results. <code class="prettyprint linenums lang-php">[Window A]: mysql> SET GLOBAL tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) 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) [窗口A]: mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) [窗口B]: mysql> commit; Query OK, 0 rows affected (0.02 sec) [窗口A]: mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | +----+------+ 1 row in set (0.00 sec)Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。[窗口A]: mysql> SET GLOBAL tx_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录) 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) [窗口B]: mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录) 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) [窗口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)Serializable（序列化执行）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result. [Window A]: mysql> SET GLOBAL tx_isolation='SERIALIZABLE'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) 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 [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) 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)Thank ~

Source: http://mp.weixin.qq.com/s?__biz=MjM5NDM4MDIwNw==&mid=2448834642&idx=1&sn=c02c5cc8ab0c1f29142ac8f8aa6b78af#rd

For more [dry information sharing], please follow my personal subscription account.
Four isolation levels of Mysql transactions