事务是怎么回事，之前已经聊过（参见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值，事务的中间结果都被查出来了，脏读出现：
