数据库原理与应用教程―SQLServer.ppt

数据库原理与应用教程SQL Server,第12章事务与并发控制,第12章 事务与并发控制,关系型数据库有四个显著的特征，即完整性安全性监测性并发性完整性是数据库的一个重要特征，也是保证数据库中的数据切实有效、防止错误、实现商业规则的一种重要机制。在SQL Server中，数据的完整性是通过一系列逻辑来保障的，这些逻辑分为三个方面，即实体完整性、域完整性和参考完整性。,第12章 事务与并发控制,数据库的安全性就是要保证数据库中数据的安全，防止未授权用户随意修改数据库中的数据，确保数据的安全。在大多数数据库管理系统中，主要是通过许可来保证数据库的安全性。监测性：对任何系统都可以这样说，没有监测，就没有优化。这句话用在数据库管理系统方面，也是切合实际的。只有通过对数据库进行全面的性能监测，也才能发现影响系统性能的因素和瓶颈，才能针对瓶颈因素，采取切合实际策略，解决问题，提高系统的性能。,第12章 事务与并发控制,并发性：数据库是一个共享资源，可以供多个用户使用。这些用户程序可以一个一个地串行执行，每个时刻只有一个用户程序运行，执行对数据库的存取，其他用户程序必须等到这个用户程序结束以后方能对数据库存取。但是如果一个用户程序涉及大量数据的输入、输出交换，则数据库系统的大部分时间处于闲置状态。为了充分利用数据库资源，发挥数据库共享资源的特点，应该允许多个用户并行地存取数据库。但这样就会产生多个用户程序并发存取同一数据的情况，若对并发操作不加控制就可能会存取和存储不正确的数据，破坏数据库的一致性，所以数据库管理系统必须提供并发控制机制。,第12章 事务与并发控制,并发控制机制的好坏是衡量一个数据库管理系统性能的重要标志之一。SQL Server以事务为单位通常使用锁来实现并发控制。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定。这样，就可以保证任何时候都可以有多个正在运行的用户程序，但是所有用户程序都在彼此完全隔离的环境中运行。,第12章 事务与并发控制,12.1事务概述事务处理是数据库的主要工作，事务由一系列的数据操作组成，是数据库应用程序的基本逻辑单元，用来保证数据的一致性。SQL Server 2005 提供了几种自动的可以通过编程来完成的机制，包括事务日志、SQL事务控制语句，以及事务处理运行过程中通过锁定保证数据完整性的机制。事务和存储过程类似，由一系列T-SQL语句组成，是 SQL Server 2005系统的执行单元。在数据库处理数据的时候，有一些操作是不可分割的整体。例如，当用银行卡消费的时候首先要在账户扣除资金，然后再添加资金到公司的户头上。在这个过程中用户所进行的实际操作可以理解成不可分割的，不能只扣除不添加，当然也不能只添加不扣除。,第12章 事务与并发控制,12.1事务概述利用事务可以解决上面的问题，即把这些操作放在一个容器里，强制用户执行完所有的操作或者不执行任何一条语句。事务就是作为单个逻辑工作单元执行的一系列操作，这一系列的操作或者都被执行或者都不被执行。在SQL Server 2005中，事务要求处理时必须满足四个原则，即原子性、一致性、隔离性和持久性。,第12章 事务与并发控制,12.1事务概述(1)原子性：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。这一性质即使在系统崩溃之后仍能得到保证，在系统崩溃之后将进行数据库恢复，用来恢复和撤销系统崩溃处于活动状态的事务对数据库的影响，从而保证事务的原子性。系统对磁盘上的任何实际数据的修改之前都会将修改操作信息本身的信息记录到磁盘上。当发生崩溃时，系统能根据这些操作记录当时该事务处于何种状态，以此确定是撤销该事务所做出的所有修改操作，还是将修改的操作重新执行。,第12章 事务与并发控制,12.1事务概述(2)一致性：一致性要求事务执行完成后，将数据库从一个一致状态转变到另一个一致状态。即在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性，事务结束时，所有的内部数据结构都必须是正确的。例如在转账的操作中，各账户金额必须平衡，这一条规则对于程序员而言是一个强制的规定，由此可见，一致性与原子性是密切相关的。事务的一致性属性要求事务在并发执行的情况下事务的一致性仍然满足。它在逻辑上不是独立的，它由事务的隔离性来表示。,第12章 事务与并发控制,12.1事务概述(3)隔离性：也称为独立性，是指并行事务的修改必须与其他并行事务的修改相互独立。隔离性意味着一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。它要求即使有多个事务并发执行，看上去每个成功事务按串行调度执行一样。,第12章 事务与并发控制,12.1事务概述(4)持久性：在事务完成提交之后，就对系统产生持久的影响，即事务的操作将写入数据库中，无论发生何种机器和系统故障都不应该对其有任何影响。例如，自动柜员机（ATM）在向客户支付一笔钱时，就不用担心丢失客户的取款记录。事务的持久性保证事务对数据库的影响是持久的，即使系统崩溃。,第12章 事务与并发控制,12.2 事务的类型根据事务的系统设置和运行模式的不同，SQL Server 2005将事务分为多种类型。12.2.1根据系统的设置分类根据系统的设置，SQL Server 2005将事务分为两种类型：系统提供的事务和用户定义的事务。,第12章 事务与并发控制,12.2.1根据系统的设置分类1.系统事务系统提供的事务是指在执行某些语句时，一条语句就是一个事务。但是要明确，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。因此，只有一条语句构成的事务也可能包含了多行数据的处理。,系统提供的事务语句如下：ALTER TABLE、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATE TABLE，这些语句本身就构成了一个事务。,第12章 事务与并发控制,12.2.1根据系统的设置分类1.系统事务例12-1使用CREATE TABLE创建一个表。CREATE TABLE student(Id CHAR(10),Name CHAR(6),Sex CHAR(2)这条语句本身就构成了一个事务。这条语句由于没有使用条件限制，那么这条语句就是创建包含3个列的表。要么创建全部成功，要么全部失败。,第12章 事务与并发控制,12.2.1根据系统的设置分类2.用户定义事务在实际应用中，大多数的事务处理采用了用户定义的事务来处理。在开发应用程序时，可以使用BEGIN TRANSACTION语句来定义明确的用户定义的事务。在使用用户定义的事务时，一定要注意事务必须有明确的结束语句来结束。如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。事务的明确结束可以使用两个语句中的一个：COMMIT语句和ROLLBACK语句。COMMIT语句是提交语句，将全部完成的语句明确地提交到数据库中。ROLLBACK语句是取消语句，该语句将事务的操作全部取消，即表示事务操作失败。,第12章 事务与并发控制,12.2.2根据运行模式分类根据运行模式的不同，SQL Server 2005将事务分为4种类型：显示事务、隐式事务、自动提交事务和批处理级事务。1自动提交事务自动提交事务是指每条单独的T-SQL语句都是一个事务。如果没有通过任何T-SQL语句设置事务，一条T-SQL语句就是一个事务，语句执行完事务就结束。以前我们使用的每一条T-SQL语句都可以叫做一个自动提交事务。,第12章 事务与并发控制,12.2.2根据运行模式分类2显式事务显式事务指每个事务均以BEGIN TRANSACTION语句、COMMIT TRANSACTION或ROLLBACK TRANSACTION语句明确地定义了什么时候启动事务、什么时候结束事务的事务。3隐式事务隐式事务指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT TRANSACTION或ROLLBACK TRANSACTION语句显式结束。4批处理级事务批处理级事务是SQL Server 2005的新增功能，该事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。,第12章 事务与并发控制,12.3事务处理语句 所有的T-SQL语句本身都是内在的事务。另外，SQL Server中有专门的事务处理语句，这些语句将SQL 语句集合分组后形成单个的逻辑工作单元。事务处理的T-SQL语句包括：(1)定义一个事务的开始：BEGIN TRANSACTTCN；(2)提交一个事务：COMMIT TRANSACTION；(3)回滚事务：ROLLBACK TRANSACTION；(4)在事务内设置保存点：SAVE TRANSACTION。,第12章 事务与并发控制,12.3事务处理语句1BEGIN TRANSACTION语句BEGIN TRANSACTION语句定义一个显式本地事务的起始点，即事务的开始。其语法格式为：BEGIN TRAN|TRANSACTION transaction_name|tran_name_variable WITH MARK description其中：(1)TRANSACTION关键字可以缩写为TRAN。(2)transaction name是给事务分配的名称，事务可以定义名称，也可以不定义名称，但是只能使用符合标识符规则的名字。(3)tran_name_variable是含有效事务名称的变量的名称，必须用数据类型声明这个变量。(4)WITH MARK用于指定在日志中标记事务，description是描述该标记的字符串。,第12章 事务与并发控制,12.3事务处理语句2.COMMIT TRANSACTION语句 COMMIT TRANSACTION语句为提交一个事务，标志一个成功的隐式事务或显式事务的结束。其语法格式为：COMMIT TRAN|TRANSACTION transaction_name|tran_name_variable 对于COMMIT TRANSACTION语句需要注意以下几点：(1)因为数据已经永久修改，所以在COMMIT TRANSACTION语句后不能回滚事务。(2)在嵌套事务中使用COMMIT TRANSACTION时，内部事务的提交并不释放资源，也没有执行永久修改，只有在提交了外部事务时，数据修改才具有永久性而且资源才会被释放。,第12章 事务与并发控制,12.3事务处理语句3.ROLLBACK TRANSACTION语句ROLLBACK TRANSACTION语句将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点，它也标志一个事务的结束。其语法格式为：ROLLBACK TRAN|TRANSACTION transaction_name|tran_name_variable|savepoint_name|savepoint_variable 对于ROLLBACK TRANSACTION语句需要注意以下几点：(1)如果不指定回滚的事务名称或保存点，则ROLLBACK TRANSACTION命令会将事务回滚到事务的起点。,(2)在嵌套事务时，该语句将所有内层事务回滚到最远的BEGIN TRANSACTION语句，transaction_name也只能是来自最远的BEGIN TRANSACTION语句的名称。(3)在执行COMMIT TRANSACTION语句后不能回滚事务。(4)如果在触发器中发出ROLLBACK TRANSACITON命令，将回滚对当前事务中所做的所有数据修改，包括触发器所做的修改。(5)事务在执行过程中出现任何错误，SQL Server都将自动回滚事务。,第12章 事务与并发控制,12.3事务处理语句4.SAVE TRANSACTION语句SAVE TRANSACTION语句用于在事务内设置保存点。其语法格式为：SAVE TRAN|TRANSACTION savepoint_name|savepoint_variable 在事务内的某个位置建立一个保存点，使用户可以将事务回滚到该保存点的状态，而不回滚整个事务。,第12章 事务与并发控制,12.3事务处理语句使用事务时应注意以下几点：(1)不是所有的T-SQL语句都能放在事务里，通常insert、update、delete、select等可以放在事务里，创建、删除、恢复数据库等不能放在事务里。(2)事务要尽量的小，而且一个事务占用的资源越少越好。(3)如果事务在事务中间发生了错误，并不是所有情况都会回滚，只有达到一定的错误级别才会回滚，可以在事务中使用err变量查看是否发生了错误。,第12章 事务与并发控制,12.3事务处理语句例12-2定义一个事务，将所有选修了c001号课程的学生的分数加5分，并提交该事务。DECLARE t_name CHAR(10)SET t_name=add_scoreBEGIN TRANSACTION t_nameUSE 教学库UPDATE 选课SET 成绩=成绩+5WHERE 课程号=C001COMMIT TRANSACTION t_name,第12章 事务与并发控制,12.3事务处理语句例12-3定义一个事务，向教学库的学生表中插入一行数据，然后再删除该行。执行后，新插入的数据行并没有被删除。BEGIN TRANSACTION USE 教学库INSERT INTO 学生(学生号,姓名,性别,专业)VALUES(0501001,朱一虹,女,计算机)SAVE TRAN savepointDELETE FROM 学生WHERE 姓名=朱一虹ROLLBACK TRAN savepointCOMMIT,第12章 事务与并发控制,12.3事务处理语句例12-4定义一个事务，向仓库库存数据库的商品表中插入一行数据，如果插入成功，则向库存情况表中插入一行或多行此商品的库存情况信息，并显示“添加成功”；如果插入失败则不向库存情况表中插入数据，并显示“添加失败”。BEGIN TRANSACTION USE 仓库库存INSERT INTO 商品(商品编号,商品名称,单价,生产商)VALUES(bx-159,冰箱,2500,安徽美菱)IF error=0 BEGIN INSERT INTO 库存情况(仓库编号,商品编号,数量)VALUES(002,bx-159,20)PRINT 添加成功！COMMIT ENDELSE BEGIN PRINT 添加失败！ROLLBACKEND,第12章 事务与并发控制,12.4 事务的并发控制并发控制指的是当多个用户同时更新行时，用于保护数据库完整性的各种技术，目的是保证一个用户的工作不会对另一个用户的工作产生不合理的影响。锁是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时就可能会发生问题，这些问题包括以下几种情况：,第12章 事务与并发控制,12.4.1 并发带来的问题(1)丢失修改：指在一个事务读取一个数据时，另外一个事务也访问该同一数据。那么，在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务T1读取某表中数据A=20，事务T2也读取A=20，事务T1修改A=A-1，事务T2也修改A=A-1；最终结果A=19,事务T1的修改被丢失。,第12章 事务与并发控制,12.4.1 并发带来的问题(2)脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。例如：事务T1读取某表中数据A=20，并修改A=A-1，写回数据库，事务T2读取A=19，事务T1回滚了前面的操作，事务T2也修改A=A-1；最终结果A=18,事务T2读取的就是“脏数据”。,第12章 事务与并发控制,12.4.1 并发带来的问题(3)不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如：事务T1读取某表中数据A=20，并修改A=A-1，写回数据库，事务T2读取A=19，事务T1回滚了前面的操作，事务T2也修改A=A-1；最终结果A=18,事务T2读取的就是“脏数据”。,第12章 事务与并发控制,12.4.1 并发带来的问题(4)幻读：与不可重复读相似，是指当事务不是独立执行时发生的一种现象。例如，第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。当对某条记录执行插入或删除操作而该记录属于某个事务正在读取的行的范围时，会发生幻读问题。,第12章 事务与并发控制,12.4.2 锁的粒度SQL Server使用了（三层）锁协议，从而有效的控制并发操作可能产生的丢失更新、读“脏”数据、不可重复读等错误。SQL Server具有多种不同粒度的锁，允许事务锁定不同的资源，并能自动使用与任务相对应的等级锁来锁定资源对象，以使锁的成本最小化。,(1)行级锁表中的行是锁定的最小空间资源。行级锁是指事务操作过程中，锁定一行或若干行数据。(2)页和页级锁在SQL Server中，除行外的最小数据单位是页。一个页有8KB，所有的数据、日志和索引都放在页上。为了管理方便，表中的行不能跨页存放，一行的数据必须在同一个页上。页级锁是指在事务的操作过程中，无论事务处理多少数据，每一次都锁定一页。,(3)簇和簇级锁页之上的空间管理单位是簇，一个簇有8个连续的页。簇级锁指事务占用一个簇，这个簇不能被其他事务占用。簇级锁是一种特殊类型的锁，只用在一些特殊的情况下。例如在创建数据库和表时，系统用簇级锁分配物理空间。由于系统是按照簇分配空间的，系统分配空间时使用簇级锁，可防止其他事务同时使用一个簇。,(4)表级锁表级锁是一种主要的锁。表级锁是指事务在操纵某一个表的数据时锁定了这些数据所在的整个表，其他事务不能访问该表中的数据。当事务处理的数量比较大时，一般使用表级锁。(5)数据库级锁数据库级锁是指锁定整个数据库，防止其他任何用户或者事务对锁定的数据库进行访问。这种锁的等级最高，因为它控制整个数据库的操作。数据库级锁是一种非常特殊的锁，它只用于数据库的恢复操作。只要对数据库进行恢复操作，就需要将数据库设置为单用户模式，防止其他用户对该数据库进行各种操作。,行 页 簇 表 数据库,第12章 事务与并发控制,12.4.3锁的类型数据库引擎使用不同类型的锁锁定资源，这些锁确定了并发事务访问资源的方式。SQL Server 2005中常见的锁有以下几种：(1)共享锁（Shared Lock）共享(S)锁允许并发事务读取（SELECT）一个资源。资源上存在共享(S)锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享(S)锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享(S)锁。,第12章 事务与并发控制,12.4.3锁的类型(2)排它锁（Exclusive Lock）排它(X)锁可以防止并发事务对资源进行访问，其它事务不能读取或修改排它(X)锁锁定的数据。即排它(X)锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令即INSERT、UPDATE或DELETE命令时SQL Server会自动使用排它(X)锁，但当对象上有其它锁存在时无法对其加排它(X)锁。排它(X)锁一直到事务结束才能被释放。,第12章 事务与并发控制,12.4.3锁的类型(3)更新锁（Update Lock）更新(U)锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享锁，然后修改行，此操作要求锁转换为排它锁。如果两个事务获得了资源上的共享锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它锁。共享锁到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享锁不兼容，此时发生锁等待，而第二个事务也试图获取排它锁以进行更新；由于两个事务都要转换为排它锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。更新锁就是为了防止这种死锁而设立的。当SQL Server准备更新数据时，它首先对数据、对象作更新锁，锁定这样数据将不能被修改，但可以读取，等到SQL Server确定要进行更新数据操作时，它会自动将更新锁换为排它锁，但当对象上有其它锁存在时无法对其作更新锁锁定。,第12章 事务与并发控制,12.4.3锁的类型(4)意向锁（Intent Lock）意向锁建立一个锁机制的分层结构。如果对一个资源加意向锁，则说明该资源的下层资源正在被加锁（S锁或X锁）；对任一资源加锁时，必须先对它的上层资源加意向锁。系统使用意向锁来最小化锁之间的冲突。这种结构依据锁定的资源范围从低到高依次是行级锁层、页级锁层和表级锁层。,第12章 事务与并发控制,意向锁表示SQLServer需要在层次结构中的某些底层资源上获取共享(S)锁或排它(X)锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享(S)锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它(X)锁。意向锁可以提高性能，因为SQLServer仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。常用的意向锁有三种类型：意向共享锁，简记为IS锁；意向排它锁，简记为IX锁；共享意向排它锁，简记为SIX锁。,第12章 事务与并发控制,意向共享锁（IS锁）：意向共享锁表示读低层次资源的事务的意向，把共享锁放在这些单个的资源上。也就是说，如果对一个数据对象加IS锁，表示它的下层资源拟（意向）加S锁。例如，要对某个元组加S锁，则要首先对表加IS锁。意向排它锁（IX锁）：意向排它锁表示修改低层次的事务的意向，把排它锁放在这些单个资源上。也就是说，如果对一个数据对象加IX锁，表示它的下层资源拟（意向）加X锁。例如，要对某个元组加X锁，则要首先对他上层的表加IX锁。,第12章 事务与并发控制,12.4.3锁的类型共享意向排它锁（SIX锁）：共享意向排它锁是共享锁和意向排它锁的组合。使用共享意向排它锁表示允许并行读取顶层资源的事务的意向，并且修改一些低层次的资源，把意向排它锁放在这些单个资源上。也就是说，如果对一个数据对象加SIX锁，表示对它加S锁，再加IX锁，即SIX=S+IX。例如对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别元组（所以要对该表加IX锁）。,第12章 事务与并发控制,12.4.3锁的类型(5)模式（架构）锁（Schema Lock）模式锁保证当表或者索引被另外一个事务处理时，不能被删除或者修改其结构模式。SQL Server系统提供了两种类型的模式锁：模式稳定锁和模式修改锁。模式稳定锁确保锁定的资源不能被删除，模式修改锁确保其他会话不能参考正在修改的资源。执行表的数据定义语言(DDL)操作（例如添加列或除去表）时使用模式修改(Sch-M)锁。当编译查询时，使用模式稳定性(Sch-S)锁。模式稳定性(Sch-S)锁不阻塞任何事务锁，包括排它锁。因此在编译查询时，其它事务（包括在表上有排它锁的事务）都能继续运行。但不能在表上执行 DDL 操作。,第12章 事务与并发控制,12.4.3锁的类型(6)大容量更新锁(Bulk Update Lock)当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table_lock_on_bulk 表选项时，将使用大容量更新锁。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。,第12章 事务与并发控制,12.4.4锁的信息1锁的兼容性在一个事务已经对某个对象锁定的情况下，另一个事务请求对同一个对象的锁定，此时就会出现锁定兼容性问题。当两种锁定方式兼容时，可以同意对该对象的第二个锁定请求。如果请求的锁定方式与已挂起的锁定方式不兼容，那么就不能同意第二个锁定请求。相反，请求要等到第一个事务释放其锁定，并且释放所有其他现有的不兼容锁定为止。,第12章 事务与并发控制,资源锁模式有一个兼容性矩阵，显示了与在同一资源上可获取的其它锁相兼容的锁。见表。,第12章 事务与并发控制,关于锁的兼容性的一些说明：(1)意向排它(IX)锁与意向排它(IX)锁模式兼容，因为 IX锁表示打算更新一些行而不是所有行，还允许其它事务读取或更新部分行，只要这些行不是其它事务当前所更新的行即可。(2)架构稳定性(Sch-S)锁与除了架构修改(Sch-M)锁模式之外的所有锁模式相兼容。(3)架构修改(Sch-M)锁与所有锁模式都不兼容。(4)大容量更新(BU)锁只与架构稳定性(Sch-S)锁及其它大容量更新(BU)锁相兼容。,第12章 事务与并发控制,12.4.4锁的信息2查看锁的信息Exec sp_lock,第12章 事务与并发控制,12.4.5死锁的产生及解决办法封锁机制的引入能解决并发用户的数据不一致性问题，但也会引起事务间的死锁问题。在事务和锁的使用过程中，死锁是一个不可避免的现象。在数据库系统中，死锁是指多个用户分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户都处于等待对方释放所锁定资源的状态。通常，根据使用不同的锁类型锁定资源，然而当某组资源的两个或多个事务之间有循环相关性时，就会发生死锁现象。,第12章 事务与并发控制,12.4.5死锁的产生及解决办法 在数据库中如何避免死锁1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；3 优化程序，检查并避免死锁现象出现；4.对所有的脚本和SP都要仔细测试。5 所有的SP都要有错误处理（通过error）6 一般不要修改SQLServer事务的默认级别。不推荐强行加锁。,第12章 事务与并发控制,12.4.6手工加锁SQL Server系统中建议让系统自动管理锁，该系统会分析用户的SQL语句要求，自动为该请求加上合适的锁，而且在锁的数目太多时，系统会自动进行锁升级。如前所述，升级的门限由系统自动配置，并不需要用户配置。,在实际应用中，有时为了应用程序正确运行和保持数据的一致性，必须人为地给数据库的某个表加锁。比如，在某应用程序的一个事务操作中，需要根据一编号对几个数据表做统计操作，为保证统计数据时间的一致性和正确性，从统计第一个表开始到全部表结束，其他应用程序或事务不能再对这几个表写入数据，这个时候，该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地（显式地）锁定这几个表，这就需要用到手工加锁（也称显式加锁）技术。,第12章 事务与并发控制,12.4.6手工加锁在SQL Server 的SQL语句（SELECT、INSERT、DELETE、UPDATE）支持显式加锁。这4个语句在显式加锁的语法上类似，下面仅以SELECT语句为例给出语法：SELECT FROM WITH（锁类型）其中，WITH 指需要在该语句执行时添加在该表上的锁类型。所指定的锁类型有如下几种：(1)HOLDLOCK：在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。(2)NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。,（3）paglock 页面锁（4）rowlock 行级锁,第12章 事务与并发控制,(5)SERIALIZABLE：设置事务为可串行的隔离性级别。(6)TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。(7)TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。(8)UPDLOCK：指定在读表中数据时设置修改锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。,第12章 事务与并发控制,例12-5系统自动加排它锁的情况。新建两个连接，在第一个连接中执行以下语句：BEGIN TRAN UPDATE 学生 SET 姓名=aaaa WHERE 学生号=0101001WAITFOR DELAY 00:00:30-等待30秒COMMIT TRAN在第二个连接中执行以下语句：BEGIN TRAN SELECT*FROM 学生 WHERE 学生号=0101001COMMIT TRAN若同时执行上述两个语句，则SELECT 查询必须等待UPDATE（系统自动加排它锁）执行完毕才能执行，即要等待30秒。,第12章 事务与并发控制,例12-6人为加HOLDLOCK锁的情况。新建两个连接，在第一个连接中执行以下语句：use 教学库Begin transactionSelect*from 学生 with(holdlock)Where 学生号=0100201waitfor delay 00:00:30Commit transaction在第二个连接中执行以下语句use 教学库begin transactionupdate 学生set 姓名=wwwwhere 学生号=0100201commit transaction若同时执行上述两个语句，则第二个连接中的select查询可以执行，而UPDATE必须等待第一个连接中的共享锁结束后才能执行，即要等待30秒。,