SERVER的锁机制(一)——概述(锁的种类与范围

作者: www.9159.com  发布:2019-11-20

锁兼容性图:

事务的概念

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

SQL SERVER的锁机制系列:

图片 1

事务:若干条T-SQL指令组成的一个操作数据库的最小执行单元,这个整体要么全部成功,要么全部失败。(并发控制)

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作

 SQL SERVER的锁机制(一)——概述(锁的种类与范围)

一、锁的粒度:

事务的四个属性:原子性、一致性、隔离性、持久性。称为事务的ACID特性。


 SQL SERVER的锁机制(二)——概述(锁的兼容性与可以锁定的资源)

图片 2

  • 原子性(atomicity)一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
  • 一致性(consistency)事务内的操作都不能违反数据库约束或规则,事务完成时的内部数据结构都必须是正确的。
  • 隔离性(isolation)并发多个事务时,各个事务不干涉内部数据,处理的都是另外一个事务处理之前或之后的数据,不可能是另一个事务处理中的数据。
  • 持久性(durability)也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

oracle数据库中有两种基本的锁类型

 SQL SERVER的锁机制(三)——概述(锁与事务隔离级别)

比较需要注意的是RID/KEY、HoBT/PAGE这两对儿的区别,RID和HoBT是针对堆表的,即没有聚集索引的表。

SQL Server中3类常见的事务

1.排它锁(Exclusive Locks,即X锁

SQL SERVER的锁机制(四)——概述(各种事务隔离级别发生的影响)

二、锁的模式:

自动提交事务:是一种能够自动执行并能自动回滚事务,这种方式是T-SQL的默认事务方式。例如在删除一个表记录的时候,如果这条记录有主外键关系的时候,删除就会受主外键约束的影响,那么这个删除就会取消。
显式事务:T-sql标明,由Begin Transaction开启事务开始,由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
隐式事务:使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开,不用Begin Transaction开启事务,当一个事务结束,这个模式会自动启用下一个事务,只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

2.共享锁(Share Locks,即S锁)。

 

图片 3

事务常用的语句

当数据对象被加上排它锁时,其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取,但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

 

1.关于其中的S、U、X锁:

Begin Transaction:标记事务开始。
Commit Transaction:事务已经成功执行,数据已经处理妥当。
Rollback Transaction:数据处理过程中出错,回滚到没有处理之前的数据状态,或回滚到事务内部的保存点。
Save Transaction:事务内部设置的保存点,就是事务可以不全部回滚,只回滚到这里,保证事务内部不出错的前提下


锁定:通俗的讲就是加锁。锁定是 Microsoft SQL Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

共享锁

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁(S 锁)时,任何其他事务都不能修改数据。 读取操作一完成,就立即释放资源上的共享锁(S 锁),除非将事务隔离级别设置为可重复读或更高级别,或者在事务持续时间内用锁定提示保留共享锁(S 锁)。

图片 4

锁定:通俗的讲就是加锁。锁定是Microsoft SQL Server数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

定义:当有事务操作时,数据库引擎会要求不同类型的锁定,如相关数据行、数据页或是整个数据表,当锁定运行时,会阻止其他事务对已经锁定的数据行、数据页或数据表进行操作。只有在当前事务对于自己锁定的资源不在需要时,才会释放其锁定的资源,供其他事务使用。

更新锁

更新锁(U 锁)可以防止常见的死锁。 在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)]。 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。 共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取排他锁(X 锁)以进行更新。 由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。 一次只有一个事务可以获得资源的更新锁(U 锁)。 如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。

一般情况下更新都是直接获取独占锁的,但是如果被更新的行上已经存在独占锁,那么事务就会尝试先获取S锁,如果有多个会话在请求S锁的队列中,那么当独占锁被释放后就有多个会话获取了S锁并尝试转换为X锁,此时发生死锁,U锁的出现即为了解决此问题。  --这段不是官网的描述,而是我根据MySQL的重复性检测加锁机制推测的。如果官网有关于此类先加S锁再转化X的文章请@我。

编写一个简单的事务

定义:当有事务操作时,数据库引擎会要求不同类型的锁定,如相关数据行、数据页或是整个数据表,当锁定运行时,会阻止其他事务对已经锁定的数据行、数据页或数据表进行操作。只有在当前事务对于自己锁定的资源不在需要时,才会释放其锁定的资源,供其他事务使用。

