第12章 并发控制 本章导读： 知识要点： SQL Server登录 12.1 事务处理 12.2 并发访问 12.3 锁

Presentation on theme: "第12章 并发控制 本章导读： 知识要点： SQL Server登录 12.1 事务处理 12.2 并发访问 12.3 锁"— Presentation transcript:

1 第12章 并发控制 本章导读： 知识要点： SQL Server登录 12.1 事务处理 12.2 并发访问 12.3 锁
第12章 并发控制 本章导读： 为了避免多用户并行存取数据库时，破坏事物的完整性和数据的一致性，SQL Server 提供了并发控制机制。并发控制机制主要通过事务隔离级别和封锁机制来调度并发事务的执行，使一个事务的执行不受其它事务的干扰。 知识要点： SQL Server登录  事务处理  并发访问  锁  事物隔离级别 12.1 事务处理 12.2 并发访问 12.3 锁 12.4 事务隔离级别

2 12.1 事务处理 SQL Server提供了一种事务处理的机制，用于确保数据的一致性和完整性。在事务处理过程中，所有操作序列都作为一个独立的逻辑单元被执行。只有所有操作序列都正确地执行完毕，事物处理才算成功提交，否则就回滚（撤销）到事物处理前的数据状态。 事务概述 事物模式

3 事务概述 事务（Transaction）是一组不可分割的、可执行的动作序列，是数据处理的逻辑单元，其包含的动作序列具有一定的偏序，即部分关键动作序列的顺序很重要，会影响事物运行结果。事物是恢复和并发的基本单位。 1．事物特性 事务可以是一条或一组SQL语句，也可以是整个应用程序，而一个应用程序也可能包含多个事务。事务有4个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability），它们统称为事务的ACID特性。 （1）原子性：是指事务中操作序列逻辑上作为一个工作单元整体考虑，要么全都执行，要么全都不执行。 （2）一致性：事务在完成时，所有数据必须从一个一致性状态变到另一个一致性状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。 （3）隔离性：是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发执行的其他事务是隔离的。一个事务能查看到另一个事物的数据状态，要么是修改它之前的状态，要么是修改它之后的状态，不会是中间状态的数据。 （4）持续性：也称永久性（Permanence），事务完成之后，它对于系统的影响是永久性的，无论发生何种操作，即使出现系统故障也将一直保持磁盘上。

4 事务概述 2．事物和批的区别 一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。编写应用程序时，一定要区分事务和批的差别： （1）批是一组整体编译的SQL语句，事务是一组作为逻辑工作单元执行的SQL语句。 （2）批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。 （3）编译时，批中某条语句存在语法错误，系统将终止批中所有语句；运行时，事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。

5 事物模式 SQL Server中的事务模式包括3种工作方式：自动提交事务、显式事务和隐式事务。 1．自动提交事务 自动提交事务是由T-SQL语句的特点自动划分的事务。它是 SQL Server 的默认模式，每条单独的T-SQL语句都是一个事务，自动提交或回滚，无须指定任何控制语句控制事务。 2．显式事务 显式事务是由用户显式定义的事务。在显式事务模式下，每个事物均以begin transaction 语句定义事务开始，用commit或 rollback 语句定义事务结束。主要事物控制语句如下： （1）begin transaction [事务名]：启动事务； （2）commit transaction [事务名]：提交事务，提交的数据变成数据库的永久部分； （3）rollback transaction [事务名]：回滚事务，撤销全部操作，回滚到事务开始时状态； （4）save transaction <事务名>：可选语句，在事务内设置保存点，可以使事物回滚到保存点，而不是回滚到事务的起点。

6 事物模式 【例12-1】 定义一个事务，向学生表中插入一条只包含学号、姓名和性别的记录。 use jxgl go select 次数=0,* from 学生 --检查当前表的内容 begin transaction insert into 学生(学号,姓名,性别)values(' ','司武长','男') save transaction label insert into 学生(学号,姓名,性别)values(' ','那佳佳','女') select 次数=1,* from 学生 --显示插入两条记录 rollback transaction label --回滚到事物保存点 select 次数=2,* from 学生 --显示第1次插入的记录被撤销了 rollback transaction select 次数=3,* from 学生 --显示第2次插入的记录被撤销了

7 事物模式 3．隐式事务 隐式事务是用set implicit transactions on不明显地定义事务开始，用commit或rollback语句明显地定义事务结束的事务。在隐式事务模式下，在当前事务提交或回滚后，SQL Server 自动开始下一个事务。主要事物模式设置语句如下： （1）设置隐性事务开始模式： set implicit_transactions on：启动隐性事务模式； set implicit_transactions off：关闭隐性事务模式。 （2）设置隐性事务回滚模式： set xact_abort on：当事务中任一条语句运行错误时，整个事务将终止并整体回滚； set xact_abort off：当事务中语句运行错误时，将终止本条语句且只回滚本条语句。

8 12.2 并发访问 数据库是允许多用户同时使用的共享资源，当多个事务并发访问（同时访问同一资源）时，若不加控制就可能会彼此冲突，破坏了数据的完整性和一致性，从而产生负面影响。 并发概述 并发异常 并发调度

9 并发概述 如果事物是顺序执行的，即一个事物完成之后，再开始另一个是事物，则称这种执行方式为串行执行，如图12-1a所示；如果数据库管理系统可以同时接收多个事物，并且这些事物在时间上可以重叠执行，则称这种执行方式为并行执行。在单CPU系统中，同一时间只能有一个事物占据CPU。各事物交叉地使用CPU，这种并发方式交叉并发。在多CPU系统中，多个事物可以同时占据CPU，这种并发方式称为同时并发，如图12-1b所示。在没有特殊说明的情况下，并发访问只考虑单CPU系统中的交叉并发的情况。

