從wait_type入手類比SQL Server Lock

標籤：

一、LCK_M_S，等待擷取共用鎖定

開始一SQL TRAN，其中執行對某資料的UPDATE。但並不COMMIT，也不ROLLBACK。

begin tranupdate [dbo].[HR_Employee] set [Description]=‘ZZ‘

這樣，便使用排它鎖鎖定了該[Employee]表。

 

在另一會話中，執行對該表的SELECT操作。至此，死結產生。

select * from [dbo].[HR_Employee]

 

使用下列script查詢當前鎖情況。

 1 SELECT wt.blocking_session_id                    AS BlockingSessesionId 2         ,sp.program_name                           AS ProgramName 3         ,COALESCE(sp.LOGINAME, sp.nt_username)     AS HostName     4         ,ec1.client_net_address                    AS ClientIpAddress 5         ,db.name                                   AS DatabaseName         6         ,wt.wait_type                              AS WaitType                     7         ,ec1.connect_time                          AS BlockingStartTime 8         ,wt.WAIT_DURATION_MS/1000                  AS WaitDuration 9         ,ec1.session_id                            AS BlockedSessionId10         ,h1.TEXT                                   AS BlockedSQLText11         ,h2.TEXT                                   AS BlockingSQLText12   FROM sys.dm_tran_locks AS tl13   INNER JOIN sys.databases db14     ON db.database_id = tl.resource_database_id15   INNER JOIN sys.dm_os_waiting_tasks AS wt16     ON tl.lock_owner_address = wt.resource_address17   INNER JOIN sys.dm_exec_connections ec118     ON ec1.session_id = tl.request_session_id19   INNER JOIN sys.dm_exec_connections ec220     ON ec2.session_id = wt.blocking_session_id21   LEFT OUTER JOIN master.dbo.sysprocesses sp22     ON SP.spid = wt.blocking_session_id23   CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h124   CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

發現該LOCK的wait_type為LCK_M_S，意味著後一會話在等待著擷取對該表的共用鎖定已完成查詢工作。

 

二、LCK_M_U，等待擷取更新鎖定。

發起一SQL會話，在其中使用更新鎖定（UPDLOCK）SELECT資料，而後WAIT一定的時間。

1 begin tran2 select * from [dbo].[HR_Employee] WITH (UPDLOCK) where [Id]=73 waitfor delay ‘00:01:00‘ 4 update [dbo].[HR_Employee] set [Description]=‘ZZ‘ where [Id]=75 commit tran

在wait的時間內，[Id]=7的行被更新鎖定鎖住。

 

發起另一會話，使用更新鎖定（UPDLOCK）完成SELECT操作。

1 select * from [dbo].[HR_Employee] WITH (UPDLOCK)

 

發現後一會話被block。wait_type為LCK_M_U，表示其在等待該表的更新鎖定。

 

三、LCK_M_X，等待擷取排它鎖

將上一小節中第二個會話的操作改為UPDATE。

update [dbo].[HR_Employee] set [Description]=‘ZZy‘ where [Id]=7

後一會話同樣被block，但這次的wait_type為LCK_M_X，表明其在等待用於UPDATE DATA的排它鎖。

從wait_type入手類比SQL Server Lock