锁定

 

锁定

 

一并发控制模型

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.