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. 举报

相关文章推荐

T-SQL Part VIII: CROSS APPLY, OUTER APPLY

除了CROSS JOIN, INNER JOIN, OUTER JOIN之外,T-SQL还提供了CROSS APPLY和OUTER APPLY这两个较为另类的Set操作符。首先来看CROSS APPL...

T-SQL Part IX, PIVOT and UNPIVOT

不同于CROSS JOIN, CROSS APPLY, OUTER APPLY,MSDN文档对PIVOT和UNPIVOT 想得重视了一点,单独做了一个页面来介绍。 简单来说,PIVOT用来把行转成列...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

SQL调优公式T=S/V

比较愚钝,从事调优工作这么些年,处理过的性能问题也不少,也就总结出这么个公式来 S指sql所需访问的资源总量,V指sql单位时间所能访问的资源量,T自然就是SQL执行所需时间了 这个公式很简单,调优的...

.Net下的线程同步:Part 4 of N--(Locks or Critical sections)

Locks(或者Critical sections)         锁定是一种一次只允许一个线程进入特定代码区段的机制,通过加锁实现。被锁定的代码区段称为critical section(关键区域...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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