数据库设计开发案例教程

《数据库设计开发案例教程》由会员分享，可在线阅读，更多相关《数据库设计开发案例教程（23页珍藏版）》请在金锄头文库上搜索。

1、CONFIDENTIAL数据库设计开发 技术案例教程（十）事务控制与锁定2北京亚思晟北京亚思晟科技有限公司科技有限公司主要内容1，事务的概念及分类2，事务的简单操作3，锁的分类4，死锁的处理3北京亚思晟北京亚思晟科技有限公司科技有限公司10.1事务管理1.事务简介 事务（transaction）是SQL Server中的单个逻辑工作单元，也是一个操作序列， 它包含了一组数据库操作命令。一个事务内的所有语句被作为一个整体执行。在 事务执行过程中，如果遇到错误，可以回滚事务，取消该事务所做的全部改变， 从而保证数据库的一致性和完整性。因此，事务是一个不可分割的工作逻辑单元 ，一个事务中的语句要么全

2、部正确执行，要么全部不起作用。 事务作为一个逻辑工作单元必须具有四个属性：原子性( Atomicity)、一致性 (Consistency)、隔离性( Isolation)和持久性(Durability)。这四个属性简称ACID属 性。 原子性：事务必须是原子工作单元，对于其数据的修改，要么全都执行，要么全 都不执行。 一致性：事务必须完成全部的操作，事务开始时系统为一个确定的状态，完成后 则成为另一个确定的状态，未完成则回到事务开始的确定状态，不允许出现未知 的、不一致的“中间”状态。由此可见，一致性和原子性是密切相关的。 隔离性：当许多人试图同时修改数据库内的数据时，必须执行控制以使某个人

3、所 做的修改不会对他人产生负面影响，这就足并发控制。一个事务的执行不能被其 他事务干扰，即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并 发执行的各个事务之间不能相互干扰。 持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故 障也将4北京亚思晟北京亚思晟科技有限公司科技有限公司10.1事务管理在SQL Server中，系统将事务模式分为显式事务、隐式事务、自动事务和批处理级事务四种。 1）显式事务 显式事务就是可以显式地定义事务的开始和结束的事务，这类事务又称为用户定义事务。 BEGIN TRAN SACTION transaction_name |Tran_na

4、me_variable 标记一个显式本地事务的起始点。 COMMIT TRAN SACTION transaction_name |Tran_name_variable 或 COMMIT WORK 标记一个成功的显式事务或隐性事务的结束。如果没有遇到错误，可使用该语句成功地结束事务。该事 务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。 ROLLBACK TRAN SACTION transaction_name| Tran_name_variable 或 ROLLBACK WORK 将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点，用来清除遇到错误的事务。该事务修

5、改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。 SAVE TRANSACTION 在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一 个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的T-sQL语句和COMMIT TRANSACTION语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用 ROLLBACK TRANSACTION transaction_ name浯句，这将撤销事务的所有语句和过程。 在事务中允许有重复的保存点名称，但指定保存点名称的ROLLBACK TRANSACTION语句只将事务回 滚

6、到使用该名称的最近的SAVE TRANSACTION。5北京亚思晟北京亚思晟科技有限公司科技有限公司10.1事务管理2）隐式事务 隐式事务是指在当前事务提交或回滚后，SQL Server自动开始的事务。所以隐式事务不需 要使用BEGIN TRANSACTION语句标识事务的开始，而只需要用户使用ROLLBACK TRANSACTION、COMMIT TRANSAC,TION等语句回滚事务或结束事务。在回滚时， SQL Server又自动开始一个新的事务。3）自动事务 自动事务是一种能够自动执行并能自动回滚的事务。在自动事务模式下,当一语句成功执行 后，它被自动提交，而当它执行过程中产生错误时则

7、自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式直到使 用BEGIN TRANSCTION语句开始一个显式事务，或者执行SET IMPLICIT_ TRANSACTIONS ON语句进入隐式事务模式为止。但当显式事务被提交或回滚，或者执行 SET IMPLICIT_TRANSACTION OFF话句后，SQL Server又进入自动事务管理模式。4）批处理级事务 只能应用于多个活动结果集( MARS)，在MARS会话中启动的T-SQL显式或隐式事务变为 批处理级事务。当批处理完成时，没有提交或回滚的批处理级事务自动由SQL

8、 Server进行 回滚。6北京亚思晟北京亚思晟科技有限公司科技有限公司10.1事务管理2事务的操作举例 以下示例： 定义一个简单的事务，将Product表中的产品价格全部提高10%，只有全部 价格都更新成功，才提交整个事务。 USE ascentWebDb BEGIN TRANSACTION MyTransaction UPDATE Product SET Price1 =Price1*1.1 COMMIT TRANSACTION MyTransaction 显示结果，如图10-1所示。如图10-17北京亚思晟北京亚思晟科技有限公司科技有限公司10.1事务管理以下示例： 在事务中使用保存点，