10 并发概述 a）串行执行 b）交叉并行执行 图12-1 多个事物的执行情况

11 并发异常 并发访问带来数据不一致性主要包括四类：丢失更新、不可重复读、脏读和幻读。 1．丢失更新（Lost Update） 丢失更新是指当两个或两个以上的事物同时读取同一数据并进行修改，其中一个事物提交的修改结果破坏了另一个事物的提交的修改结果。丢失更新有两类，分别介绍如下： （1）第一类丢失更新：一个事务在撤销时，把其它事务提交的更新数据覆盖，如表12-1所示。

12 12.2.2 并发异常----第一类丢失更新 时间 取款事务a 转账事务b t1 开始事务 t2 t3 查询账户余额为1000元 t4 t5
并发异常----第一类丢失更新 时间 取款事务a 转账事务b t1 开始事务 t2 t3 查询账户余额为1000元 t4 t5 汇入100元把余额改为1100元 t6 提交事务 t7 取出100元把余额改为900元 t8 撤销事务 t9 余额恢复为1000元（丢失更新）

13 并发异常----第二类丢失更新 （2）第二类丢失更新：一个事务在提交时，把其它事务提交的更新数据覆盖，如表12-2所示。 表12-2 第二类丢失更新 时间 转账事务a 取款事务b t1 开始事务 t2 t3 查询账户余额为1000元 t4 t5 取出100元把余额改为900元 t6 提交事务 t7 汇入100元 t8 t9 把余额改为1100元（丢失更新）

14 12.2.2 并发异常----2．脏读（Dirty Read）
脏读就是指一个事务读到另一事务尚未提交的更新数据（不正确的临时数据），如表12-3所示。 表12-3 脏读 时间 转账事务a 取款事务b t1 开始事务 t2 t3 查询账户余额为1000元 t4 取出500元把余额改为500元 t5 查询账户余额为500元 t6 撤销事务余额恢复为1000元 t7 汇入100元把余额改为600元 t8 提交事务

15 12.2.2 并发异常----不可重复读 3．不可重复读（NonRepeatable Read）
并发异常----不可重复读 3．不可重复读（NonRepeatable Read） 不可重复读是指一个事务中在两次读取同一数据行的过程中，由于另一个事务的修改，导致了第一个事物两次查询结果不一样，如表12-4所示。 表12-4 不可重复读 时间 取款事务a 转账事务b t1 开始事务 t2 t3 查询账户余额为1000元 t4 t5 取出100元把余额改为900元 t6 提交事务 t7 查询账户余额为900元（和t4读取的不一致）

16 并发异常----4．幻读 幻读是指一个事务在执行两次查询的过程中，由于另外一个事务插入或删除了数据行，导致第一个事务的第二次查询中发现新增或丢失数据行现象，如同幻觉一样，如表12-5所示。 表12-5 幻读 时间 统计金额事务a 转账事务b t1 开始事务 t2 t3 统计总存款数为1000元 t4 新增一个存款账户，存款为100元 t5 提交事务 t6 再次统计总存款数为1100元（幻象读）

17 并发异常 幻读和不可重复读的区别：幻读是指读到了其它事务已经提交的新增数据，而不可重复读是指读到了已经提交事务的更改数据；添加行级锁，锁定所操作的数据，可防止读取到更改数据，而添加表级锁，锁定整个表，则可以防止新增数据。

18 并发调度 并发事物中各事物的执行顺序和执行时机一方面取决于事物自身内部逻辑，另一方面也受到DBMS中事物调度机制的控制。并发访问时，必须采取合适的调度机制来安排各个事务动作流的执行顺序，以保证事务的ACID特性。 根据事务调度的方式，并发调度分为两种：串行调度和并行调度。 1．串行调度 若多个事务按完成顺序依次执行，则称为事物的串行调度。

19 并发调度 【例12-2】 有甲、乙两个售票窗，各卖出某一车次的硬座车票2张，卧铺车票1张。设该车次的初始硬座车票数为A=50，卧铺车票数为B=30，read（）表示读出数据，write（）表示写入数据。现将事务甲和事务乙串行执行，则有表12-6和表12-7所示的两种调度方法。

20 12.2.3 并发调度 时刻 事务甲 事务乙 t0 read（A）=50 t1 A=A-2 t2 write（A）=48 t3
并发调度 时刻 事务甲 事务乙 t0 read（A）=50 t1 A=A-2 t2 write（A）=48 t3 read（B）=30 t4 B=B-1 t5 write（B）=29 t6 read（A）=48 t7 t8 write（A）=46 t9 read（B）=46 read（B）=29 t11 t12 write（B）=28 注意：串行调度的结果总是正确的，但执行效率低。

21 并发调度 2．并行调度 若多个事务同时交叉（分时的方法）地并行进行，则称为事物的并行调度。 【例12-3】 有甲乙两个售票窗，各卖出某一车次的硬座车票2张，卧铺车票1张。设该车次的初始硬座车票数为A=50，卧铺车票数为B=30，read（）表示读出数据，write（）表示写入数据。现将事务甲和事务乙并行执行，则表12-8和表12-9列出两种调度方法中的。

