B01 SQL Server表锁定原理以及如何解除锁定.docx

B01 SQL Server表锁定原理以及如何解除锁定.docx

《B01 SQL Server表锁定原理以及如何解除锁定.docx》由会员分享，可在线阅读，更多相关《B01 SQL Server表锁定原理以及如何解除锁定.docx（16页珍藏版）》请在冰豆网上搜索。

B01SQLServer表锁定原理以及如何解除锁定

SQLServer表锁定原理以及如何解除锁定

　　1.1目前的C/S,B/S结构都是多用户访问数据库,每个时间点会有成千上万个user来访问DB,其中也会同时存取同一份数据,会造成数据的不一致性或者读脏数据.

　　1.2事务的ACID原则

　　1.3锁是关系数据库很重要的一部分,数据库必须有锁的机制来确保数据的完整和一致性.

　　1.3.1SQLServer中可以锁定的资源:

　　1.3.2锁的粒度:

　　1.3.3锁的升级:

　　锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置.

　　1.3.4锁的类型:

（1）共享锁:

　　共享锁用于所有的只读数据操作.

（2）修改锁:

　　修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象

　　（3）独占锁:

　　独占锁是为修改数据而保留的。

它所锁定的资源，其他事务不能读取也不能修改。

独占锁不能和其他锁兼容。

　　（4）架构锁

　　结构锁分为结构修改锁（Sch-M）和结构稳定锁（Sch-S）。

执行表定义语言操作时，SQLServer采用Sch-M锁，编译查询时，SQLServer采用Sch-S锁。

　　（5）意向锁

　　意向锁说明SQLServer有在资源的低层获得共享锁或独占锁的意向。

　　（6）批量修改锁

　　批量复制数据时使用批量修改锁　

1.3.4SQLServer锁类型

（1）HOLDLOCK:

在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

（2）NOLOCK：

不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

　　（3）PAGLOCK：

指定添加页锁（否则通常可能添加表锁）。

　　（4）READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。

默认情况下，SQLServer2000在此隔离级别上操作。

　　（5）READPAST:

跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，

　　READPAST仅仅应用于READCOMMITTED隔离性级别下事务操作中的SELECT语句操作。

　　（6）READUNCOMMITTED：

等同于NOLOCK。

　　（7）REPEATABLEREAD：

设置事务为可重复读隔离性级别。

　　（8）ROWLOCK：

使用行级锁，而不使用粒度更粗的页级锁和表级锁。

　　（9）SERIALIZABLE：

用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。

等同于HOLDLOCK。

　　（10）TABLOCK：

指定使用表级锁，而不是使用行级或页面级的锁，SQLServer在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。

　　　　（11）TABLOCKX：

指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。

　　（12）UPDLOCK：

指定在读表中数据时设置更新锁（updatelock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。

　　（本段摘自CSDN博客:

　　2.如何解除表的锁定,解锁就是要终止锁定的那个链接,或者等待该链接事务释放.

　　2.1ActivityMonitor

　　可以通过WaitType,BlockedBy栏位查看到,SPID54被SPID53阻塞.可以右键Details查到详细的SQL语句,或Kill掉这个进程.

　　2.2SQLServer提供几个DMV,查看locks

　　sys.dm_exec_requests

　　sys.dm_tran_locks

　　sys.dm_os_waiting_tasks

　　sys.dm_tran_database_transactions

（1）

select*fromsys.dm_tran_lockswhere  resource_type<>'DATABASE'--andresource_database_id=DB_ID（）

（2）

SELECTsession_id,blocking_session_id,*

FROMsys.dm_exec_requests

WHEREblocking_session_id>0

  　　（3）

SELECTrequest_session_idasSpid,

Coalesce（s.name+'.'+o.name+isnull（'.'+i.name,''）,

s2.name+'.'+o2.name,db.name）

ASObject,

l.resource_typeasType,

request_modeasMode,

request_statusasStatus

FROMsys.dm_tran_locksl

LEFTJOINsys.partitionsp

ONl.resource_associated_entity_id=p.hobt_id

LEFTJOINsys.indexesi

ONp.object_id=i.object_idANDp.index_id=i.index_id

LEFTJOINsys.objectso

ONp.object_id=o.object_id

LEFTJOINsys.schemassONo.schema_id=s.schema_idLEFTJOINsys.objectso2

ONl.resource_associated_entity_id=o2.object_id

LEFTJOINsys.schemass2

ONo2.schema_id=s2.schema_id