9、用于回滚部分事务 在Product表中，插入一条记录，设置一个保存点。然后将产品价格全部提高10%，如果更新成功提 交整个事务，否则回滚到保存点。 DECLARE ErrorVar int BEGIN insert product(productnumber,productName,Category,Price1,Stock) VALUES (124,小儿复方赖氯酸颗粒,西药,13,100) SAVE TRANSACTION mytranpoint UPDATE product SET Price1 =price1 *1.1 SELECT ErrorVar=error End if(Error

10、Var0) begin RollBack Transaction mytranpoint Print 更新产品价格失败 end else Begin Print更新产品价格成功 Commit transaction myalltran End 显示结果，如图10-2所示。如图10-29北京亚思晟北京亚思晟科技有限公司科技有限公司10.2锁的使用1.锁的概述 当多个用户同时访问数据时，SQL Server 2005数据库引擎通过使用锁来保证事务完整 性。在多用户环境中，锁可以防止多用户同时修改同一数据。在SQL Server中，锁是被 自动实现的，但也可以显式使用。每个事务对所依赖的资源（如行、

11、页或表）请求不同 类型的锁，当事务不再依赖锁定的资源时，它将释放锁。应用程序可以通过选择事务隔 离级别，为事务定义保护级别，以防被其他事务所修改。当多个用户同时访问一个数据库而没有进行锁定时，修改数据的用户会影响同时读取或 修改相同数据的其他用户，即这些用户可以并发访问数据。如果数据存储系统没有并发 控制，则用户可能会看到以下负面影响： 丢失更新 未提交的依赖（脏读） 不一致的分析（不可重复读） 幻读10北京亚思晟北京亚思晟科技有限公司科技有限公司10.2锁的使用1）丢失更新 丢失更新发生在两个或多个事务修改同一行的时候。在这种情况下，每个事务都不知道 其他事务的存在，最后的更新将覆盖由其他事

12、务所做的更新，这将导致前面事务完成的 数据丢失。 2）未提交的依赖（脏读） 未提交的依赖也称为脏读。脏读是指当一个事务修改数据时，另一个事务读取了修改的 数据，由于某种原因第一个事务取消了对数据的修改，数据回到原来的状态，这时第二 个事务读取的数据与数据库中的数据不相符，即读到了未提交的数据。 3）不一致的分析（不可重复读） 不一致的分析乜称为不可重复读。不一致的分析是指当一个事务读取数据库中的数据后 ，另一个事务更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发 生变化，即多次访问同一行但每次读取到的数据不相同，因此被称为“不可重复读”。 4）幻读 当一个事务对一个区域的数据执

13、行插入或删除操作，而该区域的数据属于另一个事务正 在读取的范围时，会发生幻读问题。由于其他事务的删除操作，事务第一次读取的范围 显示有一行不再存在于第二次或后续读取内容中。同样，由于其他事务的插入操作，事 务第二次或后续读取的内容显示有一行并不存在于原始渎取内容中。11北京亚思晟北京亚思晟科技有限公司科技有限公司10.2锁的使用2.锁的类型 如果并发问题不加以控制，就可能会读取和写入不正确的数据，而破坏事务的一致性。 SQL Server使用锁机制来同步多个用户同时对同一个数据的访问。通过使用不同的锁模 式锁定资源，这些锁模式确定了并发事务访问资源的方式。SQL Server下的锁模式有多 种

14、，下面主要介绍共享锁、排他锁、更新锁。 1）共享锁 共享锁（s锁）允许并发事务读取( SELECT)一个资源。资源上存在共享锁时，任何其他 事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁，除非将事务隔离 级别设置为可重复读或更高级别，或者在事务持续时间肉用锁定提示保留共享锁。 2）排他锁 排他锁（x锁）可以防止并发事务对资源进行访问。使用排他锁时，任何其他事务都无法 读取或修改排他锁锁定的数据。 3）更新锁 更新锁（U锁）可以防止常见的死锁。一个事务读取数据，对数据加上共享锁，然后修改 此数据，这时要求共享锁转换为排他锁。如果有两个事务都获得了此资源上的共享锁， 然后试图同时更新

15、数据，则两个事务尝试将共享锁转换为排他锁。从共享锁到排他锁的 转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享锁不兼容，所以发生 锁等待。由于两个事务都要转换为排他锁，并且每个事务都等待另一个事务释放共享锁 ，因此发生死锁。 要避免这种潜在的死锁问题，可在共享锁和排他锁的间隙使用更新锁（U锁）。更新锁被 应用到带有共享锁的资源，一次只有一个事务可以获得资源的更新锁。如果事务修改资 源，则更新锁转换为排他锁。12北京亚思晟北京亚思晟科技有限公司科技有限公司10.2锁的使用在SQL Server 2005中，可以通过查询sysdm_ tran_ locks动态管理视图来获得有关数据 库引

16、擎实例中当前活动的锁管理器资源信息。同时，也可以使用系统存储过程splock查 看锁的信息。 使用系统存储过程sp_ lock可以查看SQL Server系统或指定进程对资源的锁定情况，语 法格式如下： Sp_lock spid1=spid1 ,spil2=spil2; 其中，spid1和spid2为进程标识号。措定spid1和spid2参数时，SQL Server显示这些进程 的锁定情况，否则显示整个系统的锁使用情况。进程标识号为一个整数，可以使用系统 存储过程sp_who检索当前启动的进程及各进程所对应的标识号。3.设置事务隔离级别 隔离级别是指一个事务和其他事务的隔离程度，即指定数据库如何保护那些当前正在被 其他用户或服务器请求使用的数据。对于同时1行的多个事务，可以通过设置隔离级别来 平衡并发性和数据完整性。选择正确的隔离级别可以提高SQL Server的性能。 隔离级别需要使用set