一、锁的种类与范围(如下表)

排他锁

排他锁(X 锁)可以防止并发事务对资源进行访问。 使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句(如 INSERT、UPDATE 和 DELETE)合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此,数据修改语句通常请求共享锁和排他锁。 例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下,除了请求更新行上的排他锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。

Ps:在这里官网的解释可能会有歧义,DML操作虽然是读取和修改的合并,但是只有update才会先加IU、U锁来读取数据,然后修改时转化为IX、X。而删除和插入我们可以认为从一开始就是加的IX、X锁。

2.关于其中的意向锁:

意向锁有两种用途:

  • 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
  • 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

图片 5

3.关于其中的架构锁:

数据库引擎在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。

某些数据操作语言 (DML) 操作(例如表截断)使用 Sch-M 锁阻止并发操作访问受影响的表。

数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁,其中包括排他 (X) 锁。 因此,在编译查询的过程中,其他事务(包括那些针对表使用 X 锁的事务)将继续运行。 但是,无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。

4.关于其中的大容量更新锁:

大容量更新锁(BU 锁)允许多个线程将数据并发地大容量加载到同一表,同时防止其他不进行大容量加载数据的进程访问该表。 在满足以下两个条件时,数据库引擎使用大容量更新 (BU) 锁。

  • 使用 Transact-SQL BULK INSERT 语句或 OPENROWSET(BULK) 函数,或者您使用某个大容量插入 API 命令(如 .NET SqlBulkCopy)、OLEDB 快速加载 API 或 ODBC 大容量复制 API 来将数据大容量复制到表。
  • TABLOCK指定提示或表大容量加载上的锁表选项设置使用sp_tableoption。

5.关于其中的键范围锁:

在使用可序列化事务隔离级别时,对于 Transact-SQL 语句读取的记录集,键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁可防止虚拟读取。 通过保护行之间键的范围,它还防止对事务访问的记录集进行虚拟插入或删除。

关于键范围锁可以参考官网,或者另一篇博客SQL Server事务隔离级别中对于可序列化读隔离级别的加锁说明。

 

三、锁升级

SQL Server数据库会发生锁升级,官网说明的锁升级触发条件为,如果没有使用 ALTER TABLE SET LOCK_ESCALATION 选项来禁用表的锁升级并且满足以下任一条件时,触发锁升级:

  • 单个 Transact-SQL 语句在单个无分区表或索引上获得至少 5,000 个锁。
  • 单个 Transact-SQL 语句在已分区表的单个分区上获得至少 5,000 个锁,并且 ALTER TABLE SET LOCK_ESCALATION 选项设为 AUTO。
  • 数据库引擎实例中的锁的数量超出了内存或配置阈值。
  • 如果由于锁冲突导致无法升级锁,则数据库引擎每当获取 1,250 个新锁时便会触发锁升级。

对于锁升级的优化官网提供如下建议:

  1. 使用READ_COMMITTED_SNAPSHOT事务隔离级别。
  2. 使用SNAPSHOT事务隔离级别。
  3. 使用READ UNCOMMITTED事务隔离级别。

一般情况下我们只需要把READ_COMMITTED_SNAPSHOT选项打开即可,可以避免select加锁,从而避免阻塞和锁升级。

此外还可以打开1211和1224来避免锁升级,但是极度不推荐,锁升级本身就是为加快锁获取的效率而设计的,根本解决办法还是优化SQL。

 

参考文档

SQL Server 事务锁定和行版本控制指南:

关于锁升级,参考官方页面:

--开启事务
begin tran tran_Addtable1
--错误捕捉
begin try
--语句正确
insert into table1 (id,name,value,sex) values (4,'michael2','chaoshuai2',1);
 --加入保存点
 --  save tran pigOneIn 
--sex为int型 出错
insert into table1 (id,name,value,sex) values (5,'michael3','chaoshuai3','天气下雨了');
insert into table1 (id,name,value,sex) values (6,'michael4','chaoshuai4',1);
end try
begin catch
    select Error_number() as ErrorNumber,  --错误代码
          Error_severity() as ErrorSeverity,  --错误严重级别,级别小于10 try catch 捕获不到
          Error_state() as ErrorState ,  --错误状态码
          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
   if(@@trancount>0) --全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务
      rollback tran tran_Addtable1  ---由于出错,这里回滚事务到原点,第一条语句也没有插入成功。
