MySQL的事务及隔离级别

操作中需要准备的数据库和表（注：SQL语句中标点符号统一为英文状态下）：
#选择shop数据库
use shop;
#创建sh_user表
create table sh_user(
id int unsigned primary key auto_increment comment ‘用户id’,
name varchar(100) not null default ’ ’ comment’用户名’,
password varchar(255) not null default ’ ’ comment’密码’,
salt char(32) not null default ’ ’ comment ‘密码盐’,
email varchar(128) not null default ’ ’ comment ‘邮箱’,
mobile char(11) not null default ’ ’ comment ‘手机号’,
level tinyint unsigned not null default 0 comment ‘用户级别’,
money decimal(10,2) unsigned not null default 0 comment ‘金额’,
gender tinyint unsigned not null default 0 comment ‘性别’,
qq varchar(20) not null default ’ ’ comment ‘QQ’,
is_active tinyint unsigned not null default 0 comment ‘是否激活’,
reg_time datetime default null comment ‘注册时间’,
create_time datetime not null default current_timestamp comment ‘创建时间’,
update_time datetime default null comment ‘更新时间’);
#添加数据
insert into sh_user (id,name,password,salt,money,is_active)values
(1,‘Alex’,MD5(CONCAT(MD5(‘123’),‘salt1’)),‘salt1’,1000,1),
(2,‘Bill’,MD5(CONCAT(MD5(‘123’),‘salt2’)),‘salt2’,1000,1);

一、事务处理
  事务处理可以保证在同一个事务中的操作具有同步性。
1.事务的概念
  在MySQL中，事务就是针对数据库的一组操它可以由一条或多条SQL语句组成，且每个SQL语句都是相互依赖的。只要在程序执行过程中有一条SQL语句执行失败或发生错误，则其他语句都不会执行。
  MySQL中的事务必须满足A、C、I、D这四个基本特性：
（1）原子性（Atomicity）。指一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。
（2）一致性（Consistency）。指在事务处理时，无论执行成功还是失败，都要保证数据库系统处于一致的状态，保证数据库系统不会返回到一个未处理的事务中。MySQL中的一致性主要是由日志机制实现，通过日志记录数据库的所有变化，为事务恢复提供了跟踪记录。
（3）隔离性（Isolation）。指当一个事务在执行时，不会受到其他事务的影响。
（4）持久性（Durability）。指事务一旦提交，其对数据库的修改就是永久性的。但事务的持久性不能做到百分百的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所有提交的数据可能都会丢失。

2.事务的基本操作
开启事务：start transaction；
提交事务：commit；
取消事务（回滚）：rollback；
rollback只能对未提交的事务进行回已提交的事务无法回滚。当执行commit或rollback后，当前的事务就会自动结束。

#查看用户数据
select name,money from sh_user;
#开启事务
start transaction;
#Alex减少100元
update sh_user set money=money-100 where name=‘Alex’;
#Bill增加100元
update sh_user set money=money+100 where name=‘Bill’;
#提交事务
commit;
#查询金额
select name,money from sh_user;

接下来测试事务的回滚：
start transaction;
#将Bill的金额扣除100元
update sh_user set money=money-100 where name=‘Bill’;
#查询Bill的金额
select name,money from sh_user where name=‘Bill’;
#回滚事务
rollback;
#查看Bill的金额
select name,money from sh_user where name =‘Bill’;
从查询结果可以看出，Bill的金额又恢复成1100元，说明事务回滚成功。

事务的自动提交
  MySQL默认是自动提交模式，如果没有显示开启事务（strat transaction），每一条SQL语句都会自动提交（commit）。若用户想要控制事务的自动提交方式，可以通过更改autocommit变量来实现，将其值设为1表示开启自动提交，设为0表示关闭自动提交。
#查看当前会话的autocommit值
select @@autocommit;

3.事务的保存点
  在回滚事务时，事务内所有的事务都将撤销。而若希望只撤销一部分，可以用保存点来实现。
  一个事务中可以创建多个保存点，在提交事务后，事务中的保存点就会被删除。另外，在回滚到某个保存点之后创建过的保存点也会消失。
#查询Alex的金额，结果为900
select name,money from sh_user where name=‘Alex’;
#开启事务
start transaction;
#Alex扣除100元
update sh_user set money=money-100 where name=‘Alex’;
#创建保存点s1
savepoint s1;
#Alex再扣除50元
update sh_user set money=money-50 where name=‘Alex’;
#回滚到保存点s1
rollback to savepoint s1;
#查询Alex的金额，结果为800
select name,money from sh_user where name=‘Alex’;
#再次回滚事务
rollback;
#查看Alex金额，结果为900
select name,money from sh_user where name=‘Alex’;

二、事务隔离级别
1.查看隔离级别
#查看全局隔离级
select @@global.transaction_isolation;
#查看当前会话中的隔离级
select @@session.transaction_isolation;
#查看下一个事务的隔离级
select @@transaction_isolation;
  全局的隔离级影响的是所有连接MySQL的用户，而当前会话的隔离级别只影响当前正在登录MySQL服务器的用户，不会影响其他用户。而下一个事务的隔离级别仅对当前用户的下一个事务操作有影响。
  在默认情况下，上述3种方式的返回结果都是repeatable-read，表示隔离级别为可重复读。

2.修改隔离级别
#修改事务隔离级别
set session transaction isolation level read uncommitted;
#查看是否修改成功
select @@session.transaction_isolation;

#修改事务隔离级别
set session transaction isolation level repeatable read;
#查看是否修改成功
select @@session.transaction_isolation;

  默认情况下，事务的访问模式为read write（读/写模式），表示事务可以执行读（查询）或写（更改、插入、删除等）操作。若开发需要，可以将事务的访问模式设置为readonly（只读模式），禁止对表进行修改。

3.MySQL中的4中隔离级别
（1）READ UNCOMMITTED（读取未提交）
  READ UNCOMMITTED是事务中最低的级别，在该级别下的事务可以读取到其他事务中未提交的数据，这种读取方式也被称为脏读（Dirty Read）。脏读是指一个事务读取了另外一个事务未提交的数据。
  开启两个命令行窗口，分别登陆MySQL数据库，执行use shop切换到shop数据库。然后使用这两个窗口分别模拟Alex和Bill，以下称为客户端A和客户端B。
#客户端B，MySQL默认隔离级别为repeatable read可以避免脏读，将级别设置为较低的read uncommitted
set session transaction isolation level read uncommitted;
#客户端B，查询Bill当前的金额为1100
select name,money from sh_user where name=‘Bill’;
#客户端A，开启事务，并执行转账操作
start transaction;
update sh_user set money=money-100 where name=‘Alex’;
update sh_user set money=money+100 where name=‘Bill’;
#客户端B查询金额，结果为1200，客户端A未提交，但金额已增加
select name,money from sh_user where name=‘Bill’;
#客户端B。避免客户端脏读，将客户端B的事务隔离级别设为READ COMMITTED（或更高级别）可以避免脏读
set session transaction isolation level read committed;
#客户端B，查询Bill金额，此时为1100
select name,money from sh_user where name=‘Bill’;
#客户端A，回滚
rollback;

（2）READ COMMITTED(读取提交)
  READ COMMITTED是大多数DBMS（如SQL Sever、Oracle）的默认隔离级别，但不包括MySQL。在该隔离级别下只能读取其他事务已经提交的数据，避免了脏读数据的现象。但是在该隔离级别下，会出现不可重复读（NON-REPEATABLE READ）的问题。
  不可重复读是指在一个事务中多次查询的结果不一致，原因是查询的过程中数据发生了改变。
  假设客户端A是Alex用户，客户端B是网站后台，演示客户端B的不可重复读。当客户端B的事务隔离级别为READ COMMITTED时，会出现不可重复读的情况。在客户端B中开启事务，查询Aliex的金额，然后在客户端A中将Alex的金额扣除100元，最后在客户端B再次查询Alex的金额。
#客户端B
set session transaction isolation level read committed;
start transaction;
select name,money from sh_user where name =‘Alex’;
#客户端A
update sh_user set money=money-100 where name=‘Alex’;
#客户端B
select name,money from sh_user where name =‘Alex’;
commit;
  从上述结果可以看出，客户端B在同一个事务中两次查询结果中的结果不一样，这就是不可重复读的情况。
  避免网站后台的不可重复读。将客户端B的事务隔离级别设为默认级别REPEATABLE READ,可以避免不可重复读的情况。在该级别下按照上一步的方式重新测试。
#客户端B
set session transaction isolation level repeatable read;
start transaction;
select name,money from sh_user where name=‘Alex’;
#客户端A
update sh_user set money=money+100 where name=‘Alex’;
#客户端B
select name,money from sh_user where name =‘Alex’;
commit;
  从上述结果可以看出，客户端B两次查询的结果是相同的，说明REPEATABLE READ可以避免不可重复读的情况。

（3）REPEATABLE READ(可重复读)
  REPEATABLE READ是MySQL的默认隔离级别，它解决了脏读和不可重复读的问题，确保了同一事物的多个实例在并发读取数据时，会看到同样的结果。
  在理论上，该隔离级会出现幻读（PHANTOM READ）的现象。幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不可重复读有些类似，同样发生在两次查询过程中。不同的是，幻读时由于其他事务做了插入记录的操作，导致记录数有所增加，不过，MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读问题。
  假设客户端A用于新增用户，客户端B用于统计金额
演示客户端B的幻读。
#客户端B
set session transaction isolation level read committed;
start transaction;
#查询总金额为2000
select sum(money) from sh_user;
#客户端A
insert into sh_user(id,name,money) values(3,‘Tom’,1000);
#再次查询总金额为3000
select sum(money) from sh_user;
commit;
#避免客户端B的幻读。将客户端B的隔离级别设置为repeatable read ，即可避免幻读
set session transaction isolation level repeatable read;
start transaction;
#查询总金额为3000
select sum(money) from sh_user;
#客户端A
insert into sh_user(id,name,money) values(4,‘d’,1000);
#客户端B，再次查询总金额仍为3000
select sum(money) from sh_user;
commit;

(4)SERIALIZABLE（可串行化）
  SERIALIZABLE是最高级别的隔离级，它在每个读的数据行上加锁，使之不会发生冲突，从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时（Timeout）和锁竞争（Lock Contention）现象，因此SERIALIZABLE也是性能最低的一种隔离级。除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级。
  假设客户端B执行查询操作，客户端A执行更新操作：
#演示可串行化，将客户端B的事务隔离级别设置为SERIALIZABLE，然后开启事务
set session transaction isolation level serializable;
start transaction;
#查看Alex的金额为900
select name,money from sh_user where name=‘Alex’;
#客户端A中增加金额
update sh_user set money=money+100 where name=‘Alex’;
（此时光标在不停闪烁，进入等待状态）
#客户端B提交事务，客户端A的操作才会执行，提示执行结果
commit;
（若客户端B一直未提交事务，客户端A的操作会一直等待，直到超时后报错，表示锁等待超时，尝试重新启动事务）
  在默认情况下，锁等待的超时时间为50秒，可通过以下语句查询：
select @@innodb_lock_wait_timeout;