数据库原理与应用(SQLServer)第13章事务和锁课件

1、第13章 事务和锁数据库原理与应用（SQL Server）113.1 事务13.2 锁定13.1 事务13.1.1 事务原理事务是作为单个逻辑工作单元执行的一系列操作，事务的处理必须满足ACID原则，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。（1）原子性事务必须是原子工作单元，即事务中包括的诸操作要么全执行，要么全不执行。（2）一致性事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。2数据库原理与

2、应用（SQL Server）13.1.1 事务原理（3）隔离性一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务间不能互相干扰。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，这称为事务的可串行性。因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。（4） 持久性指一个事务一旦提交，则它对数据库中数据的改变就应该是永久的。即使以后出现系统故障也不应该对其执行结果有任何影响。3数据库原理与应用（SQL Server）13.1.2 事务类型1. 系统

3、提供的事务 系统提供的事务是指在执行某些T-SQL语句时，一条语句就构成了一个事务，这些语句如下：CREATE ALTER TABLE DROPINSERT DELETE UPDATE SELECT REVOKE GRANT OPEN FETCH 例如执行如下的创建表语句：4数据库原理与应用（SQL Server）13.1.2 事务类型CREATE TABLE course( cno char(3) NOT NULL PRIMARY KEY, cname char(16) NOT NULL, credit int NULL, tno char (6) NULL)这条语句本身就构成了一个事务，它

4、要么建立起含4列的表结构，要么不能创建含4列的表结构，而不会建立起含1列、2列或3列的表结构。2. 用户定义的事务在实际应用中，大部分是用户定义的事务。用户定义的事务用BEGIN TRANSACTION指定一个事务的开始，用COMMIT或ROLLBACK指定一个事务的结束。5数据库原理与应用（SQL Server）13.1.3 事务模式SQL Server通过三种事务模式管理事务。（1）自动提交事务模式每条单独的语句都是一个事务。在此模式下，每条T-SQL语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。（2）显式事务模式该模式允许用户定义事务

5、的启动和结束。事务以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。（3）隐性事务模式隐性事务不需要使用BEGIN TRANSACTION语句标识事务的开始，但需要以COMMIT或ROLLBACK语句来提交或回滚事务。在当前事务完成提交或回滚后，新事务自动启动。6数据库原理与应用（SQL Server）13.1.4 事务处理语句1. BEGIN TRANSACTIONBEGIN TRANSACTION语句用来标识一个事务的开始。语法格式：BEGIN TRAN | TRANSACTION transaction_name | tran_name_va

6、riable WITH MARK description ; 说明： transaction_name：分配给事务的名称，必须符合标识符规则，但标识符所包含的字符数不能大于 32。7数据库原理与应用（SQL Server）13.1.4 事务处理语句 tran_name_variable：用户定义的、含有有效事务名称的变量的名称。 WITH MARK description ：指定在日志中标记事务。description 是描述该标记的字符串。BEGIN TRANSACTION语句的执行使全局变量TRANCOUNT的值加1。 2. COMMIT TRANSACTIONCOMMIT TRANSAC

7、TION语句是提交语句，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也用来标识一个事务的结束。8数据库原理与应用（SQL Server）13.1.4 事务处理语句语法格式：COMMIT TRAN | TRANSACTION transaction_name | tran_name_variable ; 说明： transaction_name：SQL Server 数据库引擎忽略此参数，transaction_name 指定由前面的 BEGIN TRANSACTION 分配的事务名称。 tran_name_variable：用户定义的、含有有效事务名称的变量的名称。COMM

8、IT TRANSACTION语句的执行使全局变量TRANCOUNT的值减1。9数据库原理与应用（SQL Server）13.1.4 事务处理语句【例13.1】建立一个显式事务以显示商店销售数据库的员工表的数据。BEGIN TRANSACTION USE StoreSales SELECT * FROM EmployeeCOMMIT TRANSACTION该语句创建的显示事务以BEGIN TRANSACTION语句开始，以COMMIT TRANSACTION语句结束。10数据库原理与应用（SQL Server）13.1.4 事务处理语句【例13.2】建立一个显式命名事务，删除部门表和员工表中部门

9、号为D003的记录行。DECLARE TranName char(20)SELECT TranName=TranDelBEGIN TRANSACTION TranName DELETE FROM Department WHERE DeptID=D003 DELETE FROM Employee WHERE DeptID=D003COMMIT TRANSACTION TranDel该语句创建的显式命名事务以删除部门表和员工表中部门号为D003的记录行，在BEGIN TRANSACTION和 COMMIT TRANSACTION语句之间的所有语句作为一个整体，当执行到COMMIT TRANSACT