end catch
if(@@TRANCOUNT>0)
commit tran tran_Addtable1 --提交事务 

(一)共享锁

锁类型

说明

共享 (S)

用于不更改或不更新数据的读取操作,如 SELECT 语句。

更新 (U)

用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

独占(也可称排他)(X)

用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。

意向

用于建立锁的层次结构。 意向锁包含三种类型:意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。

架构

在执行依赖于表架构的操作时使用。 架构锁包含两种类型:架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

大容量更新 (BU)

在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。

键范围

当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

执行结果

共享锁(S锁)允许并发事务在封闭式并发控制下读取(SELECT)资源

 

图片 6

当查询(SELECT)某条记录时,SQL SERVER会尝试取得该条记录的上存在共享锁(S锁),或无法获取,就必须等待别人释放对该记录中某几种与共享锁互斥的锁,才能在设置共享锁之后,获取该条记录。

 

分析:由于插入table1时发生错误,根据事务的原子性,要么全做,要全不错,所以一条数据都没有插入

举例来说:当某人查询某张表的一条记录时,就会在该记录上放置共享锁,在而其他人也要查询这张表的此记录时,因为共享锁彼此不互斥,所以也可以再次放置共享锁,也就是说SQL SERVER允许不同连接同时读取相同的数据。如果此时有人要更新此记录,因为独占锁与共享锁互斥,所以无法放置独占锁,要等到所有读取此记录的人都读取完毕,释放了共享锁,更新数据的人才能对该记录设置独占锁,并进一步更新数据。一般情况下,在默认的事务隔离级别下,当数据读取完毕,SQL SERVER就会释放共享锁,除非有特别的设置。

(一)共享锁

事务的并发控制

(二)更新锁

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 

在多用户都用事务同时访问同一个数据资源的情况下,就会造成以下几种数据错误
1.更新丢失:多个用户同时对一个数据资源进行更新,必定会产生被覆盖的数据,造成数据读写异常。
2.不可重复读:如果一个用户在一个事务中多次读取一条数据,而另外一个用户则同时更新啦这条数据,造成第一个用户多次读取数据不一致。
3.脏读:第一个事务读取第二个事务正在更新的数据表,如果第二个事务还没有更新完成,那么第一个事务读取的数据将是一半为更新过的,一半还没更新过的数据,这样的数据毫无意义。
4.幻读:第一个事务读取一个结果集后,第二个事务,对这个结果集经行增删操作,然而第一个事务中再次对这个结果集进行查询时,数据发现丢失或新增。

更新锁是一种中继锁。当同一项资源从原来的查询操作转换为更新操作时,锁定机制会从共享锁变为更新锁,再进一步变成独占锁

当查询(SELECT)某条记录时,SQL SERVER 会尝试取得该条记录的上存在共享锁(S 锁),或无法获取,就必须等待别人释放对该记录中某几种与共享锁互斥的锁,才能在设置共享锁之后,获取该条记录。

设置事务隔离级别

