2024年1月25日发(作者：)

第9章 MySQL中的事务及并发控制

事务是多用户系统的一个数据操作基本单元，一个事务在执行过程中对资源的占用方式是动态的，同一时刻需要的资源是有限的。如果事务按串行的方式执行，那么系统资源会大量处于空闲状态，为了提高系统资源的利用率，充分实现数据资源的共享，应允许多个事务并行地执行。

因此，数据库管理系统必须对多事务的并发操作加以控制，提供一整套完善的并发控制机制，以防止多用户并发使用数据库时造成数据错误和程序运行错误，从而保证数据的完整性。

【学习目标】

 事务的概念及事务的ACID特性：原子性、持久性、隔离性和一致性

 事务SQL控制语句

 事务的可串行化调度

 并发操作的三种问题：丢失修改、读脏数据和不可重复读

 MySQL中的封锁机制及三个级别封锁协议

9.1 事务

9.1.1 事务的概念

如果一个业务逻辑中包含的若干操作，要么都成功执行要么不执行其中任何一个操作，是一个不可分割的工作单元，那么这组操作序列我们定义为事务。

9.1.2 事务的ACID特性

事务是用户定义的一组操作序列，这些操作要么全做要么全不做，事务的四个特性包括：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1. 原子性：事务里面的操作要么都执行，要么都不执行

1

2. 一致性：事务执行之前和执行之后数据库状态保持一致

3. 隔离性：一个事务的执行不被其他事务干扰

4. 持久性：事务对数据库的改变是永久性的

9.1.3 MySQL事务控制语句

MySQL中可以使用BEGIN开始事务，使用COMMIT结束事务，中间可以使用ROLLBACK回滚事务。COMMIT的作用是提交，即提交事务的所有操作，事务提交是将事务中所有对数据的更新写回到磁盘上的物理数据库中去，事务正常结束；ROLLBACK的作用是回滚，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。

1. 事务 SQL 控制语句

• START TRANSACTION （或 BEGIN ）：显式开始一个新事务

• SAVEPOINT ：分配事务过程中的一个位置，以供将来引用

• COMMIT ：永久记录当前事务所做的更改

• ROLLBACK ：取消当前事务所做的更改

• ROLLBACK TO SAVEPOINT ：取消在 savepoint 之后执行的更改

• RELEASE SAVEPOINT ：删除 savepoint 标识符

• SET AUTOCOMMIT ：为当前连接禁用或启用默认 autocommit 模式

2. AUTOCOMMIT 模式

MySQL默认操作模式就是autocommit自动提交模式，这表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。应注意，在一个表上利用事务处理业务逻辑时，要确保你所操作的表是支持事务处理的，否则，

autocommit的值对执行结果没有任何影响。

【例9-1】查看当前会话的autocommit值，往职工表中插入两条记录（职工号：w13，姓名：王小小）和（职工号：w14，姓名：张婷婷），输出职工表的数据，然后执行回滚操作，再次输出职工表的数据。

show variables like '%autocommit%';

insert into 职工(职工号,姓名) values('w13','王小小');

insert into 职工(职工号,姓名) values('w14','张婷婷');

2

select * from 职工;

rollback;

select * from 职工;

3. 事务的显示提交和隐式提交

事务的显示提交命令是COMMIT，在非autocommit模式下，要将一个事务包含的若干条语句执行结果永久写入到数据库中，可以使用COMMIT命令显示提交事务。

在MySQL中，下面两个事务控制语句具有隐式提交并进而终止当前事务的作用：BEGIN/START TRANSACTION和 SET AUTOCOMMIT=1。

4. 事务的定义和使用

【例9-3】在“银行系统”数据库中，有一张表“账户”，其中有A、B两个人的账户余额信息，表中的数据如图9-6所示。定义一个事务，包含两个操作：从A账户余额扣除1000；从B账户余额增加1000。

start transaction;

update 账户

set 余额=余额-1000

where 账户名='A';

update 账户

set 余额=余额+1000

where 账户名='B';