LEFTJOINsys.databasesdb

ONl.resource_database_id=db.database_id

WHEREresource_database_id=DB_ID（）

ORDERBYSpid,Object,CASEl.resource_typeWhen'database'Then1

when'object'then2

when'page'then3

when'key'then4

Else5end

 　　利用系统动态视图sys.dm_tran_locks查看到,重要的栏位如下:

　　resource_type被锁的资源类型（Database,FILE,Object,PAGE,KEY,EXTENT,RID,APPLICATION,METADATA,HOBT,APPOCATION_UNIT）

　　request_mode锁的类型（共享锁,更新锁,排它锁,架构锁等）

　　resource_description资源描述

　　request_session_idRequestsessionID　

一:

下面以AdventureWorks2008为示例数据库做简要的说明,过滤掉一般的数据库的共享锁,作为示例必须要看到锁,所以用WITH（HOLDLOCK）来保持锁.

　　1.Sharedlocks（S）共享锁

USEAdventureWorks2008

BEGINTRAN

select*fromSales.SalesOrderHeaderWITH（HOLDLOCK）

whereSalesOrderID='43662'

SELECTresource_type,request_mode,resource_description,request_session_id,DB_NAME（resource_database_id）asresource_database

FROM  sys.dm_tran_locks

WHERE  resource_type<>'DATABASE'

--ROLLBACKTRAN

　　在事务回滚之前,查看锁的类型:

　　其他session对Table只读,不能更新,在开一个新的session测试:

select*fromSales.SalesOrderHeader  whereSalesOrderID='43662'

go

updateSales.SalesOrderHeadersetOrderDate=GETDATE（）whereSalesOrderID='43662'

　　select可以正常执行,update语句一直处于等待状态,等待上面的session释放锁.

　　2.Updatelocks（U）:

更新锁是共享锁和独占锁的组合.用UPDLOCK保持更新锁

USEAdventureWorks2008

BEGINTRAN

select*fromSales.SalesOrderHeaderWITH（UPDLOCK）

whereSalesOrderID='43662'

SELECTresource_type,request_mode,resource_description,request_session_id,DB_NAME（resource_database_id）asresource_database

FROM  sys.dm_tran_locks

WHERE  resource_type<>'DATABASE'

ROLLBACKTRAN

　　查看到锁的信息:

　　3.Exclusivelocks（X）:

独占锁是为了锁定数据被一个session修改的数据,而不能够被另外的session修改.只能指定NOLOCK来读取.

USEAdventureWorks2008

BEGINTRAN

updateSales.SalesOrderHeadersetShipDate=GETDATE（）whereSalesOrderID='43662'

SELECTresource_type,request_mode,resource_description,request_session_id,DB_NAME（resource_database_id）asresource_database--,*

FROM  sys.dm_tran_locks

WHERE  resource_type<>'DATABASE'

ROLLBACKTRAN

　　查看锁:

　　4.Intentlocks（I）:

意向锁用于建立锁的层次结构.意向锁包含三种类型：

意向共享（IS）、意向排他（IX）和意向排他共享（SIX）。

　　数据库引擎使用意向锁来保护共享锁（S锁）或排他锁（X锁）放置在锁层次结构的底层资源上。

意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

　　意向锁有两种用途：

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

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

5.Schemalocks（Sch）:

架构锁

　　Schemastabilitylock（Sch-S）:

保持架构稳定性,用在生成执行计划时,不会阻止对数据的访问.

　　Schemamodificationlock（Sch-M）:

用在DDL操作时.当架构正在被改变时,阻止对对象数据的访问.

USEAdventureWorks2008

BEGINTRAN

CREATETABLEMyTable（IDINT,NAMEVARCHAR（20）,COUNTRYVARCHAR（15））

SELECTresource_type,request_mode,resource_description

FROM  sys.dm_tran_locks

WHERE  resource_type<>'DATABASE'orderbyrequest_mode

ROLLBACKTRAN

　　6.BulkUpdatelocks（BU）

　　数据库引擎在将数据大容量复制到表中时使用了大容量更新（BU）锁,并指定了TABLOCK提示或使用sp_tableoption设置了tablelockonbulkload表选项.大容量更新锁（BU锁）允许多个线程将数据并发地大容量加载到同一表,同时防止其他不进行大容量加载数据的进程访问该表.

　　7.Key-Rangelocks

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

　　二:

死锁与死锁解除

　　1.死锁

