数据库设计开发案例教程(十)

CONFIDENTIAL数据库设计开发技术案例教程（十）事务控制与锁定2北京亚思晟科技有限公司主要内容1，事务的概念及分类2，事务的简单操作3，锁的分类4，死锁的处理3北京亚思晟科技有限公司10.1事务管理1.事务简介事务（transaction）是SQLServer中的单个逻辑工作单元，也是一个操作序列，它包含了一组数据库操作命令。一个事务内的所有语句被作为一个整体执行。在事务执行过程中，如果遇到错误，可以回滚事务，取消该事务所做的全部改变，从而保证数据库的一致性和完整性。因此，事务是一个不可分割的工作逻辑单元，一个事务中的语句要么全部正确执行，要么全部不起作用。事务作为一个逻辑工作单元必须具有四个属性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。这四个属性简称ACID属性。原子性：事务必须是原子工作单元，对于其数据的修改，要么全都执行，要么全都不执行。一致性：事务必须完成全部的操作，事务开始时系统为一个确定的状态，完成后则成为另一个确定的状态，未完成则回到事务开始的确定状态，不允许出现未知的、不一致的“中间”状态。由此可见，一致性和原子性是密切相关的。隔离性：当许多人试图同时修改数据库内的数据时，必须执行控制以使某个人所做的修改不会对他人产生负面影响，这就足并发控制。一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能相互干扰。持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将4北京亚思晟科技有限公司10.1事务管理在SQLServer中，系统将事务模式分为显式事务、隐式事务、自动事务和批处理级事务四种。1）．显式事务显式事务就是可以显式地定义事务的开始和结束的事务，这类事务又称为用户定义事务。BEGINTRAN[SACTION][transaction_name|@Tran_name_variable]标记一个显式本地事务的起始点。COMMITTRAN[SACTION][transaction_name|@Tran_name_variable]或COMMITWORK标记一个成功的显式事务或隐性事务的结束。如果没有遇到错误，可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。ROLLBACKTRAN[SACTION][transaction_name|@Tran_name_variable]或ROLLBACKWORK将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点，用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。SAVETRAN[SACTION]在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的T-sQL语句和COMMITTRANSACTION语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用ROLLBACKTRANSACTIONtransaction_name浯句，这将撤销事务的所有语句和过程。在事务中允许有重复的保存点名称，但指定保存点名称的ROLLBACKTRANSACTION语句只将事务回滚到使用该名称的最近的SAVETRANSACTION。5北京亚思晟科技有限公司10.1事务管理2）．隐式事务隐式事务是指在当前事务提交或回滚后，SQLServer自动开始的事务。所以'隐式事务不需要使用BEGINTRANSACTION语句标识事务的开始，而只需要用户使用ROLLBACKTRANSACTION、COMMITTRANSAC,TION等语句回滚事务或结束事务。在回滚时，SQLServer又自动开始一个新的事务。3）．自动事务自动事务是一种能够自动执行并能自动回滚的事务。在自动事务模式下,'当一语句成功执行后，它被自动提交，而当它执行过程中产生错误时则自动回滚。自动事务模式是SQLServer的默认事务管理模式，当与SQLServer建立连接后，直接进入自动事务模式’直到使用BEGINTRANSCTION语句开始一个显式事务，或者执行SETIMPLICIT_TRANSACTIONSON语句进入隐式事务模式为止。但当显式事务被提交或回滚，或者执行SETIMPLICIT_TRANSACTIONOFF话句后，SQLServer又进入自动事务管理模式。4）．批处理级事务只能应用于多个活动结果集(MARS)，在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时，没有提交或回滚的批处理级事务自动由SQLServer进行回滚。6北京亚思晟科技有限公司10.1事务管理2．事务的操作举例以下示例：定义一个简单的事务，将Product表中的产品价格全部提高10%，只有全部价格都更新成功，才提交整个事务。USEascentWebDbBEGINTRANSACTIONMyTransactionUPDATEProductSETPrice1=Price1*1.1COMMITTRANSACTIONMyTransaction显示结果，如图10-1所示。如图10-17北京亚思晟科技有限公司10.1事务管理以下示例：在事务中使用保存点，用于回滚部分事务在Product表中，插入一条记录，设置一个保存点。然后将产品价格全部提高10%，如果更新成功提交整个事务，否则回滚到保存点。DECLARE@ErrorVarintBEGINinsertproduct(productnumber,productName,Category,Price1,Stock)VALUES('124','小儿复方赖氯酸颗粒','西药',13,100)SAVETRANSACTIONmytranpointUPDATEproductSETPrice1=price1*1.1SELECT@ErrorVar=@@errorEndif(@ErrorVar0)beginRollBackTransactionmytranpointPrint'更新产品价格失败'endelseBeginPrint'更新产品价格成功'CommittransactionmyalltranEnd显示结果，如图10-2所示。如图10-28北京亚思晟科技有限公司10.2锁的使用以下示例：在事务中使用保存点，用于回滚部分事务在Product表中，插入一条记录，设置一个保存点。然后将产品价格全部提高10%，如果更新成功提交整个事务，否则回滚到保存点。DECLARE@ErrorVarintBEGINinsertproduct(productnumber,productName,Category,Price1,Stock)VALUES('124','小儿复方赖氯酸颗粒','西药',13,100)SAVETRANSACTIONmytranpointUPDATEproductSETPrice1=price1*1.1SELECT@ErrorVar=@@errorEndif(@ErrorVar0)beginRollBackTransactionmytranpointPrint'更新产品价格失败'endelseBeginPrint'更新产品价格成功'CommittransactionmyalltranEnd显示结果，如图10-2所示。如图10-29北京亚思晟科技有限公司10.2锁的使用1.锁的概述当多个用户同时访问数据时，SQLServer2005数据库引擎通过使用锁来保证事务完整性。在多用户环境中，锁可以防止多用户同时修改同一数据。在SQLServer中，锁是被自动实现的，但也可以显式使用。每个事务对所依赖的资源（如行、页或表）请求不同类型的锁，当事务不再依赖锁定的资源时，它将释放锁。应用程序可以通过选择事务隔离级别，为事务定义保护级别，以防被其他事务所修改。当多个用户同时访问一个数据库而没有进行锁定时，修改数据的用户会影响同时读取或修改相同数据的其他用户，即这些用户可以并发访问数据。如果数据存储系统没有并发控制，则用户可能会看到以下负面影响：丢失更新未提交的依赖（脏读）不一致的分析（不可重复读）幻读10北京亚思晟科技有限公司10.2锁的使用1）．丢失更新丢失更新发生在两个或多个事务修改同一行的时候。在这种情况下，每个事务都不知道其他事务的存在，最后的更新将覆盖由其他事务所做的更新，这将导致前面事务完成的数据丢失。2）．未提交的依赖（脏读）未提交的依赖也称为脏读。脏读是指当一个事务修改数据时，另一个事务读取了修改的数据，由于某种原因第一个事务取消了对数据的修改，数据回到原来的状态，这时第二个事务读取的数据与数据库中的数据不相符，即读到了未提交的数据。3）．不一致的分析（不可重复读）不一致的分析乜称为不可重复读。不一致的分析是指当一个事务读取数据库中的数据后，另一个事务更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生变化，即多次访问同一行但每次读取到的数据不相同，因此被称为“不可重复读”。4）．幻读当一个事务对一个区域的数据执行插入或删除操作，而该区域的数据属于另一个事务正在读取的范围时，会发生幻读问题。由于其他事务的删除操作，事务第一次读取的范围显示有一行不再存在于第二次或后续读取内容中。同样，由于其他事务的插入操作，事务第二次或后续读取的内容显示有一行并不存在于原始渎取内容中。11北京亚思晟科技有限公司10.2锁的使用2.锁的类型如果并发问题不加以控制，就可能会读取和写入不正确的数据，而破坏事务的一致性。SQLServer使用锁机制来同步多个用户同时对同一个数据的访问。通过使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。SQLServer下的锁模式有多种，下面主要介绍共享锁、排他锁、更新锁。1）．共享锁共享锁（s锁）允许并发事务读取(SELECT)一个资源。资源上存在共享锁时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间肉用锁定提示保留共享锁。2）．排他锁排他锁（x锁）可以防止并发事务对资源进行访问。使用排他锁时，任何其他事务都无法读取或修改排他锁锁定的数据。3）．更新锁更新锁（U锁）可以防止常见的死锁。一个事务读取数据，对数据加上共享锁，然后修改此数据，这时要求共享锁转换为排他锁。如果有两个事务都获得了此资源上的共享锁，然后试图同时更新数据，则两个事务尝试将共享锁转换为排他锁。从共享锁到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享锁不兼容，所以发生锁等待。由于两个事务都要转换为排他锁，并且每个事务都等待另一个事务释放共享锁，因此发生死锁。要避免这种潜在的死锁问题，可在共享锁和排他锁的间隙使用更新锁（U锁）。更新锁被应用到带有共享锁的资源，一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换为排他锁。12北京亚思晟科技有限公司10.2锁的使用在SQLServer2005中，可以通过查询sysdm_tran_locks动态管理视图来获得有关数据库引擎实例中当前活动的锁管理器资源信息。同时，也可以使用系统存储过程sp—lock查看锁的信息。。使用系统存储过程sp_lock可以查看SQLServer系统或指定进程对资源的锁定情况，语法格式如下：Sp_lock[[@spid1=]’spid1’][,[@spil2=]’spil2’][;]其中，spid1和spid2为进程标识号。措定spid1和spid2参数时，SQLServer显示这些进程的锁定情况，否则显示整个系统的锁使用情况。进程标识号为一个整数，可以使用系统存储过程sp_who检索当前启动的进程及各进程所对应的标识号。3.设置事务隔离级别隔离级别是指一个事务和其他