思考并回答以下问题：

事务的隔离性通过事务的隔离级别实现，而事务的隔离级别则是通过锁机制实现。有哪些事务隔离级别？
为什么使用间隙锁能避免幻读现象？

事务的首要任务是保证一系列更新语句的原子性，锁的首要任务是解决多用户并发访问可能导致的数据不一致问题。如果事务与事务之间存在并发操作，此时可以通过事务之间的隔离级别实现事务的隔离性，从而实现事务间数据的并发访问。

事务的ACID特性

事务的ACID特性由原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durabilily）4个英文单词的首字母组成（如图1所示）。

图1 事务的ACID特性

1．原子性（atomicity）

原子性用于标识事务是否完全地完成。一个事务的任何更新都要在系统上完全完成，如果由于某种原因出错，事务不能完成它的全部任务，那么系统将返回到事务开始前的状态。回顾银行转账业务，如果在转帐的过程中出现错误，那么整个事务将被回滚。只有事务中的所有修改操作成功执行，事务的更新才被写入外存（例如硬盘），并使更新永久化。

2．一致性（consistency）

事务的一致性保证了事务完成后，数据库能够处于一致性状态。如果事务执行过程中出现错误，那么数据库中的所有变化将自动地回滚，回滚到另一种一致性状态。回顾银行转账业务，在转账前，两个账户处于某个初始状态（一致性状态），如果转账成功，则两个账户处于新的一致性状态。如果转账失败，那么事务将被回滚到初始状态（一致性状态）。

3．隔离性（isolation）

同一时刻执行多个事务时，一个事务的执行不能被其他事务干扰。事务的隔离性确保多个事务并发访问数据时，各个事务不能相互干扰，好像只有自己在访问数据。事务的隔离性通过事务的隔离级别实现，而事务的隔离级别则是通过锁机制实现。不同种类的事务隔离级别使用的锁机制也不相同，可以这样认为，事务是对一系列更新操作的封装（保证了多个更新操作的原子性），事务的隔离级别是对锁机制的封装（保证了多个事务可以并发地访问数据）。

4．持久性（durabilily）

持久性意味着事务一旦成功执行，在系统中产生的所有变化将是永久的。回顾银行转账业务，无论转账成功还是失败，资金的转移将永久地保存在数据库的服务器硬盘中。

事务的隔离级别与并发问题

事务的隔离级别是事务并发控制的整体解决方案，是综合利用各种类型的锁机制解决并发问题的整体解决方案。SQL标准定义了4种隔离级别：read uncommitted（读取未提交的数据）、read committed（读取提交的数据）、repeatable read（可重复读）以及serializable（串行化）。4种隔离级别逐渐增强，其中，read uncommitted的隔离级别最低，serializable的隔离级别最高。

1．read uncommitted（读取未提交的数据）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。该隔离级别很少用于实际应用，并且它的性能也不比其他隔离级别好多少。

2．read committed（读取提交的数据）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。

3．repeatable read（可重复读）

这是MySQL默认的事务隔离级别，它确保在同一事务内相同的查询语句的执行结果一致。

4．serializable（串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突。换言之，它会在每条select语句后自动加上lock in share mode，为每个查询数据施加一个共享锁。该级别可能会导致大量的锁等待现象。该隔离级别主要用于InnoDB存储引擎的分布式事务。

低级别的事务隔离可以提高事务的并发访问性能，却可能导致较多的并发问题（例如脏读、不可重复读、幻读等并发问题）；高级别的事务隔离可以有效避免并发问题，但会降低事务的并发访问性能，可能导致出现大量的锁等待，甚至死锁现象。如表1所示，read uncommitted隔离级别可能导致脏读、不可重复读、幻读等并发问题；而read committed隔离级别解决了脏读问题，却无法解决不可重复读、幻读等并发问题；repeatable read隔离级别可以解决脏读、不可重复读问题，却无法解决幻读问题；serializable隔离级别可以解决脏读、不可重复读、幻读等并发问题，却可能导致大量的锁等待现象。4种隔离级别逐渐增强，每种隔离级别解决一个并发问题。

表1 事务的隔离级别与并发问题

脏读（dirty read）：一个事务可以读到另一个事务未提交的数据，脏读问题显然违背了事务的隔离性原则。

不可重复读（non-repeatable read）：同一个事务内，两条相同的查询语句的查询结果不一致。

幻读（phantom read）：同一个事务内，两条相同的查询语句的查询结果应该相同。但是，如果另一个事务同时提交了新数据，当本事务再更新时，就会“惊奇地”发现这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