commit;

select * from 账户;

【例9-4】假设由于某种原因，A账户上的余额只扣除了1000而B账户上的余额却增加了1500，导致转账操作出错，则撤销回滚事务，A和B账户上的余额保持转账操作前的值。

start transaction;

update 账户

set 余额=余额-1000

where 账户名='A';

update 账户

3

set 余额=余额+1500

where 账户名='B';

rollback;

select * from 账户;

【例9-5】为了模拟A账户余额不足的情况，先将A账户的余额从现在的1000改为800，B账户上的余额是4000。定义一个事务，先将B账户的余额增加1000，然后再将A账户的余额扣除1000，此时由于账户表上的触发器（余额不能小于0）保护，导致A账户的扣款操作失败，最后用rollback命令撤销回滚事务，A和B账户上的余额保持转账操作前的值。

start transaction;

update 账户

set 余额=余额+1000

where 账户名='B';

update 账户

set 余额=余额-1000

where 账户名='A';

9.1.4 事务的调度

在数据库系统中，往往有多个事务同时提出处理请求，这时需要决定这些事务的执行顺序，这就是事务的调度。基本的事务调度方式有两种：串行和并行。

如果一组事务按并行调度的方式执行，不同事务的指令是交叉处理的，那么调度的策略比串行要多得多，但不能保证每种调度的执行结果都是正确的，当且仅当其结果与按某一次串行地执行它们时的结果相同，我们称这种调度策略为可串行化（serializable）的调度。

可串行化是并发事务正确性的准则，判断一个并发调度是否正确的充要条件是它是否是可串行化的。

表9-1 串行调度和并行调度

T1 T2 T1 T2 T1 T2 T1 T2

4

读A=2000

A=A-100

读A=2000 读A=2000

A=A*1.05 A=A-100

读A=2000

A=A-100

读A=2000

A=A*1.05

写A=1900

读B=2500

B=B+100

写A=2100 写A=1900

读B=2500

B=B*1.05

读A=1900

A=A*1.05 写A=1900

写A=2100

读B=2500

B=B*1.05

写B=2600 写B=2625 写A=1995

读A=1900 读A=2100

A=A*1.05 A=A-100

读B=2500

B=B+100

写A=1995 写A=2000

读B=2600

B=B*1.05

写B=2730

串行调度1

读B=2625

B=B+100

写B=2725

串行调度2

写B=2600 读B=2500

写B=2625

读B=2600 B=B+100

B=B*1.05

写B=2730 写B=2600

并行调度1 并行调度2

9.2 并发操作的可能问题

当多个用户并发地存取数据库时，就可能出现多个事务同时存取同一数据的情况，事务的并发操作可能带来以下三个问题：丢失修改、读脏数据和不可重复读。

9.2.1 丢失修改（Lost Update）

当两个或多个事务选择数据库中同一数据，然后基于最初选定的值更新该数据时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新，丢失修改也称为写-写错误。

9.2.2 读脏数据（Dirty Read）

当一个事务修改某个数据后，另一事务对该数据进行了读取，由于某种原因前一事务撤销了对该数据的修改，即将修改过的数据恢复原值，那么后一事务读到的数据与数据与数据库中的数据不一致，称之为读脏数据，也叫写-读错误。

5

9.2.3 不可重复读（Unrepeatable Read）

当一个事务读取某个数据后，另一事务执行了对该数据的更新，当前一事务再次读取该数据（希望与第一次读取的是相同的值）时，得到的数据与前一次的不一样，称之为不可重读读，也叫读-写错误。事务T1第一次按某个条件读取数据后，有以下三种情况会造成不可重复读。

（1）事务T2对同一数据执行修改操作，这时事务T1再次按相同条件读数据时，得到与前一次不同的值。

（2）事务T2对同一数据执行删除操作，这时事务T1再次按相同条件读数据时，发现某些记录神秘的消失了。

（3）事务T2将满足同样条件的记录执行插入操作，事务T1按相同条件再次读数据时，发现多了一些记录。