10、ION语句时，事务对数据库的更新操作才算确认。11数据库原理与应用（SQL Server）13.1.4 事务处理语句【例13.3】建立一个隐性事务以插入部门表和员工表中部门号为D003的记录行。SET IMPLICIT_TRANSACTIONS ON /*启动隐性事务模式*/ GO/*第一个事务由INSERT语句启动*/USE StoreSalesINSERT INTO Department VALUES (D003,物资部)COMMIT TRANSACTION /*提交第一个隐性事务*/GO12数据库原理与应用（SQL Server）13.1.4 事务处理语句/*第二个隐式事务由SELECT

11、语句启动*/USE StoreSalesSELECT COUNT(*) FROM EmployeeINSERT INTO Employee VALUES (E004,徐莉思,女,1985-07-16,公司集体宿舍,3800,D003)COMMIT TRANSACTION /*提交第二个隐性事务*/GOSET IMPLICIT_TRANSACTIONS OFF /*关闭隐性事务模式*/GO该语句启动隐性事务模式后，由COMMIT TRANSACTION语句提交了两个事务，第一个事务在Department表中插入一条记录，第二个事务统计Employee表的行数并插入一条记录。隐性事务不需要BEGI

12、N TRANSACTION语句标识开始位置，而由第一个T-SQL语句启动，直到遇到COMMIT TRANSACTION语句结束。13数据库原理与应用（SQL Server）13.1.4 事务处理语句3. ROLLBACK TRANSACTIONROLLBACK TRANSACTION语句是回滚语句，它使得事务回滚到起点或指定的保存点处，也标志一个事务的结束。语法格式：ROLLBACK TRAN | TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable ; 说明： trans

13、action_name：事务名称。 tran_name_variable：事务变量名。 savepoint_name：保存点名。 savepoint_variable：含有保存点名称的变量名。14数据库原理与应用（SQL Server）13.1.4 事务处理语句如果事务回滚到开始点，则全局变量TRANCOUNT的值减1，而如果只回滚到指定存储点，则TRANCOUNT的值不变。【例13.4】建立事务对部门表进行插入操作，使用ROLLBACK TRANSACTION语句标识事务结束。BEGIN TRANSACTION USE StoreSales INSERT INTO Department VA

14、LUES(D005,财务部) INSERT INTO Department VALUES(D006,市场部)ROLLBACK TRANSACTION15数据库原理与应用（SQL Server）13.1.4 事务处理语句该语句建立的事务对课程表和成绩表进行插入操作，但当服务器遇到回滚语句ROLLBACK TRANSACTION时，清除自事务起点所做的所有数据修改，将数据恢复到开始工作之前的状态，所以事务结束后，课程表和成绩表都不会改变。【例13.5】 建立的事务规定员工表只能插入7条记录，如果超出7条记录，则插入失败，现在该表已有6条记录，向该表插入2条记录。USE StoreSalesGOBE

15、GIN TRANSACTION INSERT INTO Employee VALUES(E008,周世海,男,1992-04-15,NULL,3100,D001) INSERT INTO Employee VALUES(E009,向莉,女,1992-08-09,NULL,3100,D001)16数据库原理与应用（SQL Server）13.1.4 事务处理语句DECLARE Count intSELECT Count=(SELECT COUNT(*) FROM Employee)IF Count7 BEGIN ROLLBACK TRANSACTION PRINT 插入记录数超过规定数，插入失败

16、！ ENDELSE BEGIN COMMIT TRANSACTION PRINT 插入成功！ END17数据库原理与应用（SQL Server）13.1.4 事务处理语句该语句从BEGIN TRANSACTION定义事务开始，向Employee表插入2条记录，插入完成后，对该表的记录计数，判断插入记录数已超过规定的7条记录，使用ROLLBACK TRANSACTION语句撤消该事务所有操作，将数据恢复到开始工作之前的状态，事务结束后，Employee表未改变。【例13.6】建立一个事务，向商品表插入一行数据，设置保存点，然后再删除该行。BEGIN TRANSACTION USE StoreSa

17、les INSERT INTO Goods VALUES(1005,DELL GTX1050,10,5799,5,2) SAVE TRANSACTION GoodsPoint /* 设置保存点 */ DELETE FROM Goods WHERE GoodsID=1005 ROLLBACK TRANSACTION GoodsPoint /*回滚到保存点GoodsPoint */COMMIT TRANSACTION18数据库原理与应用（SQL Server）13.1.4 事务处理语句该语句建立的事务执行完毕后，插入的一行并没有被删除，因为回滚语句ROLLBACK TRANSACTION将操作回退

18、到保存点GoodsPoint，删除操作被撤消，所以Goods表增加了一行数据。【例13.7】建立事务更新员工表一行的列值，设置保存点，然后再插入一行到部门表。19数据库原理与应用（SQL Server）13.1.4 事务处理语句BEGIN TRANSACTION TranUpdate USE StoreSales UPDATE Employee SET Wages=3400 WHERE EmplID=E002 SAVE TRANSACTION EmplPoint /* 设置保存点 */ INSERT INTO Department VALUES(D005,财务部) IF (error=0) B