在可重复读或可序列化事务中,此事务读取数据[获取资源(页或行)的共享锁(S锁)],然后修改数据[此操作要求锁转换为独占锁(X锁)]。 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为独占锁(X锁)。 共享模式到独占锁的转换必须等待一段时间,因为一个事务的独占锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取独占锁(X锁)以进行更新。 由于两个事务都要转换为独占锁(X锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁

举例来说:当某人查询某张表的一条记录时,就会在该记录上放置共享锁,在而其他人也要查询这张表的此记录时,因为共享锁彼此不互斥,所以也可以再次放置共享锁,也就是说SQL SERVER允许不同连接同时读取相同的数据。如果此时有人要更新此记录,因为独占锁与共享锁互斥,所以无法放置独占锁,要等到所有读取此记录的人都读取完毕,释放了共享锁,更新数据的人才能对该记录设置独占锁,并进一步更新数据。一般情况下,在默认的事务隔离级别下,当数据读取完毕,SQL SERVER就会释放共享锁,除非有特别的设置。

read uncommitted:这个隔离级别最低啦,可以读取到一个事务正在处理的数据,但事务还未提交,这种级别的读取叫做脏读。
read committed:这个级别是默认选项,不能脏读,不能读取事务正在处理没有提交的数据,但能修改。
repeatable read:不能读取事务正在处理的数据,也不能修改事务处理数据前的数据。
snapshot:指定事务在开始的时候,就获得了已经提交数据的快照,因此当前事务只能看到事务开始之前对数据所做的修改。
serializable:最高事务隔离级别,只能看到事务处理之前的数据。

若要避免这种潜在的死锁问题,请使用更新锁(U锁)。 一次只有一个事务可以获得资源的更新锁(U锁)。 如果事务修改资源,则更新锁(U锁)转换为独占锁(X锁)。

 

 

例如,当查询一条记录后,要将此内容更新(Update语句加上Where条件),一定会先查找记录,在查找的过程中就会对相关的记录放置共享锁,等找到相应的记录之后,SQL SERVER会先对记录放置更新锁,以避免发生死锁。因为共享锁与更新锁并不互斥,如果两个人同时对同一条记录放置共享锁,先进行更新的人,可以在别人也对同一条记录放置了共享锁时,继续放置更新锁,但因为更新锁互斥,所以当另一个人想再放置更新锁时,将无法设置,而进入停止等待状态。

(二)更新锁

锁的概念

(三)独占锁(也可称为排他锁)

更新锁是一种中继锁。当同一项资源从原来的查询操作转换为更新操作时,锁定机制会从共享锁变为更新锁,再进一步变成独占锁。

Microsoft SQL Server 数据库引擎使用不同的锁模式锁定资源,这些锁模式确定了并发事务访问资源的方式。

独占锁(排他锁)(X锁)可以防止并发事务对资源进行访问。 使用独占锁(X锁)时,任何其他事务都无法修改数据;仅在使用NOLOCK提示或未提交读隔离级别时才会进行读取操作。

在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为独占锁(X 锁)]。 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为独占锁(X 锁)。 共享模式到独占锁的转换必须等待一段时间,因为一个事务的独占锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取独占锁(X 锁)以进行更新。 由于两个事务都要转换为独占锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

锁的分类

对数据进行添加、修改、删除操作时(如INSERT、UPDATE和DELETE), 语句在执行所需的操作之前首先执行读取操作以获取数据。 因此,需先对所在的资源放置独占锁,以确保以上操作未完成时,不受到干扰,独占锁在开启事务之后,一直保留到事务结束。例如,UPDATE语句可能根据与一个表的联接修改另一个表中的行。 在此情况下,除了请求更新行上的独占锁之外,UPDATE语句还将请求在联接表中读取的行上的共享锁。

若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。 一次只有一个事务可以获得资源的更新锁(U 锁)。 如果事务修改资源,则更新锁(U 锁)转换为独占锁(X 锁)。

  • 共享锁:允许并发事务在封闭式并发控制下读取(SELECT)资源。资源上存在共享锁(S 锁)时,任何其他事务都不能修改数据。 读取操作一完成,就立即释放资源上的共享锁(S 锁);
  • 排他锁:可以防止并发事务对资源进行访问。使用排他锁时,任何其他事务都无法修改数据;数据修改语句(如 INSERT、UPDATE 和 DELETE)合并了修改和读取操作,通常请求共享锁和排他锁
  • 更新锁:防止常见的死锁。此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)]。果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。 共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取排他锁(X 锁)以进行更新。 由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

(四)意向锁

例如,当查询一条记录后,要将此内容更新(Update语句加上Where条件),一定会先查找记录,在查找的过程中就会对相关的记录放置共享锁,等找到相应的记录之后,SQL SERVER 会先对记录放置更新锁,以避免发生死锁。因为共享锁与更新锁并不互斥,如果两个人同时对同一条记录放置共享锁,先进行更新的人,可以在别人也对同一条记录放置了共享锁时,继续放置更新锁,但因为更新锁互斥,所以当另一个人想再放置更新锁时,将无法设置,而进入停止等待状态。

     更新锁(U 锁)使得一次只有一个事务可以获得资源的更新锁(U 锁)。 如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)

在记录上放置共享锁之前,需要对存放该记录的更大范围(如数据页或数据表)上设置意向锁,以避免其他连接对该页放置独占锁

 

  • 意向锁:数据库引擎使用意向锁来保护共享锁(S 锁)或排他锁(X 锁)放置在锁层次结构的底层资源上。在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。