　　使用或管理数据库都不可避免的涉及到死锁.一旦发生死锁,数据相互等待对方资源的释放,会阻止对数据的访问,严重会造成DB挂掉.当资源被锁定,无法被访问时,可以终止访问DB的那个session来达到解锁的目的（即Kill掉造成锁的那个进程）.

　　在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。

例如：

　　事务A获取了行1的共享锁。

　　事务B获取了行2的共享锁。

　　现在，事务A请求行2的排他锁，但在事务B完成并释放其对行2持有的共享锁之前被阻塞。

　　现在，事务B请求行1的排他锁，但在事务A完成并释放其对行1持有的共享锁之前被阻塞。

　　事务B完成之后事务A才能完成，但是事务B由事务A阻塞。

该条件也称为循环依赖关系：

事务A依赖于事务B，事务B通过对事务A的依赖关系关闭循环。

　　除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。

MicrosoftSQLServer数据库引擎死锁监视器定期检查陷入死锁的任务。

如果监视器检测到循环依赖关系，将选择其中一个任务作为牺牲品，然后终止其事务并提示错误。

这样，其他任务就可以完成其事务。

对于事务以错误终止的应用程序，它还可以重试该事务，但通常要等到与它一起陷入死锁的其他事务完成后执行。

　　2.死锁检测

　　2.1SQLServer数据库引擎自动检测SQLServer中的死锁循环。

数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务（出现错误）来打断死锁。

　　2.2查看DMV:

sys.dm_tran_locks

　　2.3SQLServerProfiler能够直观的显示死锁的图形事件.

　　三:

锁兼容性

　　锁兼容性控制多个事务能否同时获取同一资源上的锁。

如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。

如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。

例如，没有与排他锁兼容的锁模式。

如果具有排他锁（X锁），则在释放排他锁（X锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。

另一种情况是，如果共享锁（S锁）已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U锁）。

但是，在释放共享锁之前，其他事务无法获取排他锁。

　　四:

总结

　　锁的原理比较抽象,对用户来说是透明的,不用过多的关注.应用程序一般不直接请求锁.锁由数据库引擎的一个部件（称为“锁管理器”）在内部管理.当数据库引擎实例处理Transact-SQL语句时,数据库引擎查询处理器会决定将要访问哪些资源.查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型.然后,查询处理器将向锁管理器请求适当的锁.如果与其他事务所持有的锁不会发生冲突,锁管理器将授予该锁.

文章2：

sqlserver锁表语句分享，需要的朋友可以参考下

锁定数据库的一个表

SELECT*FROMtableWITH（HOLDLOCK）

注意:

锁定数据库的一个表的区别

SELECT*FROMtableWITH（HOLDLOCK）

其他事务可以读取表，但不能更新删除

SELECT*FROMtableWITH（TABLOCKX）

其他事务不能读取表,更新和删除

SELECT语句中“加锁选项”的功能说明

SQLServer提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。

用户既能使用SQLServer的缺省设置也可以在select语句中使用“加锁选项”来实现预期的效果。

本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。

功能说明：

NOLOCK（不加锁）

此选项被选中时，SQLServer在读取或修改数据时不加任何锁。

在这种情况下，用户有可能读取到未完成事务（UncommitedTransaction）或回滚（RollBack）中的数据,即所谓的“脏数据”。

HOLDLOCK（保持锁）

此选项被选中时，SQLServer会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）

此选项被选中时，SQLServer在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。

使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）

此选项被选中时，SQLServer将在整个表上置共享锁直至该命令结束。

这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）

此选项为默认选项，当被选中时，SQLServer使用共享页锁。

TABLOCKX（排它表锁）

此选项被选中时，SQLServer将在整个表上置排它锁直至该命令或事务结束。

这将防止其他进程读取或修改表中的数据。

HOLDLOCK持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

　　NOLOCK语句执行时不发出共享锁，允许脏读，等于READUNCOMMITTED事务隔离级别

　　PAGLOCK在使用一个表锁的地方用多个页锁

　　READPAST让sqlserver跳过任何锁定行，执行事务，适用于READUNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

　　ROWLOCK强制使用行锁

　　TABLOCKX强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

　　UPLOCK强制在读表时使用更新而不用共享锁

注意:

锁定数据库的一个表的区别

　　SELECT*FROMtableWITH（HOLDLOCK）其他事务可以读取表，但不能更新删除

　　SELECT*FROMtableWITH（TABLOCKX）其他事务不能读取表,更新和删

详细出处参考：