SQL Server数据库表锁定原理以及如何解除锁定

日期: 2010-08-03 来源:TechTarget中国

  1. 数据库表锁定原理

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

  

  1.2 事务的ACID原则

  

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

  1.3.1 SQL Server中可以锁定的资源:

  

  1.3.2 锁的粒度:

  

  1.3.3 锁的升级:

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

  1.3.4 锁的类型:

  (1) 共享锁:

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

  (2) 修改锁:

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

  (3) 独占锁:

  独占锁是为修改数据而保留的。它所锁定的资源,其他事务不能读取也不能修改。独占锁不能和其他锁兼容。

  (4) 架构锁

  结构锁分为结构修改锁(Sch-M)和结构稳定锁(Sch-S)。执行表定义语言操作时,SQL Server采用Sch-M锁,编译查询时,SQL Server采用Sch-S锁。

  (5) 意向锁

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

  (6) 批量修改锁

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

  1.3.4 SQL Server锁类型

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

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

  (3) PAGLOCK:指定添加页锁(否则通常可能添加表锁)。

  (4) READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL Server 2000 在此隔离级别上操作。

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

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

  (6) READUNCOMMITTED:等同于NOLOCK。

  (7) REPEATABLEREAD:设置事务为可重复读隔离性级别。

  (8) ROWLOCK:使用行级锁,而不使用粒度更粗的页级锁和表级锁。

  (9) SERIALIZABLE:用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。

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

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

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

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

  2.1 Activity Monitor

  

  可以通过Wait Type, Blocked By栏位查看到,SPID 54 被SPID 53 阻塞. 可以右键Details查到详细的SQL 语句,或Kill掉这个进程.

  2.2 SQL Server提供几个DMV,查看locks

  sys.dm_exec_requests

  sys.dm_tran_locks

  sys.dm_os_waiting_tasks

  sys.dm_tran_database_transactions

  (1)

  select * from sys.dm_tran_locks where resource_type<>’DATABASE’ –and resource_database_id=DB_ID()

  

  (2)

  SELECT session_id, blocking_session_id,*

  FROM sys.dm_exec_requests

  WHERE blocking_session_id > 0

  

  (3)

  SELECT

  request_session_id as Spid,

  Coalesce(s.name + ‘.’ + o.name + isnull(‘.’ + i.name,”),

  s2.name + ‘.’ + o2.name,

  db.name) AS Object,

  l.resource_type as Type,

  request_mode as Mode,

  request_status as Status

  FROM sys.dm_tran_locks l

  LEFT JOIN sys.partitions p

  ON l.resource_associated_entity_id = p.hobt_id

  LEFT JOIN sys.indexes i

  ON p.object_id = i.object_id

  AND p.index_id = i.index_id

  LEFT JOIN sys.objects o

  ON p.object_id = o.object_id

  LEFT JOIN sys.schemas s

  ON o.schema_id = s.schema_id

  LEFT JOIN sys.objects o2

  ON l.resource_associated_entity_id = o2.object_id

  LEFT JOIN sys.schemas s2

  ON o2.schema_id = s2.schema_id

  LEFT JOIN sys.databases db

  ON l.resource_database_id = db.database_id

  WHERE resource_database_id = DB_ID()

  ORDER BY Spid, Object, CASE l.resource_type

  When ‘database’ Then 1

  when ‘object’ then 2

  when ‘page’ then 3

  when ‘key’ then 4

  Else 5 end

  

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

相关推荐