数据库引擎使用意向锁来保护共享锁(S锁)或独占锁(X锁)放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。

(三)独占锁(也可称为排他锁)

    例如,在该表的页或行上请求共享锁(S 锁)之前,在表级请求共享意向锁。 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁(X 锁)。 意向锁可以提高性能,因为数据库引擎仅在表检           查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁有两种用途

独占锁(排他锁)(X 锁)可以防止并发事务对资源进行访问。 使用独占锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

  • 意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。
  • 架构锁:数据库引擎在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。
  • 大容量更新锁:  大容量更新锁(BU 锁)允许多个线程将数据并发地大容量加载到同一表,同时防止其他不进行大容量加载数据的进程访问该表。

· 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

对数据进行添加、修改、删除操作时(如 INSERT、UPDATE 和 DELETE), 语句在执行所需的操作之前首先执行读取操作以获取数据。 因此,需先对所在的资源放置独占锁,以确保以上操作未完成时,不受到干扰,独占锁在开启事务之后,一直保留到事务结束。例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下,除了请求更新行上的独占锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。

锁模式兼容性

· 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

(四)意向锁

图片 7

例如:

在记录上放置共享锁之前,需要对存放该记录的更大范围(如数据页或数据表)上设置意向锁,以避免其他连接对该页放置独占锁。

如何将死锁降低到最低

(五)架构锁

数据库引擎使用意向锁来保护共享锁(S 锁)或独占锁(X 锁)放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。

按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并处于一个批处理中。
使用较低的隔离级别。
使用基于行版本控制的隔离级别。
将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,使得已提交读事务使用行版本控制。
使用快照隔离。
使用绑定连接。

数据库引擎在表数据定义语言(DDL)操作(例如添加列或删除表)的过程中使用架构修改(Sch-M)锁。 保持该锁期间,Sch-M锁将阻止对表进行并发访问。 这意味着Sch-M锁在释放前将阻止所有外围操作。

意向锁有两种用途:

 

某些数据操作语言(DML)操作(例如表截断)使用Sch-M锁阻止并发操作访问受影响的表。

· 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

数据库引擎在编译和执行查询时使用架构稳定性(Sch-S)锁。Sch-S锁不会阻止某些事务锁,其中包括排他(X)锁。 因此,在编译查询的过程中,其他事务(包括那些针对表使用X锁的事务)将继续运行。 但是,无法针对表执行获取Sch-M锁的并发DDL操作和并发DML操作。

· 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

(六)大容量更新锁

例如,在该表的数据页或数据行上请求共享锁(S 锁)之前,在表级(或页级)请求共享意向锁,以防止另一个事务随后在包含那一页的表上尝试放置独占锁(X 锁)。 意向锁可以提高性能,因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。如下图。

数据库引擎在将数据大容量复制到表中时,指定 TABLOCK 提示或使用 sp_tableoption 选项(将数据表设置为 table lock on bulk load),则是使用大容量更新锁(BU)。 大容量更新锁(BU锁)允许多个线程将数据并发地大容量加载到同一表,以降低数据表的锁定竞争,同时防止其他不进行大容量加载数据的进程访问该表。

 图片 8

(七)键范围锁

意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)等等。各种意向锁的说明,如下表。

在使用可序列化事务隔离级别时,保护用户对于查询时所读取的数据行范围,以确保其他事务无法插入受“键范围锁”保护的数据行。键范围锁放置在索引上,指定开始与结束的索引键值。这些操作会先在索引上获取锁定,此种锁定可以封锁任何尝试进行插入、修改、删除索引键值在“键范围锁”中的数据行。例如:在索引键值“AAA”至“CZZ”范围中放置键范围锁,避免其他事务将含有索引键值的数据行插入到该范围内的任何地方,例如:“ABC”、“BCD”、“CEF”。另外当UPDATE语句搭配WHERE子句时,当SQL SERVER还在查找数据时,也有可能会设置键范围锁。

锁类型

说明

意向共享 (IS)

保护针对层次结构中某些(而并非所有)低层资源请求或获取的共享锁。

意向独占 (IX)

保护针对层次结构中某些(而并非所有)低层资源请求或获取的独占锁。 IX 是 IS 的超集,它也保护针对低层级别资源请求的共享锁。