9.3 封锁机制

封锁机制是并发控制的重要手段，所谓封锁，就是通过对数据加锁的方式让事务对所需的资源对象实现一定的控制能力。

封锁的基本步骤是：首先，事务T向系统申请对所需资源对象的加锁请求；然后，系统在条件成熟时，同意事务T的加锁请求，从而使事务T获得对所需数据的控制能力；最后，事务T完成所有操作后释放数据资源对象上的锁，从而放弃对该数据的控制权。

9.3.1 锁的类型

基本的封锁类型有两种：排他锁（Exclusive Locks，简称X锁）和共享锁（Shared Locks，简称S锁）。

排他锁，又称写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务既不能读取和修改A，也不能再对A加任何锁，直到T释放A上的锁。

共享锁，又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。

6

9.3.2 封锁协议

仅仅知道对数据加X锁或S锁，并不能保证事务的一致性。在对数据对象加锁时，还需要对何时加锁、加什么类型的锁、锁的持续时间以及什么时候释放锁等问题进行约定。根据对这些问题的约定方式的不同，封锁的规则也就是封锁协议分为三级，各级封锁协议对事务并发操作可能出现的丢失修改、读脏数据或不可重复读等问题，能在不同程度上予以解决。

（1）一级封锁协议

一级封锁协议的规则是：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放（事务结束包括正常结束COMMIT和非正常结束ROLLBACK）。

由此可知，一级封锁协议可以解决丢失修改的问题，但协议中对读数据没有加锁的要求，所以读脏数据和不可重复读的问题仍然存在。

（2）二级封锁协议

二级封锁协议的规则是：在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。

二级封锁协议不仅能解决丢失修改的问题，还进一步解决了读脏数据的问题。

（3）三级封锁协议

三级封锁协议的规则是：在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

三级封锁协议不仅解决了丢失修改和读脏数据的问题，还进一步解决了不可重复读的问题。

9.3.3 封锁产生的问题

封锁带来的一个重要问题是可能引起“活锁”与“死锁”。在并发事务处理中，由于锁会使一个事务处于等待状态而调度其他事务处理，因此该事务可能会因优先级低而永远等待下去。比如，如果事务T1封锁了数据R，事务T2又请求封锁数据R，于是T2等待；T3也请求封锁数据R，当T1释放了R上的锁之后，系统首先批准了T3的请求，T2仍然等待；然后T4又请求封锁R，当T3释放R上的锁之后，系统又批准了T4的请求……T2有可能永远等待，这种现象称为“活锁”。

两个以上事务循环等待被同组中另一事务锁住的数据的情况，比如，如果事7

务T1封锁了数据R1，T2封锁了数据R2，然后T1又请求封锁R2，因T2封锁了R2,于是T1等待T2释放R2上的锁；接着T2又请求封锁R1，因T1封锁了R1，于是T2等待T1释放R1上的锁。这样就出现了T1在等待T2,而T2又在等待T1，的局面，T1、T2两个事务永远不能结束，这种现象称为“死锁”。

预防“死锁”的发生，主要有以下几种方法。

（1）一次性锁请求

每一个事务在处理时一次提出所有的加锁请求，仅当这些请求全部满足时事务处理才进行，否则让其等待。

（2）锁请求排序

将每个数据单元标以线性顺序，然后要求每一个事务都按此顺序提出锁请求。

（3）序列化处理

通过应用设计为每一个数据单元建立一个“主人”程序，对给定数据单元的所有请求都发送给“主人”，而“主人”以单道的形式运行。

（4）资源剥夺

当每个事务因锁请求不能满足而受阻时，强行令两个冲突的事务中的一个ABORT释放所有的锁，然后重新运行。

9.4 习题

1、什么是事务的可串行化调度？举例说明。

2、什么是事务？事务的基本性质是什么？

3、并发操作会带来哪些问题？举例说明。

4、什么是死锁？如何预防死锁的发生？

5、什么是封锁协议？不同级别的封锁协议主要区别是什么？

8