查看当前MySQL会话的事务隔离级别可以使用MySQL命令“select @@session.tx_isolation;”。查看MySQL服务实例全局的事务隔离级别可以使用MySQL命令“select @@global.tx_isolation;”。执行结果如图2所示，从图中可以看出，MySQL默认的事务隔离级别为repeatable read（可重复读）。

图2 查看MySQL的事务隔离级别

设置事务的隔离级别

InnoDB支持4种事务隔离级别，在InnoDB存储引擎中，可以使用以下命令设置事务的隔离级别。

1
2
3

set { global | session } transaction isolation level {
    read uncommitted | read committed | repeatable read | serializable
}

合理地设置事务的隔离级别，可以有效避免脏读、不可重复读、幻读等并发问题。

场景描述1：脏读现象。

将事务的隔离级别设置为read uncommitted可能出现脏读、不可重复读以及幻读等问题，以脏读现象为例。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，然后将当前MySQL会话的事务隔离级别设置为read uncommitted，接着开启事务，查询account表中的所有记录，执行结果如图3所示。

alter table account engine=InnoDB;
set session transaction isolation level read uncommitted;
select @@tx_isolation;
start transaction;

select * from account;

图3 将事务的隔离级别设置为read uncommitted

步骤2：打开MySQL客户机B，执行下面的SQL语句，首先将当前MySQL会话的事务隔离级别设置为read uncommitted，然后开启事务，接着将account表中account_no=1的账户增加1000元钱。

1
2
3

set session transaction isolation level read uncommitted;
start transaction;
update account set balance=balance+1000 where account_no=1;

步骤3：在MySQL客户机A上执行下面的SQL语句，查询account表中的所有记录，执行结果如图4所示。从图中可以看出，MySQL客户机A看到了MySQL客户机B尚未提交的更新结果，造成脏读现象。

1	select * from account;

图4 read uncommitted隔离级别可能造成脏读问题

步骤4：关闭MySQL客户机A与MySQL客户机B，由于MySQL客户机A与MySQL客户机B的事务没有提交，因此，account表中的数据没有发生变化，“甲”账户的余额依然是200元。

场景描述2：不可重复读现象。

将事务的隔离级别设置为read committed可以避免脏读现象，但可能出现不可重复读以及幻读等现象，以不可重复读现象为例。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，然后将当前MySQL会话的事务隔离级别设置为read committed，接着开启事务，查询account表中的所有记录，执行结果如图5所示。

alter table account engine=InnoDB;
set session transaction isolation level read committed;
select @@tx_isolation;
start transaction;

select * from account;

图5 将事务的隔离级别设置为read committed

步骤2：打开MySQL客户机B，执行下面的SQL语句，首先将当前MySQL会话的事务隔离级别设置为read committed，然后开启事务，接着将account表中account_no=1的账户增加1000元钱，最后提交事务。

set session transaction isolation level read committed;
start transaction;
update account set balance=balance+1000 where account_no=1;
commit;

步骤3：在MySQL客户机A上执行下面的SQL语句，查询account表中的所有记录，执行结果如图6所示。两次查询结果对比可以看出，MySQL客户机A在同一个事务中两次执行“select * from account;”的结果不相同，造成不可重复读现象。

1	select * from account;

图6 read committed隔离级别可能造成不可重复读

说明
不可重复读现象与脏读现象的区别在于，脏读现象是读取了其他事务未提交的数据；而不可重复读现象读到的是其他事务已经提交（commit）的数据。

步骤4：关闭MySQL客户机A与MySQL客户机B，由于MySQL客户机B的事务已经提交，因此，account表中“甲”账户的余额从200元增加到1200元。

场景描述3：幻读现象。

将事务的隔离级别设置为repeatable read可以避免脏读以及不可重复读现象，但可能出现幻读现象。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，然后将当前MySQL会话的事务隔离级别设置为repeatable read，接着开启事务，查询account表中是否存在account_no=100的账户信息，执行结果如图7所示。

alter table account engine=InnoDB;
set session transaction isolation level repeatable read;
select @@tx_isolation;
start transaction;
select * from account where account_no=100;

图7 事务的隔离级别设置为repeatable read

步骤2：打开MySQL客户机B，执行下面的SQL语句，首先将当前MySQL会话的事务隔离级别设置为repeatable read，接着开启事务，然后向account表中添加一条“己”账户信息，并将account_no赋值为100，最后提交事务，执行结果如图8所示。

set session transaction isolation level repeatable read;
start transaction;
insert into account values(100,'己',5000);
commit;

图8 向account表中添加一条“己”账户信息

