MySQL的事务处理以及隔离级别

2019独角兽企业重金招聘Python工程师标准>>>

最近接触了数据并发这块，就来说下关于数据的事务处理以及隔离级别的相关知识。

1. 事务必须满足4个条件：

原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
一致性（Consistency）：事务操作之后,数据库所处的状态和业务规则是一致的;比如a,b账户相互转账之后，总金额不变！
隔离性（Isolation）：如果多个事务并发执行，应像各个事务独立执行一样！
持久性（Durability）：事务提交后被持久化到数据库.

2. MySQL处理事务有两种方法

1) 用BEGIN,ROLLBACK,COMMIT来实现
开始：START TRANSACTION或BEGIN语句可以开始一项新的事务
提交：COMMIT可以提交当前事务，是变更成为永久变更
回滚：ROLLBACK可以回滚当前事务，取消其变更

2) 直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！
我们可以通过set autocommit=0 禁止自动提交 
set autocommit=1 开启自动提交来实现事务的处理。
但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，并且只用于当前连接。
※ MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持！

3. 事务中一些有问题的读取：脏读，不可重复读，幻读

※脏读：一个事务读取了另一个未提交的并行事务写的数据。
(事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了。) 
exp:
小明的分数为89,事务A中把他的分数改为98,但事务A尚未提交。
与此同时，事务B正在读取小明的分数，读取到小明的分数为98。
随后，事务A发生异常，而回滚了事务。小明的分数又回滚为89。
最后，事务B读取到的小明的分数为98的数据即为脏数据，事务B做了一次脏读。
 (大部分数据库缺省的事物隔离级别都不会出现这种状况)

※不可重复读：一个事务重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务修改过。
 (事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化。)
exp：
在事务A中，读取到小明的分数为89，操作没有完成，事务还没提交。
与此同时，事务B把小明的分数改为98，并提交了事务。
随后，在事务A中，再次读取小明的分数，此时工资变为98。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

※幻读：一个事务重新执行一个查询，返回一套符合查询条件的行，发现这些行因为其他最近提交的事务而发生了改变。
 (事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE 子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突 然出现的一样。)
exp：
目前分数为90分以上的的学生有15人，事务A读取所有分数为90分以上的的学生人数有15人。
此时，事务B插入一条分数为99的学生记录。
这是，事务A再次读取90分以上的的学生，记录为16人。此时产生了幻读。
(大部分数据库缺省的事物隔离级别都会出现这种状况，此种事物隔离级别将带来表级锁)

4. 事务隔离级别的描述

从级别低到高依次为：
READ UNCOMMITTED： 幻像读、不可重复读和脏读都允许。
READ COMMITTED： 允许幻像读、不可重复读，但不允许脏读。
REPEATABLE READ： 允许幻像读，但不允许不可重复读和脏读。InnoDB默认级别
SERIALIZABLE： 幻像读、不可重复读和脏读都不允许。

ORACLE默认的是 READ COMMITTED。
MYSQL默认的是 REPEATABLE READ。
但是InnoDB的可重复读隔离级别和其他数据库的可重复读是有区别的，不会造成幻象读（phantom read）。
ORACLE数据库支持 READ COMMITTED 和 SERIALIZABLE ，不支持 READ UNCOMMITTED 和 REPEATABLE READ 。

如果数据库的隔离级别为REAE_UNCOMMITTED， 则其他线程可以看到未提交的数据， 因此就出现脏读；
如果数据库隔离级别设为READ_COMMITTED，即没提交的数据别人是看不见的，就避免了脏读；但是，正在读取的数据只获得了读取锁，读完之后就解锁，不管当前事务有没有结束，这样就容许其他事务修改本事务正在读取的数据。导致不可重复读。
如果数据库隔离级别设置为REPEATABLE READ，因为对正在操作的数据加锁，并且只有等到事务结束才放开锁， 则可以避免不可重复读；
REPEATABLE READ只能保证正在被本事务操作的数据不被其他事务修改，却无法保证有其他事务提交新的数据。 则有可能线程1在操作表T1的时候（特别是统计性的事务），其他线程仍然可以提交新数据到表T1，这样会导致线程1两次统计的结果不一致，就像发生幻觉一样。
SERIALIZABLE因为获得范围锁，且事务是一个接着一个串行执行，则保证了不会发生幻读。
由此可见，隔离级别越高，受其他事物干扰越少，并发性能越差。

5. 修改事务隔离级别

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询；
查看InnoDB系统级别的事务隔离级别：SELECT @@global.tx_isolation;
查看InnoDB会话级别的事务隔离级别：SELECT @@tx_isolation;

修改事务隔离级别：
mysql> set global transaction isolation level read committed;
mysql> set session transaction isolation level read committed;

6. MySQL数据库下测试结果

1）并发更新， 表tab1 的一条记录id=1 num=1，两个session分别执行事务1，2
分别输入下面的语句

事务1 (session1) ：

    start transaction；

    update tab1 set num=num+1 where id=1；

=========

一条记录被更新，select发现num=2;

事务2 (session2)：

    start transaction;

    update tab1 set num=num+1 where id=1;

=========

事务2会被阻塞，然后session1输入commit;提交事务1。此时事务2更新成功。

session1 select一下会发现num还是2（不允许脏读 ）；session2 select 一下 num 为3（update可以得到最新提交过的数据然后更新，但是如果没有update，一直是select 的话select得到的num一直是1 ），然后commit；

session1 select 发现num为3 ，session2 select 发现num为3；

所以，事务中update是有行写锁（排他锁）的，不会发生： 脏读和不可重复读（对于自身有update的事务，update之后是可以读到最新数据的，这属于例外？^_^ ）

2）关于幻象读 ，innodb默认事务隔离级别是不会出现的；摘自网上，测试通过：

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

session 1 创建表并插入测试数据

mysql> create table test(i int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)
session 2 查询，没有数据，正常，session1没有提交，不允许脏读

mysql> select * from test;
Empty set (0.00 sec)
session 1 提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session 2 查询，还是没有数据，没有产生幻象读

mysql> select * from test;
Empty set (0.00 sec)

3）最后，现在应该知道数据库并发控制并不复杂，交给数据库的事务就行，mysql 选用innodb引擎，不会出现“不可重复读”。

在实际使用的过程中，会碰到多用户并发操作数据库导致数据错误的问题，解决方案下次再单独写篇博客记录。

转载于:https://my.oschina.net/liangbo/blog/364278