MySQL的事务异常处理及隔离级别（包括实例测试）

• 事务异常，包括脏读（Dirty Read）、不可重复读（Non-repeactable Read）和幻读（Phantom Read）。

  • 脏读
    维基百科的解释：

    A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

    一个事务(transaction)还在执行但没有提交(commit)的时候,你读到了更新的结果，这种现象就是脏读。
    

  • 不可重复读
    维基百科的解释：

    A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.
    在事务处理过程中，一行被读取了两次，但两次的结果不一样，这种现象就是不可重复读。
    

  • 幻读

    A phantom read occurs when, in the course of a transaction, new rows are added or removed by another transaction to the records being read.
    在事务处理过程中，在读取数据时，另一个事务正在新增行或删除行，读取结果发生了改变，这种现象就是幻读。
    

• 事务的四大特（ACID）
  包括原子性(atomicity)，一致性(consistency)，隔离性(isolation)，
  持久性(durability)。

• 隔离级别

  • 读未提交（Read uncommited）

  This is the lowest isolation level. In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.
  这个是隔离级别最低的一层。脏读是被允许的，所以可以看到事务还没提交的内容。

  • 读已提交

  In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level). As in the previous level, range-locks are not managed.
  这个级别的隔离，DBMS（具有基于锁的并发控制）会保存写入锁一直到事务结束，但是读锁在这个时候就会释放掉，所以有可能发生可重复读的情况。跟上面的读未提交级别一样，范围锁没有被管理。

  • 可重复读

  In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.
  这个级别的隔离，DBMS（具有基于锁的并发控制）会保存写入锁和读取锁一直到事务结束。但是由于范围锁的缘故，依然有可能发生幻读。

  • 可串行化

  This is the highest isolation level.With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom readsphenomenon.
  DBMS（具有基于锁的并发控制）会保存写入锁和读取锁一直到事务结束。同时当查询时必须询问范围锁，所以可以解决事务的三种情况。但是也会牺牲并发性。

• 事务异常与隔离级别的关系

  	脏读	不可重复读	幻读
  读未提交	允许	允许	允许
  读已提交	禁止	允许	允许
  可重复读	禁止	禁止	允许
  可串行化	禁止	禁止	禁止

• 测试实例
  MySQL默认的隔离级别是REPEATABLE-READ

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.01 sec)

设置隔离级别为READ-UNCOMMITTED

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

mysql> show variables like '%transaction_isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set, 1 warning (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, 1 warning (0.00 sec)

并且新建两个客户端，并执行上述语句

• 脏读

客户端A：
mysql> begin;
Query OK, 0 rows affected (0.02 sec)

mysql> update test set name='关云长' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

客户端B:
mysql>  select * from test where id=1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 关羽   |
+----+--------+
1 row in set (0.00 sec)

mysql>  select * from test where id=1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 关云长    |
+----+-----------+
1 row in set (0.00 sec)

• 不可重复读

客户端A：
mysql> begin;
Query OK, 0 rows affected (0.02 sec)

mysql> update test set name='关云长' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
客户端B:
mysql>  select * from test where id=1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 关羽   |
+----+--------+
1 row in set (0.00 sec)

mysql>  select * from test where id=1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 关云长    |
+----+-----------+
1 row in set (0.00 sec)

• 幻读

     客户端A：
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test values (4,'吕布');
    Query OK, 1 row affected (0.00 sec)
    
     客户端B：
    mysql> select * from test;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | 关羽   |
    |  2 | 刘备   |
    |  3 | 张飞   |
    |  4 | 吕布   |
    +----+--------+
    4 rows in set (0.00 sec)