19、EGIN ROLLBACK TRANSACTION EmplPoint /* 如果上一T-SQL语句执行成功,回滚到保存点EmplPoint */ END ELSE COMMIT TRANSACTION TranUpdate20数据库原理与应用（SQL Server）13.1.4 事务处理语句该语句建立的事务执行完毕后，并未插入一行到Department表，由IF语句根据条件IF (error=0，上一T-SQL语句执行成功)，回滚语句ROLLBACK TRANSACTION将操作回退到保存点EmplPoint，插入操作被撤消，所以仅更新了Employee表一行的列值。4. 事务嵌套在SQL

20、Server中， BEGIN TRANSACTION和COMMIT TRANSACTION语句也可以进行嵌套，即事务可以嵌套执行。全局变量TRANCOUNT用于返回当前等待处理的嵌套事务数量，如果没有等待处理的事务，该变量值为0。BEGIN TRANSACTION语句将TRANCOUNT加1。ROLLBACK TRANSACTION将TRANCOUNT递减0，但ROLLBACK TRANSACTION savepoint_name 除外，它不影响TRANCOUNT。COMMIT TRANSACTION或COMMIT WORK将TRANCOUNT递 1。21数据库原理与应用（SQL Server

21、）13.2 锁定13.2.1 并发影响修改数据的用户会影响同时读取或修改相同数据的其他用户，即使这些用户可以并发访问数据。并发操作带来的数据不一致性包括丢失更新、脏读、不可重复读、幻读等。（1）丢失更新(lost update)当两个事务同时更新数据，此时系统只能保存最后一个事务更新的数据，导致另一个事务更新数据的丢失（2）脏读(dirty read)当第一个事务正在访问数据，而第二个事务正在更新该数据时，但尚未提交，会发生脏读问题，此时第一个事务正在读取的数据可能是”脏”(不正确)数据，从而引起错误。22数据库原理与应用（SQL Server）13.2.1 并发影响（3）不可重复读(unre

22、peatable read)如果第一个事务两次读取同一文档，但在两次读取之间，另一个事务重写了该文档，当第一个事务第二次读取文档时，文档已更改，此时发生原始读取不可重复。（4）幻读当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读问题。由于其他事务的删除操作，事务第一次读取的行的范围显示有一行不再存在于第二次或后续读取内容中。同样，由于其他事务的插入操作，事务第二次或后续读取的内容显示有一行并不存在于原始读取内容中。13.2.2 可锁定资源和锁模式1. 可锁定资源（1）数据行(Row)数据页中的单行数据。23数据库原理与应用（SQL Server）13.2.2 可锁

23、定资源和锁模式（2）索引行(Key) 索引页中的单行数据，即索引的键值。（3）页(Page) 页是SQL Server存取数据的基本单位，其大小为8KB。（4）扩展盘区(Extent) 一个盘区由8个连续的页组成。（5）表(Table) 包括所有数据和索引的整个表。（6）数据库(Database)整个数据库。2. 锁模式（1）共享锁（S锁）24数据库原理与应用（SQL Server）13.2.2 可锁定资源和锁模式共享锁允许并发事务在封闭式并发控制下读取资源。当资源上存在共享锁时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁，除非将事务隔离级别设置为可重复读或更高级别，

24、或者在事务持续时间内用锁定提示保留共享锁。（2）更新锁（U锁）更新锁可以防止常见的死锁。在可重复读或可序列化事务中，此事务读取数据，获取资源（页或行）的共享锁，然后修改数据，此操作要求锁转换为排他锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁。 共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁以进行更新。由于两个事务都要转换为排他锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。25数据库原理与应用（SQL Server）13.2.2 可锁定资源和锁模式若

25、要避免这种潜在的死锁问题，就要使用更新锁，一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换为排他锁。（3）排他锁（X锁）排他锁可防止并发事务对资源进行访问，其他事务不能读取或修改排他锁锁定的数据。（4）意向锁意向锁表示SQL Server需要在层次结构中的某些底层资源（如表中的页或行）上获取共享锁或排他锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁。意向锁可以提高性能，因为SQL Server仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确

26、定事务是否可以锁定整个表。26数据库原理与应用（SQL Server）13.2.2 可锁定资源和锁模式意向锁包括意向共享（IS）、意向排它（IX）以及与意向排它共享（SIX）。 意向共享（IS）锁：通过在各资源上放置S锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。意向排它（IX）锁：通过在各资源上放置X锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX是IS的超集。意向排它共享（SIX）锁：通过在各资源上放置IX锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。（5）架构锁执行表的数据定义语言操作（如增加列或删除表）时使用架构修改（Sch-M）锁。27数据库原理与应用（SQL Server）13.2.2 可锁定资源和锁模式当编译查询时，使用架构稳定性（Sch-S）锁。架构稳定性（Sch-S）锁不阻塞任何事务锁，包括排它锁。因此在编译查询时，其它事务（包括在表上有排它锁的事务）都能继续运行，但不能在表上执行DDL操作。在执行依赖于表架构的操作时使用。