锁定
一并发控制模型
1 悲观并发控制
2 乐观并发控制
二事务控制
ACID属性：Atomicity（原子性），Consistency（一致性），Isolation（隔离性），Durability（持续性）；
三隔离级别
Uncommitted Read（未提交读）
Read Committed（已提交读）
Repeatable Read（可重复读）
Snapshot（快照）
Serializable（可串行化）
四锁
1 概念
锁（lock）保证数据的逻辑一致性；
闩（latch）保证物理一致性；供SQL Server内部使用；
自旋锁（Spinlocks）为互斥而设计的；
闩比用来锁定数据和索引叶子分页的完全锁更加轻量；
自旋锁比闩和锁更加轻量；
2 锁的模式
Abbreviation Lock Mode
S Shared
X Exclusive
U Update
IS Intent shared
IU Intent update
IX Intent exclusive
SIX Shared with intent exclusive
SIU Shared with intent update
UIX Update with intent exclusive
Sch-S Schema stability
Sch-M Schema modification
BU Bulk update
3 锁定的资源
表示资源类型：DATABASE、FILE、OBJECT、PAGE、KEY、EXTENT、RID、APPLICATION、METADATA、HOBT 或ALLOCATION_UNIT。
4 查看视图sys.dm_tran_locks
CREATE VIEW DBlocks AS
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN
object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.hobt_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
SELECT
request_session_id,
resource_type,
DB_NAME(resource_database_id) AS DatabaseName,
OBJECT_NAME(resource_associated_entity_id) AS TableName,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks AS TL
JOIN sys.all_objects AS AO
ON TL.resource_associated_entity_id = AO.object_id
WHERE request_type = 'LOCK'
AND request_status = 'GRANT'
AND request_mode IN ('X','S')
AND AO.type = 'U'
AND resource_type = 'OBJECT'
AND TL.resource_database_id = DB_ID();
5 锁兼容
某个进程正在资源上申请锁，如果该锁可以在一个不同的进程已经占有同已资源上的另一个
锁时被授权，那么称这两个锁是兼容的；如果一个申请资源的锁与另一个当前正在被持有的锁是
不兼容的，那么正在请求的连接就必须等待锁被释放。
6 锁升级和禁用锁升级
7 锁定提示
SELECT select_list
FROM object [WITH (locking hint)]
DELETE [FROM] object [WITH (locking hint)
[WHERE <search conditions>]
UDPATE object [WITH (locking hint)
SET <set_clause>
[WHERE <search conditions>]
INSERT [INTO] object [WITH (locking hint)
<insert specification>
HOLDLOCK
UPDLOCK 可以消除转换死锁
TABLOCK 与DELETE语句一起使用，能够使SQL Server在行被删除时回收分页（如果在堆上进行删除时
获取了行级锁或者分页锁，这些空间就不会被回收且无法被其它对象使用）
PAGLOCK
TABLOCKX
ROWLOCK
READUNCOMMITTED | REPEATABLEREAD | SERIALIZABLE
READCOMMITTED
READCOMMITTEDLOCK
NOLOCK
READPAST 用在数据表作为队列情况下，与（top 1）语句结合使用
XLOCK
--设定锁超时
SET LOCK_TIMEOUT 5000;
SELECT @@LOCK_TIMEOUT;
8 锁，阻塞和死锁的区别：
锁是一种基本的并发机制；阻塞是指一个任务被迫等待被另外一个锁住的资源；
死锁是指两个任务彼此互相阻塞；
普通的锁等待并不是一种死锁。当持有锁的进程完成以后，等待的进程就能
够获取锁了。在多用户系统中，锁等待是正常的，预期的和必然的。
9 等待
（）等待两种情况：
许多进程等待同一个资源；阻塞链；
（）检测等待
性能计数器：SQLServer:General Statistics（Lock Waits ）；SQLServer:Wait Statistics
DMV视图：sys.dm_os_waiting_tasks sys.dm_tran_locks
sp_who sp_who2 查看 blk 和BlkBy列
sysprocesses
DBCC INPUTBUFFER
sys.dm_exec_requests
--查看持续时间大于秒
SELECT
WT.session_id AS waiting_session_id,
WT.waiting_task_address,
WT.wait_duration_ms,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
WHERE WT.wait_duration_ms > 5000;
--pick out each waiting and granted lock for each given resource
SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
TL1.resource_associated_entity_id,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks as TL1
JOIN sys.dm_tran_locks as TL2
ON TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
AND TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL AND TL2.resource_description IS NULL))
ORDER BY TL1.request_status ASC;
SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
CASE TL1.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id,
TL1.resource_database_id)
WHEN 'DATABASE' THEN 'DATABASE'
ELSE
CASE
WHEN TL1.resource_database_id = DB_ID() THEN
(SELECT OBJECT_NAME(object_id, TL1.resource_database_id)
FROM sys.partitions
WHERE hobt_id = TL1.resource_associated_entity_id)
ELSE NULL
END
END AS ObjectName,
TL1.resource_description,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks AS TL1
JOIN sys.dm_tran_locks AS TL2
ON TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
WHERE TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL
AND TL2.resource_description IS NULL))
ORDER BY TL1.resource_database_id,
TL1.resource_associated_entity_id,
TL1.request_status ASC;
--Trace
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold', 60;
RECONFIGURE;
--Errors and Warnings组Blocked Process Report 事件类指明某个任务已被阻塞，导致超过指定的时间。此事件类不包括系统任务和正在等待未发现死锁的资源的任务。
--SQLDiag
Server>__sp_perf_stats09_Startup.OUT -- headblockersummary --
--查看阻塞任务
SELECT
WT.session_id AS waiting_session_id,
DB_NAME(TL.resource_database_id) AS DatabaseName,
WT.wait_duration_ms,
WT.waiting_task_address,
TL.request_mode,
(SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests AS ER
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE ER.session_id = TL.request_session_id)
AS waiting_query_text,
TL.resource_type,
TL.resource_associated_entity_id,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description AS blocking_resource_description,
CASE WHEN WT.blocking_session_id > 0 THEN
(SELECT ST2.text FROM sys.sysprocesses AS SP
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
WHERE SP.spid = WT.blocking_session_id)
ELSE NULL
END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS WT
JOIN sys.dm_tran_locks AS TL
ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
AND WT.session_id > 50;
----------------------
--10 死锁
（）死锁分类：读/写循环死锁,写/写循环死锁，转换死锁；
（）检测：
性能计数器：SQLServer:Locks Number of Deadlocks/Sec
Trace: Lock:Deadlock , Lock:Deadlock Chain
Deadlock graph event
DBCC TRACEON (3605, 1222, -1)
--1 process A:
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.9
WHERE ProductID = 922;
--2 process B:
BEGIN TRAN
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = OrderQty + 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
--3 process A:
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = OrderQty - 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
--4 process B:
UPDATE Production.Product
SET ListPrice = ListPrice * 0.9
WHERE ProductID = 922;
--列出最初锁住资源，导致一连串其他进程被锁住的起始源头
IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid
IN (SELECT blocked FROM master.sys.sysprocesses)) --确定有进程被其他的进程锁住
SELECT spid 进程,status 状态, 登入帐号=SUBSTRING(SUSER_SNAME(sid),1,30),
使用者机器名称=SUBSTRING(hostname,1,12), 是否被锁住=CONVERT(char(3),blocked),
数据库名称= SUBSTRING(DB_NAME(dbid),1,20),cmd 命令,waittype 等待型态
FROM master.sys.sysprocesses
--列出锁住别人(在别的进程中blocked字段出现的值)，但自己未被锁住(blocked=0)
WHERE spid IN (SELECT blocked FROM master.sys.sysprocesses)
AND blocked=0
ELSE
SELECT '没有进程被锁住'
-- 查看锁的链接关系
select t1.resource_type as [资源锁定类型]
,db_name(resource_database_id) as [数据库名]
,t1.resource_associated_entity_id as [锁定的对象]
,t1.request_mode as [等待者需求的锁定类型]
,t1.request_session_id as [等待者sid]
,t2.wait_duration_ms as [等待时间]
,(select text from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as [等待者要执行的批次]
,(select substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as [等待者正要执行的语法]
,t2.blocking_session_id as [锁定者sid]
,(select text from sys.sysprocesses as p
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as [锁定者的语法]
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
--11 解决死锁方法：
Determine whether a short-term or long-term solution is required.
Isolate the deadlocking code and reproduce it in a test setting.
Check the granularity and amount of locking in the deadlocks.
Check for missing indexes.
Shorten the transactions.
Retry the transaction if it is a deadlock victim.
Lower the deadlock priority of one process.
Use bound connections.