意向独占共享 (SIX)

保护针对层次结构中某些(而并非所有)低层资源请求或获取的共享锁以及针对某些(而并非所有)低层资源请求或获取的意向独占锁。 顶级资源允许使用并发 IS 锁。 例如,获取表上的 SIX 锁也将获取正在修改的页上的意向独占锁以及修改的行上的独占锁。 虽然每个资源在一段时间内只能有一个 SIX 锁,以防止其他事务对资源进行更新,但是其他事务可以通过获取表级的 IS 锁来读取层次结构中的低层资源。

意向更新 (IU)

保护针对层次结构中所有低层资源请求或获取的更新锁。 仅在页资源上使用 IU 锁。 如果进行了更新操作,IU 锁将转换为 IX 锁。

共享意向更新 (SIU)

S 锁和 IU 锁的组合,作为分别获取这些锁并且同时持有两种锁的结果。 例如,事务执行带有 PAGLOCK 提示的查询,然后执行更新操作。 带有 PAGLOCK 提示的查询将获取 S 锁,更新操作将获取 IU 锁。

更新意向排他 (UIX)

U 锁和 IX 锁的组合,作为分别获取这些锁并且同时持有两种锁的结果。

 

下面来实际举例来说明

--示例代码一:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM [Book] WHERE [bookid]=1

WAITFOR DELAY '00:00:10'

COMMIT TRAN

 

 

可以通过另一条连接执行SP_LOCK 来查看上面代码的执行结果,如下图。在数据表上与数据页上都放置了意向共享锁,而在锁定的记录上放置了共享锁。

 图片 9

 

--示例代码二:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM [WBK_PDE_LIST] WHERE [WBOOK_NO]='BE404942450020' and cop_g_no='60217445'

WAITFOR DELAY '00:00:10'

COMMIT TRAN

 

 

可以通过另一条连接执行SP_LOCK 来查看上面代码的执行结果,如下图。由于上述代码中的[WBK_PDE_LIST]是一张堆表,所以直接就对数据表加了共享锁。

 图片 10

对上述示例中表WBK_PDE_LIST添加索引,

CREATE NONCLUSTERED INDEX [IX_WBK_PDE_LIST_WBOOKNO] ON [dbo].[WBK_PDE_LIST] 

(

[WBOOK_NO] ASC,

[COP_G_NO] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF

, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

 

 

然后再次执行代码示例二,再打开一个新的查询分析器,在查询分析器中执行SP_LOCK 来查看上面代码的执行结果,如下图。在数据表与数据页上分别放置了意向共享锁,在索引与数据行上放置了共享锁。

 图片 11

(五)架构锁

数据库引擎在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。

某些数据操作语言 (DML) 操作(例如表截断)使用 Sch-M 锁阻止并发操作访问受影响的表。

数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁,其中包括排他 (X) 锁。 因此,在编译查询的过程中,其他事务(包括那些针对表使用 X 锁的事务)将继续运行。 但是,无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。

(六)大容量更新锁

数据库引擎在将数据大容量复制到表中时,指定 TABLOCK 提示或使用 sp_tableoption 选项(将数据表设置为 table lock on bulk load),则是使用大容量更新锁(BU)。 大容量更新锁(BU 锁)允许多个线程将数据并发地大容量加载到同一表,以降低数据表的锁定竞争,同时防止其他不进行大容量加载数据的进程访问该表。

(七)键范围锁

在使用可序列化事务隔离级别时,保护用户对于查询时所读取的数据行范围,以确保其他事务无法插入受“键范围锁”保护的数据行。键范围锁放置在索引上,指定开始与结束的索引键值。这些操作会先在索引上获取锁定,此种锁定可以封锁任何尝试进行插入、修改、删除索引键值在“键范围锁”中的数据行。例如:在索引键值“AAA”至“CZZ”范围中放置键范围锁,避免其他事务将含有索引键值的数据行插入到该范围内的任何地方,例如:“ABC”、“BCD”、“CEF”。另外当UPDATE语句搭配WHERE子句时,当SQL SERVER还在查找数据时,也有可能会设置键范围锁。

 

本文由9159.com发布于www.9159.com,转载请注明出处:SERVER的锁机制(一)——概述(锁的种类与范围

关键词: