SQL Server数据库应用与开发：第09章 事务和锁

1、SQL Server 2005 数据库应用与开发第09章 事务和锁内容提要事务是由一系列的数据操作命令组成，是数据库应用程序的基本逻辑单元。SQL Server 2005在对数据库进行操作时，通过事务来保证数据的一致性和完整性。用户访问数据库时，经常出现并发的情况。数据库系统的并发处理能力是衡量其性能的重要标志之一。数据库系统通过适当的并发控制机制协调并发操作，保证数据的一致性。在SQL Server 2005中，以事务为基本操作单位，使用锁来实现并发控制。第09章 事务和锁9.1 事务概述9.2 管理事务9.3 管理并发数据访问9.4 管理锁9.5小结9.1 事务概述在计算机系统设计过程中，

2、与一个商业事务相关的数据必须保证可靠性、一致性和完整性，以符合实际的商业过程。在SQL Server 2005中，通常由事务来完成相关操作，以确保多个数据的修改作为一个单元来处理。事务（Transaction）是单个的工作单元。如果某一事务执行成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被还原。 9.1 事务概述 9.1.1 事务的特点定义事务必须符合如下的典型特征。原子性（Atomicity）：事务包含的一系列数据操作是是一个整体。执行部分操作则数据会回滚到原来的状态。一致性(Consistency) ：事务执行

3、完成后，将数据库从一个一致状态转变到另一个一致状态，事务不能违背定义在数据库中的任何完整性检查。隔离性(Isolation) ：一一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰持久性(Durability)：要求一旦事务提交，那么对数据库所做的修改将是持久的，无论发生何种机器和系统故障，都不应该对其有任何影响。9.1 事务概述9.1.2事务的分类任何对数据的修改都是在事务环境中进行的。按照事务定义的方式可以将事务分为系统定义事务和用户定义事务。SQL Server 2005支持3种事务模式分别对应上述两类事务：自动提交事务、显式事务和隐式事务。（1）

4、自动提交事务。 SQL Server 2005将一切操作作为事务处理，它不会在事务以外更改数据。如果没有用户定义事务，SQL Server会自己定义事务，称为自动提交事务。每条单独的语句都是一个事务。（2）显式事务。显式事务是指显式定义了启动和结束的事务。在实际应用中，大多数的事务是由用户来定义的。事务结束分为提交（COMMIT）和回滚（ROLLBACK）两种状态。9.1 事务概述9.1.2事务的分类（3）隐式事务。 在隐式事务中，SQL Server在没有事务定义的情况下会开始一个事务，但不会像在自动提交模式中那样自动执行COMMIT或ROLLBACK语句，事务必须显式结束。Transact

5、-SQL 脚本使用SET IMPLICIT_TRANSACTIONS ON / OFF语句可以启动/关闭隐式事务模式。（4）分布式事务。一个比较复杂的环境，可能有多台服务器，那么要保证在多服务器环境中事务的完整性和一致性，就必须定义一个分布式事务。在分布式事务中，所有的操作都可以涉及对多个服务器的操作，当这些操作都成功时，那么所有这些操作都提交到相应服务器的数据库中，如果这些操作中有一条操作失败，那么这个分布式事务中的全部操作都被取消。9.1 事务概述9.1.2事务的分类跨越两个或多个数据库的单个数据库引擎实例中的事务实际上也是分布式事务。该实例对分布式事务进行内部管理；对于用户而言，其操作就

6、像本地事务一样。对于应用程序而言，分布式提交必须由事务管理器管理，以尽量避免出现因网络故障而导致事务由某些资源管理器成功提交，另一些资源管理器回滚的情况。通过准备阶段和提交阶段管理提交进程可避免这种情况，这称为两阶段提交。9.2 管理事务 事务的基本操作包括启动、保存、提交或回滚等。 9.2.1 启动事务1显式事务的定义显式事务需要明确定义事务的启动。显式事务的定义格式如下：BEGIN TRAN | TRANSACTION transaction_name | tran_name_variable WITH MARK description 例9.1 定义一个事务，将teaching数据库的s

7、core表中所有选了c05109号课程的学生的分数加5分，并提交该事务。程序代码如下：USE teachingGODECLARE TranName VARCHAR(20);SELECT TranName = Add_Score;BEGIN TRAN TranName; UPDATE score SET final=final+5 WHERE courseno=c05109;COMMIT TRAN TranName;GO例9.2 定义一个事务，将teaching数据库的score表中所有选了c05109号课程的学生的分数减5分，并提交该事务。程序代码如下：USE teachingGOBEGIN

8、TRAN Subtract_Score WITH MARK NSubtract score of c05109; GO UPDATE score SET final=final-5 WHERE courseno=c05109;COMMIT TRAN Subtract_Score;GO9.2 管理事务9.2.1 启动事务2隐式事务的定义默认情况下，隐式事务是关闭的。使用隐式事务需先将事务模式设置为隐式事务模式。不再使用隐式事务时，要退出该模式。 SET IMPLICIT_TRANSACTIONS ON | OFF需要注意的是：在使用隐式事务时，不要忘记结束事务（提交或回滚）。由于不需要显式的定义

9、事务的开始，事务的结束很容易被忘记，导致失误长期运行；在连接关闭时产生不必要的回滚；或者造成其他连接的阻塞问题。9.2 管理事务9.2.1 启动事务2隐式事务的定义例9.3 分别使用显式事务和隐式事务向表course中插入两条记录。程序代码如下：-first partUSE teachingGOSET NOCOUNT ON;SET IMPLICIT_TRANSACTIONS OFF;GOPRINT NTran count at start = + CAST(TRANCOUNT AS NVARCHAR(10);BEGIN TRANSACTION INSERT INTO course VALUES

10、(c05141,WIN程序设计,选修,64,4.0); PRINT NTran count at 1st = + CAST(TRANCOUNT AS NVARCHAR(10); INSERT INTO course VALUES(c05142,WEB程序设计,选修,64,4.0); PRINT NTran count at 2nd = + CAST(TRANCOUNT AS NVARCHAR(10);COMMIT TRANSACTIONGO- second partPRINT NSetting IMPLICIT_TRANSACTIONS ON.;SET IMPLICIT_TRANSACTION

11、S ON;PRINT NUse implicit transactions.;- No BEGIN TRAN needed here.INSERT INTO course VALUES(c05151,管理信息系统,选修,48,3.0);PRINT NTran count in 1st implicit transaction = + CAST(TRANCOUNT AS NVARCHAR(10);INSERT INTO course VALUES(c05152,电子商务,选修,48,3.0);PRINT NTran count in 2nd implicit transaction = + CA

12、ST(TRANCOUNT AS NVARCHAR(10);GOCOMMIT TRANSACTION;PRINT NTran count after implicit transaction = + CAST(TRANCOUNT AS NVARCHAR(10);SET IMPLICIT_TRANSACTIONS OFF;GO9.2 管理事务9.2.2 保存事务为了提高事务执行的效率，或者进行程序的调试等，可以在事务的某一点处设置一个标记（保存点），这样当使用回滚语句时，可以不用回滚到事务的起始位置，而是回滚到标记所在的位置即保存点。保存点设置及使用格式：SAVE TRAN | TRANSACTI

13、ON savepoint_name | savepoint_variableROLLBACK TRANSACTION savepoint_name | savepoint_variable例9.4定义一个事务，向course表中添加一条记录，并设置保存点。然后再删除该记录，并回滚到事务的保存点，提交事务。程序代码如下：USE teachingGOBEGIN TRAN INSERT INTO course VALUES(c05139,统一建模语言UML,选修,48,3.0); SAVE TRAN savepoint; DELETE FROM course WHERE courseno=c0513

14、9; ROLLBACK TRAN savepoint;COMMIT TRANGO9.2 管理事务9.2.3 提交事务提交事务标志着一个执行成功的隐式事务或显式事务的结束。事务提交后，自事务开始以来所执行的所有数据修改被持久化，事务占用的资源被释放。COMMIT TRAN | TRANSACTIONtransaction_name | tran_name_variable9.2 管理事务9.2.4 回滚事务回滚事务是指清除自事务的起点或到某个保存点所做的所有数据修改。释放由事务控制的资源。ROLLBACK TRAN | TRANSACTIONtransaction_name | tran_nam

15、e_variable | savepoint_name | savepoint_variable 9.2 管理事务9.2.5 自动提交事务SQL Server 2005在启动显式事务，或者隐性事务模式设置为打开之前，都将以自动提交模式进行操作。即：在未启动显式事务，或者回滚和关闭启动显式事务时，SQL Server为自动提交模式。在关闭隐性事务模式设置时，SQL Server 为自动提交模式。在自动提交模式下，发生回滚的操作内容取决于遇到的错误的类型。当遇到运行时错误时，仅回滚发生错误的语句；当遇到的错误时编译错误时，回滚所有的语句。例9.5比较自动提交事务发生运行时错误和编译时错误的处理情况

16、。程序代码如下：-发生编译错误的事务示例：USE teachingGOINSERT INTO course VALUES(c11111,测试课程,必修,48,3.0);INSERT INTO course VALUES(c22222,测试课程,必修,48,3.0);-语法错误INSERT INTO course VALUSE (c33333,测试课程,必修,48,3.0); SELECT * FROM course;GO-发生运行时错误的事务示例：USE teachingGOINSERT INTO course VALUES(c11111,测试课程,必修,48,3.0);INSERT INTO

17、 course VALUES(c22222,测试课程,必修,48,3.0);-重复键INSERT INTO course VALUES(c11111,测试课程,必修,48,3.0); SELECT * FROM course;GO9.2 管理事务9.2.6 事务嵌套可以在显式事务中再定义事务，称为嵌套事务。SQL Server 2005支持嵌套事务最重要的原因为了允许在存储过程中使用事务而不必顾及这个事务本身是否在另一个事务中被调用的。下面对于嵌套事务进行如下说明：SQL Server 2005数据库引擎忽略内部事务的提交。根据最外部事务结束时采取的操作，将提交或者回滚内部事务。如果提交外部事

18、务，也将提交内部嵌套事务；如果回滚外部事务，也将回滚所有内部事务。对COMMIT TRANSACTION的每个调用都必须用于事务最后执行的语句。如果嵌套BEGIN TRANSACTION语句，那么COMMIT语句只应用于最后一个嵌套事务，也就是在内部的嵌套事务。9.2 管理事务9.2.6 事务嵌套下面对于嵌套事务进行如下说明：ROLLBACK TRANSACTION语句的transaction_name transaction_name只能引用外部事务的事务名称。如果在一组嵌套事务的任意级别执行使用外部事务名称的ROLLBACK TRANSACTION transaction_name语句，那

19、么所有嵌套事务都将回滚。TRANCOUNT函数可以记录当前事务的嵌套级别。每个BEGIN TRANSACTION语句使TRANCOUNT增加1。每个COMMIT TRANSACTION语句使TRANCOUNT减去1。如果TRANCOUNT等于0，则表明当前操作不在事务中。默认情况下，隐式事务是不能嵌套的。9.2 管理事务例9.6 嵌套事务提交后，外部事务发生回滚。程序代码如下：USE teachingGOBEGIN TRAN PRINT NAfter 1st BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); BEGIN TRAN PRINT NAfte

20、r 2nd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); BEGIN TRAN PRINT NAfter 3rd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); UPDATE course SET cname=SQL Server教程,period=64,credit=4.0 WHERE courseno=c22222; COMMIT TRAN; PRINT After 1st COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); ROLLBACK TRAN; PR

21、INT NAfter ROLLBACK TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); SELECT * FROM course WHERE courseno=c22222;GO9.2 管理事务例9.7使用TRANCOUNT函数查看事务的嵌套级别。程序代码如下：PRINT NTrancount before transaction: + CAST(TRANCOUNT As NVARCHAR(10);BEGIN TRAN PRINT NAfter 1st BEGIN TRAN: + CAST(TRANCOUNT As NVARCHAR(10); BEGIN TRA

22、N PRINT NAfter 2nd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); COMMIT TRAN PRINT NAfter 1st COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10);COMMIT TRANPRINT NAfter 2nd COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); GO9.3 管理并发数据访问并发数据访问是指多个用户能够同时时刻访问某些数据。当数据库引擎所支持的并发操作数较大时，数据库并发程序就会增多。控制多个用户如何同时访问和更改

23、共享数据而不会彼此冲突称为并发控制。在SQL Server 2005中，并发控制是通过用锁来实现的。9.3 管理并发数据访问9.3.1 并发的影响多个用户访问同一个数据资源时，如果数据存储系统没有并发控制，就会出现并发问题，比如修改数据的用户会影响同时读取或修改相同数据的其他用户。下面列出了使用SQL Server时可能出现的一些并发问题：（1）更新丢失。当两个或多个事务选择同一行，然后根据最初选定的值更新该行时，就会出现更新丢失的问题。每个事务都不知道其他事务的存在。最后的更新将覆盖其他事务所做的更新，从而导致数据丢失。9.3 管理并发数据访问9.3.1 并发的影响（2）不可重复读当一个事务

24、多次访问同一行且每次读取不同数据时，会出现不可重复读问题。因为其他事务可能正在更新该事务正在读取的数据。（3）幻读。当对某行执行插入或删除操作，而该行属于某事务正在读取的行的范围时，就会出现幻读问题。由于其他事务的删除操作，使事务第一次读取行范围时存在的行在后续读取时已不存在。与此类似，由于其他事务的插入操作，后续读取显示原来读取时并不存在的行。（4）脏读，即读出的是不正确的临时数据。9.3 管理并发数据访问9.3.2 并发控制的类型计算机系统对并发事务遵循可串行化（serializable）的调度策略，即几个并行事务执行是正确的，当且仅当其结果与按某一次序串行地执行它们的结果相同时。可串行性

25、（serializability）是并行事务正确性的唯一准则。从理论上讲，在某一事务执行时禁止其他事务执行的调度策略一定是可串行化的调度，这也是最简单的调度策略。但这种方法实际上是不可行的因为它使用户不能充分共享数据库资源。目前常用的可串行化调度策略有悲观并发控乐观并发控制。9.3 管理并发数据访问9.3.2 并发控制的类型（1）悲观并发控制。悲观并发控制将在事务执行过程中根据需要锁定资源，阻止用户以影响其他用户的方式修改数据。比如用户执行的操作导致应用了某个锁，则直到这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作。该方法主要用在数据争夺激烈的环境中，以及出现并发冲突时用锁保护数据的

26、成本比回滚事务的成本低的环境中，因此该方法称为悲观并发控制。9.3 管理并发数据访问9.3.2 并发控制的类型（2）乐观并发控制。乐观并发控制中，用户读取数据时不锁定数据。当一个用户更新数据时，系统将进行检查，查看该用户读取数据后对其他用户是否又更改了该数据。如果其他用户更新了数据，将产生一个错误。一般情况下，受到错误信息的用户将回滚事务并重新开始。该方法主要用在数据争用不大，且偶尔回滚事务的成本低于读取数据时锁定数据的成本的环境内。目前DBMS普遍采用锁（悲观并发控制）来保证调度的正确性。9.3 管理并发数据访问9.3.3 事务的隔离级别锁在用作事务控制机制时，可以解决并发问题。虽然在同一时

27、间可以运行多个事务，但锁只允许事务独立运行，运行时互相完全隔离。事务可以设置隔离级别，隔离级别描述了一个事务必须与其他事务所进行的资源或数据更改相隔离的程度。隔离级别从允许并发负面影响（如脏读、幻读等）的角度进行描述。9.3 管理并发数据访问9.3.3 事务的隔离级别SQL Server 2005支持的隔离级别（从最低到最高）如下：未提交读(READ UNCOMMITTED)。已提交读(READ COMMITTED)。可重复读(REPEATEABLE READ)。可序列化（可串行化）(SERIALIZABLE)。随着隔离级别的提高，可以更有效地防止数据的不一致性。但是，这将降低事务的并发处理能

28、力，会影响多用户访问。9.3 管理并发数据访问9.3.3 事务的隔离级别SQL Server 支持的隔离级别（从低到高）如下：未提交读(READ UNCOMMITTED)。已提交读(READ COMMITTED)。可重复读(REPEATEABLE READ)。可序列化（可串行化）(SERIALIZABLE)。随着隔离级别的提高，可以更有效地防止数据的不一致性。但是，这将降低事务的并发处理能力，会影响多用户访问。表中列出了隔离级别以及各个级别可以解决的并发问题。隔离级别脏读不可重复读幻读未提交读是是是已提交读否是是可重复读否否是可串行读否否否9.3 管理并发数据访问9.3.3 事务的隔离级别隔离

29、级别可以通过编程方式进行设置，也可以通过使用SQL语法 SET TRANSACTION ISOLATION LEVEL进行设置。下面是使用SET TRANSACTIOIN ISOLATION LEVEL设置隔离级别的语法：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED| READ COMMITTED | REPEATABLE READ| SNAPSHOT | SERIALIZABLE 例9.8 将隔离级别设置为REPEATABLE READ时，对于后续每个Transact-SQL语句，SQL Server将所有共享锁保持到事务结束。程序代码如下

30、：USE teachingGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRAN; SELECT * FROM course; SELECT * FROM score;COMMIT TRAN;GO9.4 管理锁当多个用户或应用程序同时访问同一数据时，锁可以防止这些用户或应用程序同时对数据进行更改。锁由SQL Server 2005数据库引擎在内部进行管理。根据用户采取的操作，会自动获取和释放锁。如果在没有使用锁时多个用户同时更新同一数据，则数据库内的数据会出现逻辑错误。如果出现这种情况，则对这些数据执行的查询可能会产生意外的

31、结果。9.4 管理锁当事务开始并在事务内以查询语言、数据操作语言（DML）或数据定义语言（DDL）执行命令时，SQL Server 2005会锁定任何所需的资源以帮助保护所需隔离级别的资源。默认情况下，行级锁定用于数据页，页级锁定用于索引页。为保留系统资源，当超过行锁数的可配置阈值时，锁管理器将自动执行锁升级。在锁管理器中可以为每个会话分配的最大锁数是262143。9.4 管理锁9.4.1锁的类型锁的类型确定并发事务可以访问数据的方式。SQL Server根据必须锁定的资源和必须执行的操作来确定使用那种锁。表9.3介绍了SQL Server支持的锁类型。锁类型说明共享（S）保护资源，以便只能对

32、其进行读取访问。当资源上存在共享（S）锁时，其他事务均不能修改数据。排他（X）指示数据修改，例如插入、更新或删除。确保不能同时对同一资源进行多个更新。更新（U）防止常见形式的死锁。每次只有一个事务可以获得资源上的U锁。如果事务修改资源，则U锁将转换为X锁。架构在执行依赖于表架构的操作时使用。架构锁的类型时架构修改（Sch-M）和架构稳定性（Sch-S）。意向建立锁层次结构。最常见的意向锁类型是IS、IU和IX。这些锁指示事务正在处理层次结构中较低级别的某些资源，而不是所有资源。较低级别的资源将具有S、U或X锁。9.4 管理锁9.4.2可以锁定的资源可以锁定的资源指锁定的粒度或发生锁定的级别。默

33、认情况下，行级锁用于数据页，页级锁用于索引页。为保留系统资源，当超过行锁数的可配置阈值时，锁管理器将自动执行锁升级。在较小粒度（如行级）上锁定会提高并发性，但开销更多，因为如果锁定许多行，则必须持有更多的锁。在较大粒度（如表级）上锁定会降低并发性，因为锁定整个表会限制其他事务对该表任何部分的访问。但是，此级别上的锁定开销较少，因为维护的锁较少。可以锁定的资源主要包括行、数据页、架构、表和数据库等。 锁说明RID行标识符，用于锁定表内的单个行。PAG一个8K的数据页或索引页。TAB整个表，包括所有数据和索引。MD表的元数据。用于保护表架构。DB数据库。9.4 管理锁9.4.3锁的兼容性如果某事务

34、已锁定一个资源，而另一个事务又需要访问该资源，那么SQL Server会根据第一个事务所用锁定模式的兼容性确定是否授予第二个锁。对于已锁定的资源，只能施加兼容类型的锁。资源的锁定模式有一个兼容性矩阵，可以显示哪些锁与在同一资源上获取的其他锁兼容，并按照锁强度递增的顺序列出这些锁。表中显示了请求的锁定模式及其与现有锁定模式的兼容性。请求的模式ISSUIXSIXX意向共享（IS）是是是是是否共享（S）是是是否否否更新（U）是是否否否否意向排他（IX）是否否是否否意向排他共享（SIX）是否否否否否排他（X）否否否否否否9.4 管理锁9.4.4 死锁SQL Server 2005对并发事务的处理，使用

35、任何方案都会导致死锁（deadlock）问题。在下面两种情况下，可以发生死锁。第1种情况是，两个事务分别锁定了两个单独的对象，这时每一个事务都要求在另外一个事务锁定的对象上获得一个锁，结果是每一个事务都必须等待另外一个事务释放占有的锁，此时就发生了死锁。这种死锁是最典型的死锁形式。第2种情况是，在一个数据库中，有若干长时间运行的事务并行的执行操作，查询分析器处理非常复杂的查询时，例如连接查询，由于不能控制处理的顺序，有可能发生死锁。9.4 管理锁9.4.4 死锁死锁是指事务永远不会释放它们所占用的锁，死锁中的两个事务都将无限期等待下去。SQL Server 2005的SQL Server Da

36、tabase Engine自动检测死锁循环，并选择一个会话作为死锁中放弃的一方，通过终止该事务来打断死锁。被终止的事务发生回滚，并返回给连接一个错误消息。如果在交互式的Transact-SQL语句中发生死锁错误，用户只要简单地重新输入Transact-SQL语句即可。在程序中的Transact-SQL中，应用程序必须提供对死锁错误码的处理，例如通过提示信息通知用户或者自动再次执行该事务。9.4 管理锁9.4.4 死锁例9.9本例制造了一个简单的死锁场景，并由SQL Server检测和处理死锁。具体步骤和代码如下。（1）启动SQL Server Management Studio并打开一个查询设

37、计器窗口。（2）输入并执行以下代码来创建一个表t1，并在不关闭事务的情况下插入数据。USE teaching;GOCREATE TABLE t1(i int);BEGIN TRAN;INSERT INTO t1 VALUES(1);（3）打开第2个查询窗口并执行以下语句创建另一个表t2，并在其中插入数据，然后尝试在表t1中更新数据。USE teaching;GOCREATE TABLE t2(i int);BEGIN TRAN;INSERT INTO t2 VALUES(1);UPDATE t1 SET i=2;由于在查询1中的事务没有提交，因此该事务将被阻塞。（4）切换回查询窗口1，执行以下UPDATE语句更新表t2。此时会发生什么结果呢？UPDATE t2 SET i=3;在几秒钟后，其中一个事务被取消了，并且返回了一个错误消息