mysql的事务隔离级别举例

　　事务是怎么回事，之前已经聊过（参见Oracle事务的隔离 ），无关哪个数据库，只要提到事务，都是这四种隔离级别。mysql的默认隔离级别是3——可重复读。更重要的是，是否支持事务，跟mysql的存储引擎相关。登陆mysql，查询存储引擎（以下SQL均基于mysql5.7.26版本操作）：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

 

　　我们看到，这么多存储引擎，也就一个InnoDB支持事务。再瞧一下默认的事务隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

 

　　我们看到，不管是当前会话也好，整个系统也好，默认的隔离级别都是可重复读。

　　再看下默认的事务自动提交开关：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

　　我们把上面的开关给关了，好测试：

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

 

　　我们建个表测试一下：

mysql> create table test_wlf
    -> (
    -> id INT(11),
    -> name VARCHAR(25)
    -> );
Query OK, 0 rows affected (0.06 sec)

　　插入一条数据：

mysql> insert into test_wlf values(1,"wulf");
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_wlf;
+------+------+
| id   | name |
+------+------+
|    1 | wulf |
+------+------+
1 row in set (0.00 sec)

　　再来一个窗口，同样把autocommit关掉，两事务同时搞起：

 

 　　因为当前的事务隔离级别是可重复读，所以右边的事务始终可以重复读取到wulf这条数据，即使左边的事务新增了wumanshu这条数据，按理应该出现幻读，可惜已经被InnoDB用mvcc（多版本并发控制）解决了。换句话说，幻读在InnoDB已经死绝了。

　　接下来我们把两个窗口都改成已提交读：

mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> set global tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

　　再试试修改数据：

 

 　　以上可以看到已提交读出现了不可重复读。继续，接下来我们把两个窗口都改成未提交读：

mysql> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set global tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

　　再次更新id为2的name值，事务的中间结果都被查出来了，脏读出现：