数据库基础PPT第7章 事务处理及锁

第7章

事务处理与锁7.1事务概述7.2事务处理7.3锁简介7.4死锁及其排除简介Page129十月2023教学要求：通过本章学习，读者应掌握以下内容：通过学习本章，读者应掌握以下内容：了解SQLServer中事务与锁的概念了解并发控制，掌握理解事务控制语句了解可以锁定的资源项和锁的类型了解死锁的概念，死锁的排除，锁定信息的显示Page229十月20237.1事务概述

多用户并发存取同一数据可能会导致产生数据的不一致性问题，正确地使用事务处理可以有效控制这类问题的发生频度。7.1.1事务的概念事务（Transaction）是用户定义的一个数据库操作序列，是一个不可分割的工作单位。要么所有操作顺序完成，要么一个也不要做，绝不能只完成了部分操作，还有一些没有完成。在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或整个程序。7.1.2事务的特征

事务是作为单个逻辑工作单元执行的一系列操作。事务作为一个逻辑工作单元有4个属性。(1)原子性。事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。(2)一致性。事务在完成时，必须使所有的数据都保持一致状态。(3)隔离性。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。

(4)持久性。事务完成之后对系统的影响是永久的。事务一旦提交，它对数据库的更新不再受后继操作或故障的影响。7.2事务处理

1．显示启动事务（BEGINTRANSACTION）BEGINTRANSACTION语句用来显示定义事务，其语法格式如下：BEGINTRANSACTION[SACTION][transaction_name|@tran_name_variable[WITHMARK[‘description’]]]

各参数说明如下：(1)transaction_name是给事务分配的名称。transaction_name必须遵循标识符规则，但是不允许标识符多于32个字符。仅在嵌套的BEGIN…COMMIT或BEGIN…ROLLBACK语句的最外语句上使用事务名。(2)@tran_name_variable是用户定义的、含有有效事务名称的变量名称。必须用char、varchar、nchar或nvarchar数据类型声明该变量。(3)WITHMARK[‘description’]用来指定在日志中标记事务。Description是描述该标记的字符串。如果使用了WITHMARK，则必须指定事务名。WITHMARK允许将事务日志还原到命名标记。2．隐式启动事务通过API函数或T-SQLSETIMPLICIT_TRANSACTIONON语句，将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时，下一个T-SQL语句又将启动一个新事务。应用程序再使用SETIMPLICIT_TRANSACTIONOFF语句关闭隐式事务模式3．事务提交（COMMITTRANSACTION）COMMITTRANSACTION语句用来标志一个成功的隐式事务或显示事务的结束。其语法格式为：

COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]将显示事务或隐式事务回滚到事务的起点或事务内的某个保存点。其语法格式如下：

ROLLBACKTRAN[SACTION][transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]各参数说明如下：(1)transaction_name。是BEGINTRANSACTION上的事务指派的名称。transaction_name必须遵循标识符规则。嵌套事务时transaction_name必须是来自最远的BEGINTRANSACTION语句的名称。(2)@tran_name_variable是用户定义的、含有有效事务名称的变量名称。(3)savepoint_name。是来自SAVETRANSACTION语句的保存点名称，必须符合标识符规则。(4)@savepoint_variable。是用户定义的、含有有效保存点名称的变量。必须用char、varchar、nchar或nvarchar数据类型声明该变量。4．事务回滚ROLLBACKTRANSACTION

5．设置保存点在事务内设置保存点，其语法格式如下：

SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}6．事务嵌套与BEGIN…END语句类似，BEGINTRANSACTION和COMMITTRANSACTION语句也可以进行嵌套，即事务可以嵌套执行。【例7.1】定义一个事务，向“学生信息”表添加一条记录，并设置保存点。然后再删除该记录，并回滚到事务的保存点，提交事务。语句如下：USEjxglGOBEGINTRANSACTIONINSERTINTO学生信息(stu_id,stu_name,stu_sex,stu_birth)VALUES('2001020222',张小明','男','2002/09/23')SAVETRANSACTIONsavepoint_1DELETEFROM学生信息WHEREstu_id='2001020222'ROLLBACKTRANSACTIONsavepoint_1COMMITTRANSACTIONGO【例7.2】事务的隐式启动。USEjxglGOSETIMPLICIT_TRANSACTIONSON--启动隐式事务模式GO--第一个事务由INSERT语句启动INSERTINTO成绩VALUES(’2001020222’,’100101’,100)COMMITTRANSACTION--提交第一个隐式事务GO--第二个隐式事务由SELECT语句启动SELECTCOUNT(*)FROM成绩DELETEFROM成绩WHEREstu_id=’2001020222’COMMITTRANSACTION--提交第二个隐式事务GOSETIMPLICIT_TRANSACTIONSOFF--关闭隐式事务模式GO7.3锁简介

为了保证事务的隔离性和一致性，数据库管理系统需要对并发操作进行正确调度。如果没有调度好而导致多个用户同时访问一个数据库，那么当他们的事务同时使用相同的数据时可能会产生数据的不一致性。这些问题主要体现在下面几个方面。1．读“脏”数据库（DirtyRead）时间读“脏”数据时间读“脏”数据事务T1数据库中R的值事务T2事务T1数据库中R的值事务T2t0t1t2t3

READRR=R-200UPDATER1000

t4t5t6t7

ROLLBACK800

1000READR2．不可重复读（None-RepeatableRead）时间不可重复读时间不可重复读事务T1数据库中R的值事务T2事务T1数据库中R的值事务T2t0t1t2t3

READR

1000

READRR=R-300t4t5t6t7

READR

700

UPDATERCOMMIT3．丢失修改（LostUpdate）时间丢失数据时间丢失数据事务T1数据库中R的值事务T2事务T1数据库中R的值事务T2t0t1t2t3

READR

R=R-2001000

READR

t4t5t6t7

UPDATERREADR

800700R=R-300

UPDATER7.3.1SQLServer锁的模式

根据锁定资源的方式的不同，SQLServer提供了共享锁、更新锁、排他锁、意向锁等。1．共享（Shared）锁共享锁允许并发事务读取一个资源。资源上存在共享（S）锁时，任何其他事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享（S）锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享（锁）。它用于不更改或不更新数据的操作（只读操作），如SELECT语句。2．更新（Update）锁更新锁也称为U锁，它可以防止常见的死锁。更新锁用来预定要对资源施加X锁，它允许其他事务读，但不允许再施加U锁或X锁。3．排他（Exclusive）锁排他（X）锁可以防止并发事务对资源进行访问。其他事务不能读取或修改排他（X）锁锁定的数据。用于数据修改操作，例如INSERT、UPDATE或DELETE，确保不会同时同一资源进行多重更新。4．意向（Intent）锁数据库引擎使用意向锁来保护共享锁（S）或排他（X）锁，放置在锁层次结构的底层资源上。意向锁的类型为意向共享（IS）、意向排他（IX）以及与意向排他共享（SIX）。5．架构（Schema）锁执行表的数据定义语言（DDL）操作（如添加列或删除列）时使用架构修改锁（Sch-M）。在架构修改锁（Sch-M）起作用期间，会防止对表的并发访问。6．BU——大量更新（BulkUpdate）当将数据大容量复制到表，且指定了TABLOCK提示或者使用sp_tableoption设置了tablelockonbulk表选项时，将使用大容量更新（BU）锁。7．RANGE——键范围（Key-Range）在使用可序列化事务隔离级别时，对于T-SQL语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。7.3.2SQLServer中锁的查看

SQLServer为了尽量减少锁定的开销，允许一个事务锁定不同类型的资源，具有多粒度锁定机制。SQLServer可以对行、页、键、键范围、索引、表或数据库获取锁。数据行(Row)。数据页中的单行数据。索引行。索引页中的单行数据，即索引的键值。页(Page)。页是SQLServer存取数据的基本单位，其大小是8KB。扩展盘区(Extent)。一个盘区由8个连续的页组成。表(Table)。数据库(Database)。锁定在较小的粒度（如行）可以提高并发度，因为如果锁定了许多行，则需要持有更多的锁，开销较高。锁定在较大的粒度（如表）会降低并发度，因为锁定整个表限制了其他事务对表中任意部分的访问，需要维护的锁较小，开锁较低。用系统存储过程查看锁使用系统存储过程sp_lock查看SQLServer所持有的所有锁的信息。系统存储过程sp_lock的语法格式如下：

sp_lock[[@spid1=]‘spid1’][,@spid2=]‘spid2’]

参数spid1、spid2都是来自master.dbo.sysprocessesrSQLServer进程ID号。Spid1的数据类型为int，默认值为NULL。如果没有指定spid，则显示所有锁的信息。【例7.3】查看SQLServer中当前持有的所有锁的信息。语句如下：USEmasterEXECsp_lock7.4死锁及其排除简介

在事务和锁的使用过程中，死锁是不可避免的。一般来说，对数据库的修改由一个事务组成，此事务读取记录，获取资源的共享锁，如果要修改记录行，需要转换成排他锁。如果两个事务获得了资源上的共享锁，然后试图同时更新数据，都要求加排他锁，就会发生两个事务互相等待对方释放共享锁的情况，这种现象称为死锁，如果不加干预，死锁中的两个事务都将无限期等待下去。【例7.4】死锁示例。测试用的基础数据。CREATETABLELocktable1(c1intdefault(0));CREATETABLELocktable2(c1intdefault(0));INSERTINTOLocktable1values(1);INSERTINTOLocktable2values(1);打开两个查询窗口，分别执行下面两段SQL代码。--查询语句1BEGINTRANUPDATELocktable1SETc1=c1+2;WAITFORDelay’00:01:00’;SELECT*FROMLocktable2ROL