T-SQL Part V: Locks

原创 2016年05月30日 16:50:37

写SQL最常见的问题就是Dead Lock了。本篇简单介绍入门级别的Lock使用和排查。

首先来看MSDN上的官方文档(https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx)。

摘要一下,SQL Server可以进行Lock的Resource:

Resource Description
RID A row identifier used to lock a single row within a heap.
KEY A row lock within an index used to protect key ranges in serializable transactions.
PAGE An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENT A contiguous group of eight pages, such as data or index pages.
HoBT A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLE The entire table, including all data and indexes.
FILE A database file.
APPLICATION An application-specified resource.
METADATA Metadata locks.
ALLOCATION_UNIT An allocation unit.
DATABASE The entire database.

Lock的类型:

Lock mode Description
Shared (S) Used for read operations that do not change or update data, such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

T-SQL中,使用Lock最简单的方法当然是SELECT ... FOR UPDATE,选中对应的ROW进行Lock以便Update。


进行Lock排查,可以通过以下方式进行查看当前Lock的状态:

  • exec sp_lock; 这是最原始的方式,dump所有Lock相关的信息。
  • select cmd,* from sys.sysprocesseswhere blocked > 0通过查看当前sysprocesses的方式来抉择那些process被blocked。配合上exec sp_who2和kill,分别用来查看process的信息和终止指定的process。
  • select * from sys.dm_tran_locks; Dynamic View dm_trans_locks返回当前系统中的locks。Dynamic Views and Functions请参阅:https://msdn.microsoft.com/en-us/library/ms188754.aspx
是为之记。
Alva Chien
2016.5.30
版权声明:© 2004 - 2016 Alva Chien, All Rights Reserved.

orcle 11g select count(*) from v$lock 引起的思索

orcle 11g select count(*) from v$lock 引起的思索
  • u012007236
  • u012007236
  • 2014年12月30日 22:40
  • 1115

Delphi对象模型(Part V)

 Delphi对象模型 (PART V)  Delphi对于面向对象编程的支持丰富而且强大。除了传统的类和对象,Delphi还提供了接口,异常处理,多线程编程等特性。这一章节深入讲解了Delphi的对...
  • Musicwind
  • Musicwind
  • 2001年10月22日 16:43
  • 1023

Eiffel 引介 Part V

Eiffel IntroductionEiffel 引介 Rensselaer, 2000James C. McKim, Jr, Rensselaer at Hartford K ][ N G o...
  • hejishan
  • hejishan
  • 2008年04月08日 23:33
  • 180

Linux之软件包管理

一,包管理器 1.1 二进制应用程序的组成部分:  二进制文件,库文件,配置文件,帮助文件 1.2 程序包管理器   debian : deb文件,dpkg包管...
  • ly1358152944
  • ly1358152944
  • 2016年08月24日 19:57
  • 1001

Error in acquiring locks: Locks on the underlying objects cannot be acquired. retry after some time

今天有一个任务报错: Unable to acquire IMPLICIT, SHARED lock db_ecar@bd_dw_terminal_id_city_info after 100 a...
  • wer0735
  • wer0735
  • 2017年12月06日 14:56
  • 128

hive锁

hive lock报错 FAILED: Error in acquiring locks: Locks on the underlying objects cannot be acquired....
  • wer0735
  • wer0735
  • 2017年10月25日 17:36
  • 359

T-SQL, Part I: LIKE Pattern

The basic usage of LIKE pattern: %: it would be placed at the end and/or the beginning of a str...
  • alvachien
  • alvachien
  • 2016年03月27日 15:06
  • 268

T-SQL Part VII: CROSS JOIN

虽然不能确定是不是只有个SQL Server提供了Cross Join的功能,貌似W3School的SQL教程中是没有的 SQL教程。而Wikipedia中倒是有,也是最新的SQL:2011SQL:2...
  • alvachien
  • alvachien
  • 2016年06月13日 17:51
  • 211

ASE out of locks 重现和解决探究

lab1   同时delete一张row lock表,监控锁状况
  • lileizhang
  • lileizhang
  • 2014年04月21日 16:47
  • 1486

解决 Studio 使用svn 的时候提示Run 'svn cleanup' to remove locks (type 'svn help cleanup' for details)

  • amor5216
  • amor5216
  • 2018年02月08日 11:12
  • 2966
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:T-SQL Part V: Locks
举报原因:
原因补充:

(最多只允许输入30个字)