步骤3：接着在MySQL客户机A上执行下面的SQL语句，查询account表中是否存在account_no=100的账户信息，执行结果如图9所示。从图中可以看出，account 表中不存在account_no=100的账户信息。

1	select * from account where account_no=100;

图9 查询account表中是否存在account_no=100的账户信息

步骤4：由于MySQL客户机A检测到account表中不存在account_no=100的账户信息，因此MySQL客户机A就可以向account表中插入一条account_no=100的账户信息。在MySQL客户机A上执行下面的insert语句，向account表中添加一条“庚”的账户信息，并将account_no赋值为100，执行结果如图10所示。

1	insert into account values(100,'庚',5000);

图10 repeatable read隔离级别可能造成幻读问题

从运行结果可以看出，account表中确实存在account_no=100的账户信息，但由于repeatable read（可重复读）隔离级别使用了“障眼法”，使得MySQL客户机A无法查询到account_no=100的账户信息，这种现象称为幻读现象。

说明
幻读现象与不可重复读现象不同之处在于，幻读现象读不到其他事务已经提交（commit）的数据，而不可重复读现象读到的是其他事务已经提交（commit）的数据。

场景描述4：并发访问性能问题

避免幻读现象的方法有两个。

方法一：保持事务的隔离级别repeatable read不变，利用间隙锁的特点，对查询结果集施加共享锁（lock in share mode）或者排他锁（for update）。这种方法要求数据库开发人员了解间隙锁的特点。

方法二：将事务的隔离级别设置为serializable，可以避免幻读现象，这种方法最为简单，先以方法二为例。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，然后将当前MySQL会话的事务隔离级别设置为serializable，接着开启事务，查询account表中是否存在account_no=200的账户信息，执行结果如图11所示。

alter table account engine=InnoDB;
set session transaction isolation level serializable;
select @@tx_isolation;
start transaction;
select * from account where account_no=200;

图11 将事务的隔离级别设置为serializable

步骤2：打开MySQL客户机B，执行下面的SQL语句，首先将当前MySQL会话的事务隔离级别设置为serializable，然后开启事务，接着向account表中添加一条“庚”的账户信息，并将account_no赋值为200，执行结果如图12所示。从图中可以看出，MySQL客户机B发生锁等待现象，降低了事务间的并发访问性能（虽然解决了幻读问题）。

set session transaction isolation level serializable;
start transaction;

insert into account values(200,'庚',5000);

图12 serializable隔离级别可以防止幻读问题说明

由于InnoDB存储引擎发生了锁等待超时引发的错误异常，InnoDB存储引擎回滚引发了该错误异常的事务，因此，“庚”的账户信息并没有添加到account表中。

将事务隔离级别设置为serializable，可以有效避免幻读现象。然而，serializable隔离级别会降低MySQL的并发访问性能，因此，不建议将事务的隔离级别设置为serializable。

使用间隙锁避免幻读现象

MySQL默认的事务隔离级别为repeatable read。为了保持事务的隔离级别repeatable read不变，利用间隙锁的特点对查询结果集施加共享锁（lock in share mode）或者排他锁（for update），同样可以避免幻读现象，同时也不至于降低MySQL的并发访问性能。当然这种方法首先要求数据库开发人员了解InnoDB间隙锁的特点。

场景描述5：将事务的隔离级别设置为repeatable read，可以避免脏读以及不可重复读现象，但可能出现幻读现象。通过引入间隙锁，可以避免幻读现象。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，然后将当前MySQL会话的事务隔离级别设置为repeatable read，接着开启事务，查询account表中是否存在account_no=200的账户信息，执行结果如图13所示。

alter table account engine=InnoDB;
set session transaction isolation level repeatable read;
select @@tx_isolation;
start transaction;
select * from account where account_no=200 lock in share mode;

图13 将事务的隔离级别设置为repeatable read

说明
虽然account表中不存在account_no=200的账户信息，但最后一条 select语句为account_no=200的账户信息施加了间隙锁（共享锁）。

步骤2：打开MySQL客户机B，执行下面的SQL语句，首先将当前MySQL会话的事务隔离级别设置为repeatable read，接着开启事务，然后向account表中添加一条“庚”的账户信息，并将account_no赋值为200。insert语句将被阻塞，执行结果如图14所示。

1
2
3

set session transaction isolation level repeatable read;
start transaction;
insert into account values(200,'庚',5000);

图14 使用间隙锁锁定account_no=200的账户，insert语句被阻塞

步骤3：接着在MySQL客户机A上执行下面的SQL语句，查询account表中是否存在account_no=200的账户信息，执行结果如图15所示。从图中可以看出，account表中不存在account_no=200的账户信息。

1	select * from account where account_no=200;lock in share mode