22 12.2.3 并发调度 时刻 事务甲 事务乙 t0 read（A）=50 t1 A=A-2 t2 write（A）=48 t3
并发调度 时刻 事务甲 事务乙 t0 read（A）=50 t1 A=A-2 t2 write（A）=48 t3 read（A）=48 read（B）=30 t4 t5 write（A）=46 t6 t7 B=B-1 t8 write（B）=29 t9 read（B）=29 t11 t12 write（B）=28

23 并发调度 注意：并行调度中，一个事务的执行可能会受到其它事务的干扰，调度的结果不一定正确，并行调度事物可以有效提高数据库的性能，增加系统的吞吐量。 3．可串行化调度 如果一个多个事务的并行调度是正确的，且仅当其结果与按某一次序串行调度时的结果等价相同，则称这种调度策略为事务的可串行化（Serializable）调度。可串行化是并发事务正确性的判别准则，一个给定的并发调度，当且仅当它是可串行化时，才认为是正确的调度。

24 12.3 锁 锁是防止其他事务访问指定的资源、实现并发控制的一种主要手段，有利于确保多用户环境下的数据一致性。一个事务在对某个数据库对象（如表、记录等）操作之前，先向系统发出请求，封锁该对象，阻止其它事务更新此数据库对象，从而保证事务完整性和一致性。 锁的模式

25 锁的模式 一个事务对资源对象加什么样的锁是由事物所执行任务来灵活决定的。SQL Server 2005支持的锁模式有22种，常见的锁模式如表12-10所示。

26 12.3.1 锁的模式 表12-10 SQL Server 2005支持的锁模式
锁的模式 表 SQL Server 2005支持的锁模式 缩写 描述 s 允许其他用户读取但不能修改被锁定资源 x 防止别的进程修改或者读取被锁定资源的数据（除非该进程设定为未提交读隔离级别） u 防止其他进程获取更新锁或者排他锁；在搜索数据并修改时使用 is 表示该资源的一个组件被一个共享锁锁定住了。这类锁只能在表级或者分页级才能被获取 iu 表示该资源的一个组件被一个更新锁锁定住了。这类锁只能在表级或者分页级才能被获取 ix 表示该资源的一个组件被一个排他锁锁定住了。这类锁只能在表级或者分页级才能被获取 six 表示一个正持有共享锁的资源还有一个组件（一个分页或者一行记录）被一个排他锁锁定住 siu 表示一个正持有共享锁的资源还有一个组件（一个分页或者一行记录）被一个更新锁锁定住 uix 表示一个正持有更新锁的资源还有一个组件（一个分页或者一行记录）被一个排他锁锁定住 sch-s 表示一个使用该表的查询正在被编译 sch-m 表示表的结构正在被修改 bu 表示向表进行批量数据复制并指定了tablock 锁定提示时使用（手动或自动皆可）

27 12.3.1 锁的模式 根据锁定资源方式的不同，SQL Server 2005的锁分为两种类型：基本锁和专用锁。 1．基本锁
锁的模式 根据锁定资源方式的不同，SQL Server 2005的锁分为两种类型：基本锁和专用锁。 1．基本锁 基本锁有两种：共享锁（Share Locks）和排他锁（Exclusive Locks）。 （1）共享锁：又称为S锁或读锁，发生在查询数据时。如果事务T对数据对象R加上了S锁，则T只可以读取R，不可以修改R，同时允许其他事务继续加S锁，与T并行读取R，但不能修改R，直到T释放R上的S锁。换句话说，共享锁是非独占的，允许其他事物共享锁定，防止其他事物排他锁定。用户读取数据之后，立即释放共享锁。

28 锁的模式 注意：一般来说，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed级别，只在读取（select）的期间保持锁定，查询出数据后立即释放锁；如果隔离级别为 Repeatable read 或 Serializable 级别，直到事务结束才释放锁。另外，如果 select 语句中指定了HoldLock提示，则也要等到事务结束才释放锁。 （2）排他锁：又称为X锁或写锁，发生在增加、删除和更新数据时。如果事务T对数据对象R加上了X锁，则只允许T读写R，其他事务都不能再对R加任何锁，直到T释放R上的X锁。换句话说，排他锁是独占的，与其它事务的共享锁或排他锁都不兼容。用户更改数据总是通过排他锁来锁定并持续到事务结束后。

29 锁的模式 2．专用锁 专用锁主要有更新锁、意向锁、结构锁和批量更新锁。 （1）更新锁：又称为U锁，是一种介于共享锁和排他锁之间的中继锁。如果两个以上事物同时将共享锁升级为排他锁时，必然出现彼此等待对方释放共享锁，从而造成死锁。在修改数据事务开始时，如果直接申请更新锁，锁定可能要被修改的资源，就可以避免潜在的死锁。一次只有一个事务可以获得更新锁，若修改数据，则转换为排他锁，否则转换为共享锁。 （2）意向锁：表示SQL Server有在资源的低层获得共享锁或排他锁的意向。例如放置在表上的共享意向锁，表示事务打算在表中的页或行上加共享锁。意向锁可以提高性能，因为系统仅在表级上检查意向锁而无须检查下层。意向锁又分为：意向共享（IS）锁、意向排他（IX）锁和意向排他共享（SIX锁）。  意向共享锁：说明事务意图在它的低层资源上放置共享锁来读取数据。  意向排他锁：说明事务意图在它的低层资源上放置独占锁来修改数据。  意向排他共享锁：说明事务意图在它的顶层资源放置共享锁来读取数据，并意图在它的低层资源上放置排他锁，也称共享式独占锁。

30 锁的模式 （3）结构锁：用于保证有些进程需要结构保持一致时不会发生结构修改。结构锁分为架构修改锁（Sch-M）和架构稳定锁（Sch-S）。执行表（结构）定义语言操作时，SQL Server采用Sch-M锁；编译查询时，SQL Server采用Sch-S锁，Sch-S锁不阻塞任何事物锁。 （4）批量更新锁：批量复制数据并指定了tablock锁定提示时使用批量更新锁。

31 封锁协议 封锁协议 运用X锁和S锁对数据对象加锁时遵循的规则（何时申请X锁或S锁，持锁时间和何时释放），称为封锁协议(Locking Protocol)。封锁协议共分为三级，三级封锁协议分别在不同程度上解决了数据的不一致性问题，为并发操作的正确调度提供了一定保证，其中第三级封锁协议是最高级别。 1．一级封锁协议 事务T在更新数据对象之前，必须对其获准加X锁，并且直到事务T结束时才释放该锁。如果未获准加X锁，则该事务T进入等待状态，直到获准加X锁后该事务才继续执行。 一级协议可以防止丢失修改，并保证事务T是可恢复的。在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。如表12-11所示。

32 12.3.2 封锁协议 时刻 事务甲 事务乙 t0 获准Xlock(A) t1 Read(A)=50 t2 申请Xlock(A) t3
封锁协议 时刻 事务甲 事务乙 t0 获准Xlock(A) t1 Read(A)=50 t2 申请Xlock(A) t3 A=A-3 wait t4 Write(A)=47 t5 Unlock(A) t6 t7 Read(A)=47 t8 A=A-2 t9 Write(A)=45

33 封锁协议 2．二级封锁协议 二级封锁协议在一级封锁协议的基础上，加上事务T在读取数据对象R以前必须先对其加S锁，读完数据对象R后即可释放S锁。如果未获准加S锁，则该事务T进入等待状态，直到获准加X锁后该事务才继续执行。 二级封锁协议除了能防止丢失修改的问题之外，还能解决读“脏”数据的问题。如表12-12所示。在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。

34 12.3.2 封锁协议 表12-12 二级封锁协议与解决读“脏”数据 时刻 事务甲 事务乙 t0 获准Slock(A) t1
封锁协议 表 二级封锁协议与解决读“脏”数据 时刻 事务甲 事务乙 t0 获准Slock(A) t1 Read(A)=50 t2 A=A-3 t3 Write(A)=47 申请Xlock(A) t4 Rollback wait t5 Unlock(A) t6 获准Xlock(A) t7

35 封锁协议 3．三级封锁协议 三级封锁协议在二级封锁协议的基础上，再规定S锁必须在事务T结束后才能释放。如果未获准加S锁，则该事务T进入等待状态，直到获准加X锁后该事务才继续执行。 三级封锁协议除了能防止丢失修改和读“脏”数据的问题之外，还能解决不可重复读的问题。如表12-13所示。

36 12.3.2 封锁协议 时刻 事务甲 事务乙 t0 获准Xlock(A) t1 Read(A)=50 t2 A=A-3 t3
封锁协议 时刻 事务甲 事务乙 t0 获准Xlock(A) t1 Read(A)=50 t2 A=A-3 t3 Write(A)=47 申请Slock(A) t4 Rollback wait t5 Unlock(A) t6 获准Slock(A) t7

37 两段锁协议 为了保证并发调度的正确性，DBMS普遍采用两段锁协议来实现并发调度的可串行化。所谓两段锁协议是指将每个事务的执行严格分为两个阶段：加锁阶段（扩展阶段）和解锁阶段（收缩阶段）。遵守第三级封锁协议必然遵守两段锁协议。 加锁阶段：在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。在这阶段，事务可以申请加锁，但不能释放锁。 解锁阶段：当事务释放了一个锁以后，事务进入解锁阶段，在这阶段，事务只能解锁，不能再进行加锁。 两段锁协议是从加锁、解锁顺序（会影响事务的并发调度）的角度来描述。若并发执行的事物均遵守两段锁协议，则对这些事物的任何并发调度策略都是可串行化的。两段锁协议是并发调度可串行化的充分条件，但不是必要条件。在实际应用中也有一些事物并不遵守两段锁协议，但它们却可能是可串行化调度。例如，表12-14和表12-15都是可串行化调度，不过只有表12-14遵守两段协议，而表12-15不遵守两段协议 注意：一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行，因此一次封锁法遵守两段锁协议；但是两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁。

38 12.3.3 两段锁协议 时刻 事务T1 事务T2 t0 Slock A t1 Read A=50 t2 Xlock A t3 A=A-2
Write A=48 t5 Slock B Unlock A t6 Read B=30 t7 Xlock B t8 t9 B=B-1 Wait t10 Write B=29 t11 Unlock B t12 Read B=29 t13 t14 t15 Write B=28 t16 t17 t18 Read A=48 t19 t20 t21 Write A=46 t22 t23 两段锁协议

39 锁的粒度 加锁对并发访问的影响体现在锁的粒度上，锁的粒度是指锁的生效范围（封锁对象）。 SQL Server系统具有多粒度锁定，允许锁定不同层次的资源。为了使锁定成本减至最少，系统自动分析SQL语句请求，将资源锁定在适合任务的级别上，在锁的数目太多时，也会自动进行锁升级。如更新某一行，用行级锁；而更新所有行，则升级为表级锁。 根据封锁的资源不同，锁分为行、页、范围、表或数据库级锁，如表12-16所示。

40 12.3.4 锁的粒度 资源 描述 数据行（RID） 用于锁定堆中的单个行的行标识符 索引行（Key）
锁的粒度 资源 描述 数据行（RID） 用于锁定堆中的单个行的行标识符 索引行（Key） 索引中用于保护可序列化事务中的键范围的行锁 页（Page） 一个数据页或索引页，其大小8KB 范围（Extent） 一组连续的8个页组成，如数据页或索引页 HOBT 堆或B树。保护索引或没有聚集索引的表中数据页堆的锁 表（Table） 整个表，包括所有数据和索引的整个表 文件（File） 数据库 应用程序（application） 应用程序专用的资源 元数据（Metadata） 元数据锁 分配单元（Allocation_Unit） 分配单元 数据库（Database） 整个数据库

41 锁的粒度 封锁粒度与系统的并发度和并发控制的开销密切相关。直观地看，封锁的粒度越大，数据库所能够封锁的数据单元就越少，并发度就越小，系统开销也越小；反之，封锁的粒度越小，并发度较高，但系统开销也就越大。 注意： （1）行级锁是一种最优锁，因为行级锁不可能出现占用数据而不使用数据的现象。 （2）锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁。

42 查看锁的信息 在SQL Server 2005中，查看锁的信息有多种方式，既可以通过SSMS查看锁信息，也可以通过存储过程查看信息。另外，通过SQL Profiler工具，还可以图形化的方式显示与分析死锁（Deadlock）事件，有关操作请参阅相关资料。 1．锁的兼容性 在一个事务已经锁定某个对象的情况下，另一个事务也请求锁定该对象，则会出现锁定兼容与冲突。当两种锁定方式兼容时，允许第二个事物的锁定请求。反之，不允许第二个事物的锁定请求，直至等待第一个事务释放其现有的不兼容锁定为止。 资源锁模式有一个兼容性矩阵，列出同一资源上可获取的兼容性的锁，如表12-17所示。

43 查看锁的信息

44 查看锁的信息 注意： （1）意向排他（IX）锁与意向排他（IX）锁模式兼容，因为IX锁只打算更新一些行而不是所有行，还允许其它事务读取或更新部分行，只要这些行不是当前事务所更新的行即可。 （2）架构稳定性（Sch-S）锁与除了架构修改（Sch-M）锁模式之外的所有锁模式相兼容。 （3）架构修改（Sch-M）锁与所有锁模式都不兼容。 （4）批量更新（BU）锁只与架构稳定（Sch-S）锁及其它BU锁相兼容。

45 查看锁的信息 【例12-4】 共享锁和更新锁兼容示例。 A事物： begin tran select 时间1=getdate(),* from 学生 with(updlock) where 学号=' ' go waitfor delay '00:00:06' --暂停6秒 update 学生 set 总分=总分-10 where 学号=' ' updlock升级为排他锁 waitfor delay '00:00:06' rollback tran select 时间2=getdate(),* from 学生 where 学号=' ' B事物： update 学生 set 总分=总分+10 where 学号=' ' select 时间2=getdate(), * from 学生 where 学号=' ' commit tran 先执行A事务，然后立即执行B事务，A、B事物最终运行结果如图12-1和图12-2所示。 注意：共享锁和更新锁可以同时在同一个资源上。同一时间不能在同一资源上有两个更新锁。一个事物只能有一个更新锁获此资格。

46 （2）单击释放后，打开“活动监视器”对话框的“进程状态”界面，如图12-2所示。
查看锁的信息 2．通过SSMS查看锁的信息 （1）启动SSMS，在“对象资源管理器”窗格中依次展开SHUJU（服务器实例）→“管理”，右击“活动监视器”，弹出快捷菜单，选择“查看进程”命令，如图12-1所示。 （2）单击释放后，打开“活动监视器”对话框的“进程状态”界面，如图12-2所示。 图12-4 “活动监视器”对话框的“进程状态”界面 （3）单击“按进程分类的锁”或“按对象分类的锁”选项卡，可以看到锁的具体信息。

47 12.3.5 查看锁的信息 3．使用存储过程查看锁的信息 使用系统存储过程sp_lock也可以列出当前的锁，其语法格式如下：
查看锁的信息 3．使用存储过程查看锁的信息 使用系统存储过程sp_lock也可以列出当前的锁，其语法格式如下： 格式：sp_lock [spid] 说明：spid是int类型的进程ID号，如果不指定spid，则显示所有进程的锁。 【例12-5】 显示编号为53的锁的信息。 use jxgl exec sp_lock 53

48 锁定提示 封锁及其升级是由系统动态管理的，然而，有时为了应用程序正确运行和保持数据的一致性，必须人为地对SQL语句进行特别指定（锁定提示，手工加锁），其语法格式如下： 格式：select * from <表名> whith(锁) where <条件> 说明：锁定提示优先于事务隔离级别，常见的锁定提示有三种类型： （1）类型1 ①Read unCommitted：不发出锁。 ②Read Committed：发出共享锁，保持到读取结束。 ③Repeatableread：发出共享锁，保持到事务结束。 ④Serializable：发出共享锁，保持到事务结束。

49 锁定提示 （2）类型2 ①Nolock：不发出锁，可读到“脏”数据，这个选项仅仅应用于select语句。 ②Holdlock：发出共享锁，持续到事务结束释放，等同于Serializable在表级上的应用。 ③Xlock：发出排他锁，持续到事务结束释放（排他锁与共享锁不兼容）。 ④Updlock：发出更新锁，持续到这个语句或整个事务结束释放，允许别的事物读数据（更新锁与共享锁兼容），不允许更新和删除。 ⑤Readpast：发出共享锁，但跳过锁定行，它不会被阻塞。适用条件：提交读的隔离级别，行级锁，Select语句中。 （3）类型3 ①Rowlock：使用行级锁，而不使用粒度更粗的页级锁和表级锁。 ②Paglock：在使用一个表锁的地方使用多个页锁。 ③Tablock：在表级上发出共享锁，持续到语句结束释放。xlock tablock等价于Tablockx。 ④Tablockx：在表级上发出排他锁，持续到语句或事务结束，阻止其他事务读或更新数据。

50 锁定提示 【例12-6】 系统自动加排他锁的情况。 A事物： begin tran update 学生 set 姓名='席同锁' where 学号=' ' waitfor delay '00:00:10' --等待10秒 commit tran B事物： select * from 学生 where 学号=' ' --等待A事物结束才能执行 执行A事物后，立即执行B事物，则B事物（select 语句）必须等待A事物（执行update语句时，系统自动加排他锁）执行完毕才能执行，即B事物要等待10秒才能显示查询结果。 【例12-7】 人为加holdlock锁的情况（比较tablock锁）。 select 时间0=getdate(),* from 学生 with (holdlock) --人为加holdlock锁 where 学号=' ' go waitfor delay '00:00:10' --延迟10秒后结束事物 select 时间1=getdate(),* from 学生 where 学号=' '--不等待，立即执行 update 学生 set 姓名='任伟锁' where 学号=' ' --伴随A事物延迟 select 时间2=getdate(), * from 学生 where 学号=' ' 执行A事物后，立即执行B事物，A、B事物最终运行结果如图12-5和12-6所示。

51 锁定提示 注意：B事物连接中的select语句可以立即执行，而update语句必须等待A事物连接中的共享锁结束后才能执行，即B事物连接中的update语句要等待10秒才能执行。 【例12-8】 人为加tablock锁的情况（比较holdlock锁）。 A事物： begin tran select 时间0=getdate(),* from 学生 with (tablock) --人为加tablock锁 where 学号=' go waitfor delay '00:00:10' --延迟10秒后结束事物 commit tran B事物： select 时间1=getdate(),* from 学生 where 学号=' '--不等待，立即执行 update 学生 set 姓名='龚巷锁' where 学号=' ' --不等待，立即执行 select 时间2=getdate(), * from 学生 where 学号=' ' 执行A事物后，立即执行B事物，A、B事物最终运行结果如图12-7和12-8所示。

52 活锁与死锁 封锁可有效解决并行操作的不一致性问题，但也因此而产生新的问题：活锁和死锁。 1．活锁（Livelock） 当某个事务请求对某一数据的排他性封锁时，由于其他事务对该数据的操作而使这个事务处于永久等待状态，这种状态称为活锁。 【例12-9】 如果事务T1封锁了数据R，事务T2又请求封锁R，于是T2等待。T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待。然后T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求，……，T2有可能永远等待，从而发生了活锁。如表12-18所示。

53 12.3.7 活锁与死锁 时刻 事务T1 事务T2 事务T3 事务T4 t0 lock(R) t1 … 申请lock(A) t2 wait
活锁与死锁 时刻 事务T1 事务T2 事务T3 事务T4 t0 lock(R) t1 … 申请lock(A) t2 wait 申请lock(R) t3 unlock(R) t4 获准lock(R) t5 t6 t7

54 活锁与死锁 预防活锁的简单办法是采用“先来先服务”的策略。当多个事务请求封锁同一数据对象时，封锁子系统按请求封锁的先后次序对事物排队，数据对象上的锁一旦释放，就按顺序批准申请队列中的第一个事务获得锁。

55 活锁与死锁 2．死锁（Deadlock） 在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。 死锁是所有事务都被无限长延迟的极端阻塞情况，导致死锁出现的情况主要有两种： （1）两个事务同时锁定两个单独的对象，又彼此要求封锁对方的锁定对象。 （2）长时间执行不能控制处理顺序的并发事物，比如复杂查询中连接查询。

56 活锁与死锁 【例12-10】 如果事务T1封锁了数据R1，T2封锁了数据R2，然后T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁。接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁。这样就出现了相互等待状态而不能结束，就形成了死锁的局面。如表12-19所示。

57 12.3.7 活锁与死锁 时刻 事务T1 事务T2 t0 lock(R1) t1 … lock(R2) t2 申请lock(R2) t3
活锁与死锁 时刻 事务T1 事务T2 t0 lock(R1) t1 … lock(R2) t2 申请lock(R2) t3 wait 申请lock(R1) t4 t5 t6 t7

58 活锁与死锁 防止死锁的发生其实就是要破坏产生死锁的条件。预防死锁通常有两种方法： （1）一次加锁法 要求每个事务一次就将要使用的数据全部加锁，否则就不能继续执行下去。其存在以下缺点：  事务耗时锁定过多数据，延迟其他事务及时访问，降低了系统的并发程度；  无法预知事务需要加锁的数据，被迫扩大加锁范围，降低了系统的并发程度； （2）顺序加锁法 预先规定一个访问数据的加锁顺序，要求所有事务都遵照执行这个加锁顺序。其存在以下缺点：  需要加锁的数据过多，并且不断变化，维护加锁顺序很困难，代价非常大；  事物封锁请求是随着事物执行而动态决定，无法预知事务访问的数据，难以统一要求事务遵照固定的加锁顺序； 在操作系统中广为使用的预防死锁发生的策略并不适合数据库系统的特点。在DBMS中普遍采用的是诊断并解除死锁的办法，即允许发生死锁，采用一定手段定期诊断系统中有无死锁，如有就设法解除之。 诊断死锁并解除死锁的方法有超时法和事物等待图法。 （1）超时法：如果一个事务的等待时间超过规定时间，就认为发生了死锁。这个实现简单，但不足也很明显。一是误判死锁，事物因为其他原因使等待时间超过时限被误判为死锁；二是时限设置太长，死锁发生后不可能及时发现。 （2）事务等待图法：事务等待图是一个有向图G=(T, U)，T为结点的集合，每个结点表示正在运行的事务；U为边的集合，每条边表示事务等待的情况。若事务T1等待事务T2，则T1，T2之间划一条有向边，表示从 T1 指向 T2。 事物等待图动态地反映了所有事物的等待情况。并发控制子系统周期性地生成事物等待图，并进行检测。如果发现图中存在回路，则表示系统中出现了死锁。当搜索检测到锁定请求环时，SQL Server 系统通常选择一个处理死锁代价最小的事物，将其撤销，释放此事物持有的所有锁，使其他事物得以继续下去。当然，系统回滚该事务以保持数据一致性，并向该进程发出1205号错误信息。

59 12.3.7 活锁与死锁 【例12-11】 死锁示例。 A事物： begin tran
活锁与死锁 【例12-11】 死锁示例。 A事物： begin tran select 时间1=getdate(),* from 学生 with(holdlock) where 学号=' ' go waitfor delay '00:00:06' update 学生 set 总分=总分-10 where 学号=' ' rollback tran select 时间2=getdate(),* from 学生 where 学号=' ' B事物： select 时间2=getdate(), * from 学生 where 学号=' ' commit tran 执行A事务后，立即执行B事务，A、B事务最终运行结果如图12-9和图12-10所示。

60 活锁与死锁 注意：当T1执行select语句后，其共享锁需升级为排他锁才能继续执行update语句，升级之前，需要T2释放其共享锁，但共享锁holdlock只有在事务结束后才释放，所以T2不释放共享锁而导致T1等待。同理，T1不释放共享锁而导致T2等待，这样就产生了死锁。

61 12.4 事务隔离级别 很多情况下，定义正确的隔离级别并不是简单的决定。作为一种通用的规则，使用较低的隔离级别（已提交读）比使用较高的隔离级别（可序列化）持有共享锁的时间更短，更有利于减少锁竞争，避免死锁，同时依然可以为事务提供它所需的并发性能。 隔离级别概述

62 隔离级别概述 事务隔离级别是控制一个事物与其它事物隔离程度，它是系统内置的一组加锁策略。对于编程人员来说，不是通过手工设置来控制锁的使用，而是通过设置事务的隔离级别来控制锁的使用，从而实现并发法访问控制。 1．并发控制模型 为避免并发访问可能产生的不利影响，SQL Server 2005 提供了两种并发控制机制：悲观并发控制模式和乐观并发控制模式。 （1）悲观并发控制模式 假定系统中存在足够多的数据修改操作，以致于任何确定的读操作都可能受到其他用户写操作的影响。在事务执行过程中，悲观并发控制将根据需要使用锁锁定资源。在悲观并发环境中，读（reader）和写（writer）之间是冲突的、互相阻塞的。 （2）乐观并发控制模式 假定系统中存在非常少的数据修改操作，以致于任何单独的事务都不太可能影响其他事务正在修改的数据。乐观并发控制采用行版本查看事物，或查询当前进程一开始读取时的数据状态，并且不受当前进程或其它进程对数据进行修改的影响。在乐观并发环境中，读写之间不会互相阻塞，但是写者之间会发生阻塞。

63 隔离级别概述 注意： （1）乐观锁的缺点是发生冲突时，SQL Server会抛出异常给应用程序处理，而应用程序一般会要求重新执行事务，因而影响系统的性能和增加处理的复杂性。 （2）快照和已提交读快照的区别：已提交读快照只是在更新的时候对快照和原始数据进行版本比较；而快照则不仅在更新时比较，而且在多次读事务的时候也比较读取数据的版本。 2．事物隔离级别 设置事物隔离级别的命令是set transaction isolation level，其语法格式如下： 格式：set transaction isolation level { read committed | read uncommitted | repeatable read | serializable | snapshot } 说明：系统按照设置的隔离级别自动控制并发事物处理；

64 悲观并发模型 SQL Server 2005 支持 ANSI/ISO SQL 92 标准定义的4个等级的事务隔离级别，不同事物隔离级别能够解决的数据并发问题的能力是不同的，如表12-20所示。 隔离级别越高，越能保证数据的完整性和一致性，但也意味着并发性能的降低。通常情况下，隔离级别设为Read Committed，既能避免脏读取，又保持较好的并发性能。 隔离级别 脏读 不可重复读 幻象读 第一类丢失更新 第二类丢失更新 未提交读 允许 不允许 已提交读 可重复读 可串行化读

65 悲观并发模型 1．未提交读 未提交读（Read Uncommitted）：这是最低的事务隔离级别，仅仅保证读取过程中不会读取非法数据，读事务不会阻塞读事务和写事务，因而读事务可以读取写事务尚未提交的数据，写事务也不会阻塞读事务，只会阻塞写事务而已。 【例12-12】 使用未提交隔离级别的脏读示例。 A事物： use jxgl go set transaction isolation level read uncommitted begin tran update 学生set 总分=总分+5 where 籍贯='山东' select 次数=1,* from 学生 where 籍贯='山东' waitfor delay '00:00:10' --暂停10秒 rollback transaction --回滚事务 select 次数=2,* from 学生 where 籍贯='山东' B事物： begin transaction select * from 学生 where 籍贯='山东' commit transaction 执行A事务后，立即运行B事务，A、B事务最终运行结果如图12-11和图12-12所示。

66 悲观并发模型 2．已提交读 已提交读（Read Committed）：采用此种隔离界别的时候，读事务不会阻塞读事务和写事务，不过写事务会阻塞读事务和写事务，因而只解决了脏读问题，没有解决不可重复读和幻读问题。此选项是SQL Server 2005默认的隔离级。 【例12-13】 使用已提交隔离级别的不可重复读示例。 A事物： use jxgl go set tran isolation level read committed begin transaction select 次数=1,* from 学生where 籍贯='山东' waitfor delay '00:00:10' select 次数=2,* from 学生where 籍贯='山东' select 次数=3,* from 学生where 籍贯='山东' commit transaction B事物： update 学生 set 总分=总分-5 where 籍贯='山东' select * from 学生 where 籍贯='山东' 执行A事务后，立即运行B事务，A、B事务最终运行结果如图12-13和图12-14所示。

67 悲观并发模型 3．可重复读 可重复读（Repeatable Read）：采用此种隔离级别，读事务只阻塞写事务中的update和delete操作，不阻塞读事务和写事物中的insert操作，因而只解决了脏读和不可重复读的问题，还是没有解决幻读问题。此选项会影响系统的效能，非必要情况最好不用此隔离级； 【例12-14】 使用可重复读隔离级别的幻读示例。 A事物： use jxgl go set tran isolation level repeatable read begin transaction select * from 学生where 籍贯='山东' waitfor delay '00:00:10' commit transaction set tran isolation level read committed B事物： insert into 学生(学号,姓名,性别,总分,籍贯) values(' ','柯崇福','男',550,'山东') 执行A事务后，立即运行B事务，A事务最终运行结果如图12-15所示。

68 悲观并发模型 4．可串行化读 可串行化读（Serializable）：此种隔离级别是最严格的隔离级别，和X锁类似，要求事物序列化执行，读事务阻塞了写事务任何操作，解决了并发异常问题（脏读、不可重复读、幻读）。此选项极大影响系统的性能，如非必要，应该避免设置此隔离级； 【例12-15】 使用可串行化读隔离级别。 A事物： use jxgl go set tran isolation level serializable begin transaction select * from 学生 where 籍贯='山西' waitfor delay '00:00:30' commit transaction set tran isolation level read committed B事物： insert into 学生(学号,姓名,性别,总分,籍贯) values(' ','徐列华','男',550,'山西') update 学生set 性别='女' where 学号=' ' 执行A事务后，立即运行B事务，A、B事务最终运行结果如图12-16和图12-17所示。

69 12.4.3 乐观并发模型 隔离级别 脏读 不可重复读 幻影读 并发控制模型 已提交读（快照） 不允许 允许 乐观 快照
乐观并发模型 行版本控制允许一个事务在排他锁定数据后读取数据的最后提交版本，读取数据时不再请求共享锁，而且永远不会与修改进程的数据发生冲突，如果请求的行被锁定（如正被更新），SQL Server 2005系统会从行版本存储区返回最早的关于该行的记录。由于不必等待到锁释放就可进行读操作，可以降低读写操作之间发生的死锁几率，因此查询性能得以大大增强。 SQL Server 2005支持两种基于行版本的事务隔离级别：已提交读快照和快照。这两种隔离级别如表12-21所示。 隔离级别 脏读 不可重复读 幻影读 并发控制模型 已提交读（快照） 不允许 允许 乐观 快照

70 乐观并发模型 1．已提交读快照 已提交读快照（Read_Committed_Snapshot）：它是已提交读隔离级别的一种实现方法。和已提交读级别相比较：相同的是两者只能避免脏读，也无更新冲突检测，不同的是，已提交读快照读数据时无须共享锁，因而读写之间不会阻塞。

71 乐观并发模型 【例12-16】 使用已提交读（快照）隔离级别。 A事物： alter database jxgl set allow_snapshot_isolation on use jxgl go alter database jxgl set read_committed_snapshot on set transaction isolation level read committed begin transaction select * from 学生 where 籍贯='山东' waitfor delay '00:00:10' commit transaction B事物： update 学生 set 总分=总分-5 where 籍贯='山东' 执行A事务后，立即运行B事务，A、B事务最终运行结果如图12-18和图12-19所示。

72 乐观并发模型 2．快照 快照（Snapshot）：所有读取操作不再受其它锁定影响，读取的数据是读取事物开始前逻辑确定并符合一致性的数据行版本。快照可以避免脏读、丢失更新、不可重复读、幻读、而且有更新冲突检测的特点。

73 乐观并发模型 【例12-17】 使用快照隔离级别。 A事物： use jxgl go alter database jxgl set allow_snapshot_isolation on set tran isolation level snapshot begin transaction select * from 学生 where 籍贯='山东' waitfor delay '00:00:10' commit transaction alter database jxgl set allow_snapshot_isolation off B事物： update 学生set 总分=总分+5 where 籍贯='山东' select * from 学生where 籍贯='山东' 执行A事务后，立即运行B事务，A、B事务最终运行结果如图12-20和图12-21所示。

74 本章小结 事务和锁是两个紧密联系的概念。对于多用户系统来说，事务使用锁来防止其他用户修改另外一个还没有完成的事务中的数据，解决了数据库的并发性问题。SQL Server 2005具有多粒度锁定，允许一个事务锁定不同类型的资源。为了使锁定的成本减至最少，SQL Server自动将资源对象锁定在适合任务的级别上。