目录
一、事务管理
1、概念及特性
事务：用户定义的一组数据库操作，是一个不可分割的工作单位，这些操作要么同时成功，要么同时失败。可以是一句SQL语句，也可能是一组SQL语句。
事物的特性：
① 原子性：事务中一系列操作是不可再分的工作单位
② 一致性：事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态
③ 隔离性：一个事务的执行不能被其他事务所干扰
④ 持续性：一个事务一旦提交，该事务的操作结果永久有效
2、事务控制
（1）事务控制语句
1、开始事务
begin tran[saction] 事务名称2、提交事务
commit tran[saction] 事务名称3、回滚事务
即撤销事务中数据库数据更新的操作，回到begin 语句之前的状态，也就是撤销事务
rollback tran[saction] 事务名称|保存点4、设置保存点
save tran[saction] 保存点5、设置事务自动提交
- 显式事务：sql server默认自动提交，需要手动执行 开始事务 语句，才可以执行 提交事务 或 回滚事务 语句；此模式下若未执行 开始事务 语句，则数据库会直接提交，无法回滚
- 隐式事务：不需要执行 开始事务 语句，可以进行提交和回滚；一个事务开始后，在其他查询窗口无法查询到该事务涉及的数据，需要提交或回滚后才能查询到
显示事务和隐式事务区别 显式事务 隐式事务 开启事务 begin tran set implicit_transactions on --开启隐式事务 提交事务 commit 回滚事务 rollback 最后 / set implicit_transactions off --关闭隐式事务 set implicit_transactions off --开启显式事务 set implicit_transactions on --开启隐式事务
显示事务举例
假设两个表，一个表为银行bank表，一个为卡类型bank_类型表
主键：bank_类型表的id 外键：bank表的id
当在bank表内插入不存在的id——5，则会出现错误
此时运用事务会进行回滚，不会执行该错误操作，也不会对表有任何影响
begin try begin tran --开启事务 --set implicit_transactions on; --开启隐式事务 insert into bank(id,name,money) values(100,'烦烦',2000) --错误语句 commit --事务提交 end try begin catch rollback --事务回滚 print '出现异常！' end catch
create trigger tri_1 on 学生表
for delete
as if (select count(*)
from 选修表,deleted
where 选修表.xh=deleted.xh)>0 --说明选修表里待删除的记录还在
begin
delete 选修表
from 选修表,deleted
where 选修表.xh=deleted.xh
end二、并发控制
并发控制：确保及时纠正由并发操作导致错误的一些机制
1、问题引入
事务的并发操作产生的数据不一致性问题有三类：
丢失修改、不可重复读、读"脏"数据
2、并发执行带来的问题
并发控制就是要用正确的方式调度并发操作，使某个事务的执行不受其它事务的干扰
（1）丢失修改
两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失
（2）不可重复读
指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果
（3）读"脏"数据
事务T1修改某一数据后并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时T1修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致
例：高铁订票
①甲(T1)读出某列车余票A=20
②乙(T2)读出同一列车余票A也为20
③甲卖出一张车票，修改余额A←A-1，所以A为19，把A写回数据库
④乙也卖出一张票，修改余额A←A-1，所以A为19，把A写回数据库
卖出两张票，数据库中余票只减少1
这种情况称为丢失修改，是由并发操作引起的
原因：第4步中T2事务修改A并写回后覆盖了T1事务的修改
并发控制的主要技术：封锁、时间戳、乐观控制法、多版本并发控制
三、封锁
封锁：事务在对某个数据对象操作之前，先向系统发出加锁请求，加锁后事务对该数据对象有了一定的控制权，在事务释放它的锁之前，其他事务不能更新该数据对象
1、基本锁的类型
- 排它锁：又称写锁 / X锁，若事务T对数据对象A加上X锁，则只允许T 读取和修改A，任何其它事务都不能再对A读取和修改，直到T释放A上的锁
- T对A加锁，其他事务都不能再对A加任何形式的锁
- 共享锁：又称读锁 / S锁，若事务T对数据对象A加上S锁，则事务T可以读取A但不能修改 A，其它对A加S锁的事务也只能对A读取，而不能修改，直到T释放 A上的锁
- T对A加锁，其他事务只能对A加S锁
2、封锁粒度
（1）封锁粒度基本概念
- 指封锁对象的大小，封锁对象可以是逻辑单元，也可以是物理单元
- 封锁粒度越小，并发度越高，系统开销越大
- 封锁粒度越大，并发度越低，系统开销越小
（2）多粒度封锁
在多粒度封锁机制中，常采用粒度树管理封锁对象
一个结点加锁，隐含该结点的所有子节点也被加同样类型的锁
一个数据对象可能以显示封锁和隐式封锁两种方式封锁
- 显示封锁：事务直接对数据对象封锁
- 隐式封锁：由于上级结点加锁导致该数据对象也加上锁
一般加锁需要进行三种检查：
- 检查该数据对象上有无显式封锁与之冲突
- 检查所有上级结点，看本事务的显式封锁与该数据对象的隐式事务是否冲突
- 检查所有下级结点，看本事务的隐式封锁与其上的显示封锁是否冲突
3、意向锁
如果对某数据对象加锁时要进行如上的检查，效率很低，此时引入意向锁
（1）意向锁概念
对任意结点加锁时，必须先对其上层结点加意向锁
例如：T要对关系R加X锁，必须先对R所在的数据库加意向锁，在检查封锁冲突时，只需要检查数据库和关系R是否加了不相容的锁，而不需要检查R中每一个元组是否加X锁
（2）常用意向锁
- 意向共享锁（IS锁）：如果对一个数据对象加IS锁，表示它的子节点拟加S锁
- eg：若要对某个元组加S锁，则需要对关系和数据库加IS锁
- 意向排他锁（IX锁）：如果对一个数据对象加IX锁，表示它的子节点拟加X锁
- eg：若要对某个元组加X锁，则需要对关系和数据库加IX锁
- 共享意向排他锁（SIX锁）：如果对一个数据对象加SIX锁，表示对它加S锁，再加IX锁
- eg：若要对某个表加SIX锁，则表示该事务要读整个表（所以加S锁），同时更新个别元组（所以加IX锁）
（3）意向锁加锁方法
申请封锁按自上而下进行，释放封锁按从下到上进行
4、封锁协议
封锁协议级别越高，一致性程度越高
（1）一级封锁协议
事务T在修改数据对象R之前必须先对其加X锁，直到事务结束才释放
- 作用：一级封锁协议可防止丢失修改，并保证事务T是可恢复的
- 说明：在一级封锁协议中，只对修改数据规定加锁，对读数据没有加锁，所以它不能保证可重复读和不读“脏”数据
（2）二级封锁协议
在一级封锁协议的基础上，事务T在读取R之前先对其加S锁，读完后即可释放S锁
- 作用：二级封锁协议可以防止丢失修改和读“脏”数据
- 说明：在二级封锁协议中，由于读完数据后立刻释放S锁，所以它不能保证可重复读
（3）三级封锁协议
在一级封锁协议的基础上，事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放
- 作用：三级封锁协议可防止丢失修改、读脏数据和不可重复读
封锁协议 X锁（排它锁） S锁（共享锁） 解决的一致性问题 申请 释放 申请 释放 丢失修改 读"脏"数据 不可重复读 一级封锁 事务开始 事务结束 √ 二级封锁 事务开始 事务结束 读数据前 读完后立即 √ √ 三级封锁 事务开始 事务结束 事务开始 事务结束 √ √ √
3、活锁与死锁
（1）活锁
- 如果事务T₁封锁了数据R，事务T₂又请求封锁R，于是T₂等待。事务T₃也请求封锁R，当T₁释放了R上的封锁之后，系统首先批准了T₃的要求，T₂仍然等待。然后事务T₄又请求封锁R，当T₃释放了R上的封锁之后系统又批准了T₄的请求，…，这样T₂又可能永远等待
- 这种在多个事务请求对同一数据封锁时，总是使某一事务等待的情况称为活锁
- 避免活锁——先来先服务，当多个事务请求封锁同一数据对象时，按请求封锁的先后次序对这些事务排队，该数据对象上的锁一旦释放，首先批准申请队列中第一个事务获得锁
（2）死锁
- 多个事务交错等待其他事务释放锁而陷入僵持局面的现象
- 如果事务T₁封锁了数据R₁，事务T₂封锁了数据R₂；然后T₁又请求封锁R₂，由于T₂已封锁了R₂，因而T₁只能等待T₂释放R₂上的锁；接着T₂又请求封锁R₁，由于T₁已封锁了R₁，因而T₂只能等待T₁释放R₁上的锁；这样就出现了T₁等待T₂、T₂等待T₁的局面，显然T₁和T₂相互等待永远不能结束，形成了死锁
解决死锁问题：
1、采取一定措施来预防死锁的发生
2、允许发生死锁，然后采用一定手段定期诊断系统中有无死锁，若有则解除之
预防死锁：
1、一次封锁法：要求每个事务必须一次将所有要使用的数据全部加锁，否则该事务不能继续执行
2、顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁
四、并发调度的可串行性
1、可串行化
可串行化是并发事务正确性的准则
2、两段式封锁协议
两段式封锁协议是指所有事务必须分两个阶段对数据对象进行加锁和解锁
- 申请加锁阶段：事务在对任何数据进行读、写操作之前，进入申请加锁阶段，在该阶段事务可以申请封锁，但是不能解除任何已取得的封锁
- 释放封锁阶段：事务可以释放封锁，但是释放后不能申请新的封锁
按照两段式封锁协议，一个事务在事务开始时需要对事务访问的数据对象申请加锁，直到事务结束时才能释放封锁，因而三级封锁协议符合两段式封锁协议
五、SQL Server并发控制语句
1、锁的级别
行级锁 → 页级锁 → 盘区级锁（一个扩展盘区是8个连续的页）→ 表级锁 → 数据库级锁
2、锁的类型
之前已经介绍过排他锁X锁、共享锁S锁、意向锁，这里再介绍两个锁：
（1）更新锁
- 更新锁是为修改操作提供的页级排他锁
- 如果有多个事务同时对一个数据申请了共享锁，修改数据时，这些共享锁都会升级为排他锁，一个事务升级为排他锁与其他事务的共享锁不兼容，发生锁等待，两个锁都在等待对方释放共享锁，发生死锁
- 如果一个数据在修改前直接申请更新锁，在数据修改时升级为排他锁，就能避免死锁
（2）架构锁
架构锁包括架构修改锁和架构稳定锁，是为保证系统架构不被修改和删除而设置的锁
3、SQL Server 自动加锁功能
一般情况下，SQL Server能自动提供加锁功能，而不需要用户专门设置，这些功能表现在：
- 当用SELECT 语句访问数据库时，系统能自动用共享锁访问数据；在使用INSERT、UPDATE和DELETE 语句增加、修改和删除数据时，系统会自动给使用数据加排他锁
- 系统可用意向锁使锁之间的冲突最小化
- 当系统修改一个页时，会自动加更新锁。更新锁与共享锁兼容，而当修改了某页后，更新锁会上升为排他锁
- 当操作涉及参考表或者索引时，SQL Server会自动提供架构稳定锁和架构修改锁
4、锁定提示
虽然SQL Server提供自动加锁功能，但有时候需要通过锁定提示指定所需的封锁
在SELECT、INSERT、UPDATE、DELETE中使用锁定提示的格式：
表名 with (锁定提示)常用锁定提示：
- holdlock：将共享锁保留到事务完成
- nolock：不加任何锁（仅用于select语句）
- rowlock：行级共享锁，数据读完立马释放
- tablock：表级排他锁，防止其他事务读取更新表，并在事务结束前一直持有
- xlock：排他锁，在事务结束前一直持有
- updlock：更新锁，允许读取数据并在以后更新数据，确保读取数据时数据未被更改
【例1】 假设读者卡号为“1100001”和“1100002”的读者同时分别经甲、乙两个图书管理员(对应甲、乙两个事务)借阅书号为“JSJ001”的图书，定义甲、乙两个事务代码模拟实现并发借书操作，要求避免丢失修改问题的发生。
甲事务定义如下：begin try declare @kcsl smallint begin tran select @kcsl=库存数量 from 图书 with (xlock) where 图书编号='JSJ001' --事务开始上X锁 print '借书前库存数量:' print @kcsl waitfor delay '0:0:8' -延迟8s保证甲、乙两个事务并发执行 insert into 借阅(读者卡号,图书编号,借书日期) values('1100001','JSJ001','2015-5-7') update 图书 set 库存数量=@kcsl-1 where 图书编号='JSJ001' select @kcsl=库存数量 from 图书 where 图书编号='JSJ001' print '借书后库存数量:' print @kcsl commit end try begin catch rollback end catch乙事务定义中把甲事务定义中的“insert into”语句修改为下列语句即可：
insert into 借阅(读者卡号,图书编号,借书日期) values( '1100002','JSJ001','2015-5-7')
5、隔离
在SQL Server 中也可以通过设置事务的隔离级别来实现并发控制，设置隔离级别比直接使用封锁更简单
（1）隔离级别
- read uncommitted(未提交读)：该隔离级别最低，可以进行脏读，即使一项操作未做完或未提交，其他操作也可以读取未提交的数据，相当于NOLOCK，即不进行任何限制
- read committed(提交读)：默认隔离级别，避免数据的脏读，相当于TABLOCK，但是没有选用HOLDLOCK
- repeatable read(可重复读)：相当于同时选用了TABLOCK 和HOLDLOCK
- serializable(可串行化)：事务隔离的最高级别，事务之间完全隔离，相当于使用锁定提示中的XLOCK(包含UPDLOCK，数据库管理系统会自动判断是需要表级封锁还是元组级封锁)。如果事务在可串行化隔离级别上运行，则可以保证任何并发重叠事务均是串行的
隔离级别所允许的不同类型行为 隔离级别 脏读 不可重复读 幻影 read uncommitted(未提交读) 允许 允许 允许 read committed(提交读) / 允许 允许 repeatable read(可重复读) / / / serializable(可串行化) / / / set transaction isolation level 隔离级别隔离级别越低，并发效率越高，产生干扰可能性越大
六、课后习题
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