图15 查询account表中是否存在account_no=200的账户信息

步骤4：由于MySQL客户机A检测到account表中不存在account_no=200的账户信息，因此MySQL客户机A就可以向account表中插入一条account_no=200的账户信息。在MySQL客户机A上执行下面的insert语句，向account表中添加一条“庚”的账户信息，并将account_no赋值为200，执行结果如图16所示。

1	insert into account values(200,'庚',5000);

图16 成功添加account_no=200的账户信息

从运行结果可以看出，当MySQL的事务隔离级别是repeatable read时，数据库开发人员可以利用间隙锁的特点，避免幻读现象。

事务与锁机制注意事项

使用事务与锁机制还应该注意以下内容。

锁的粒度越小，应用系统的并发性能就越高。由于InnoDB支持行级锁，如果需要提高应用系统的并发性能，建议选用InnoDB存储引擎。
如果事务的隔离级别无法解决事务的并发问题，数据库开发人员只有在完全了解锁机制的情况下，才能在SQL语句中手动设置锁，否则应该使用事务的隔离级别。
使用事务时，尽量避免在一个事务中使用不同存储引擎的表。
尽量缩短锁的生命周期。例如，在事务中避免使用长事务，可以将长事务拆分成若干个短事务。在事务中避免使用循环语句。
优化表结构，优化SQL语句，尽量缩小锁的作用范围。例如，可以将大表拆分成小表，从而缩小锁的作用范围。
InnoDB 默认的事务隔离级别是 repeatable read，而 repeatable read 隔离级别使用间隙锁实现InnoDB的行级锁。不合理的索引可能导致行级锁升级为表级锁，从而引发严重的锁等待问题。
对于InnoDB行级锁而言，设置锁等待超时参数为合理范围，编写锁等待超时异常处理程序，解决发生锁等待问题（甚至死锁）。
为避免死锁，一个事务对多条记录进行更新操作时，当获得所有记录的排他锁后，再进行更新操作。
为避免死锁，一个事务对多个表进行更新操作时，当获得所有表的排他锁后，再进行更新操作。
为避免死锁，确保所有关联事务均以相同的顺序访问表和记录。
必要时，使用表级锁来避免死锁。
避免在一个单独事务中混合使用存储引擎。在服务器级别，MySQL不能管理事务，事务是由存储引擎实现的，尽量避免在一个单独事务中混合使用存储引擎。如果在一个单独事务中混合了事务表和非事务表，若一切正常，这个事务就没有问题；但是如果执行回滚操作，非事务表改变的数据并不会回滚。若数据库的一致性遭到了破坏，则很难恢复和呈现完整的事务。

习题

1．请简单描述事务的必要性。

2．关闭MySQL自动提交的方法有哪些？您推荐数据库开发人员使用哪一种方法？

3．关闭MySQL自动提交后，提交更新语句的方法有哪些？您推荐数据库开发人员使用哪一种方法？

4．请简单描述典型的事务处理使用方法。

5．请简单描述典型的事务保存点使用方法。您是如何理解保存点是“临时状态”这句话的？

6．请简单描述锁机制的必要性。

7．为MyISAM表施加表级锁的语法格式是什么？

8．为MyISAM表施加表级锁时，read local与read选项有什么区别？

9．您是如何理解锁的粒度、隐式锁与显式锁、锁的类型、锁的钥匙以及锁的生命周期等概念的？

10．您如何理解锁的粒度、锁的生命周期与数据库的并发性能之间的关系？

11．您如何理解锁的粒度、锁的生命周期与服务器资源之间的关系？

12．“选课系统”应该使用哪种粒度的锁机制？为什么？

13．为InnoDB表施加行级锁的语法格式是什么？

14．请列举现实生活中的“资源竞争”问题，如何使用锁机制解决此类“资源竞争”问题？

15．完成调课功能的replace_course_proc()存储过程以及完成选课功能choose_proc()存储过程使用了行级锁解决了“资源竞争”问题，能不能将存储过程中下面的select语句写在“start transaction;”语句之前，以便缩短行级锁的生命周期？

1	select state into status from course where course_no=c_no;

16．InnoDB什么时候使用间隙锁，什么时候使用记录锁？间隙锁与记录锁之间的区别是什么？

17．锁等待与死锁之间是什么关系？

18．请解释事务的ACID特性。

19．MySQL支持哪些事务隔离级别？默认的事务隔离级别是什么？

20．每一种事务隔离级别可能引发什么问题？

21．脏读现象、不可重复读现象以及幻读现象之间有什么区别？

22．您如何理解事务、锁机制、事务的隔离级别之间的关系？