[sql] 테이블에 고정 된 잠금을 확인하는 방법

쿼리 일괄 처리와 비교하여 어떤 데이터베이스 잠금이 어떤 행에 적용되는지 확인할 수 있습니까?

실시간으로 테이블 행 레벨 잠금을 강조하는 도구가 있습니까?

DB : SQL Server 2005

답변

다른 응답에 추가하기 위해 sp_lock실행중인 모든 프로세스에서 전체 잠금 정보를 덤프하는 데 사용될 수도 있습니다. 출력이 압도적 일 수 있지만 잠긴 항목을 정확히 알고 싶다면 실행하는 것이 좋습니다. 나는 보통 함께 사용sp_who2 으로 잠금 문제를 신속하게 제로화하기 위해 .

sp_lock해당 SQL Server 버전에 따라 온라인에서 사용 가능한 여러 가지 “친숙한” 프로 시저 버전이 있습니다.

귀하의 경우 SQL Server 2005의 경우 sp_lock여전히 사용할 수 있지만 더 이상 사용되지 않으므로 이제 sys.dm_tran_locks이런 종류의보기 를 사용하는 것이 좋습니다 . sp_lock 함수를 “roll your own”하는 방법에 대한 예제를 여기에서 찾을 수 있습니다 .

답변

이것은 어떤 행이 잠겨 있는지 정확하게 보여주지는 않지만 도움이 될 수 있습니다.

다음을 실행하여 차단 된 명령문을 확인할 수 있습니다.

select cmd,* from sys.sysprocesses
where blocked > 0

또한 각 블록이 무엇을 기다리고 있는지 알려줄 것입니다. 따라서 어떤 블록이 다른 블록을 일으킨 첫 번째 블록을 일으켰는지 확인할 수 있습니다.

@MikeBlandford의 의견을 추가하려면 편집하십시오 .

차단 된 열은 차단 프로세스의 spid를 나타냅니다. kill {spid}를 실행하여 문제를 해결할 수 있습니다.

답변

다음 쿼리 를 통해 테이블에서 현재 잠금을 찾을 수 있습니다 .

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

sys.dm_tran_locks를 참조하십시오

동일한 request_owner_type 의 여러 인스턴스가 존재하면 request_owner_id 열을 사용하여 각 인스턴스를 구별합니다. 분산 트랜잭션의 경우 request_owner_type 및 request_owner_guid 열에 다른 엔터티 정보가 표시됩니다.

예를 들어, 세션 S1은 Table1에서 공유 잠금을 소유합니다. 세션 S1에서 실행중인 트랜잭션 T1도 Table1에서 공유 잠금을 소유합니다. 이 경우 sys.dm_tran_locks에 의해 반환되는 resource_description 열에 는 동일한 리소스의 두 인스턴스가 표시됩니다. request_owner_type의 열은 하나 개의 세션으로 인스턴스와 트랜잭션으로 다른 표시됩니다. 또한 resource_owner_id 열은 다른 값을 갖습니다.

답변

DMV (Dynamic Management View)를 사용하여 잠금뿐만 아니라 잠긴 항목의 object_id 또는 partition_id를 캡처합니다.

(MUST는 object_id를 얻기 위해 관찰하려는 데이터베이스로 전환합니다)

SELECT
     TL.resource_type,
     TL.resource_database_id,
     TL.resource_associated_entity_id,
     TL.request_mode,
     TL.request_session_id,
     WT.blocking_session_id,
     O.name AS [object name],
     O.type_desc AS [object descr],
     P.partition_id AS [partition id],
     P.rows AS [partition/page rows],
     AU.type_desc AS [index descr],
     AU.container_id AS [index/page container_id]
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_os_waiting_tasks AS WT
 ON TL.lock_owner_address = WT.resource_address
LEFT OUTER JOIN sys.objects AS O
 ON O.object_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions AS P
 ON P.hobt_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units AS AU
 ON AU.allocation_unit_id = TL.resource_associated_entity_id;

답변

내장 sp_who2저장 프로 시저를 사용하여 SQL Server 인스턴스에서 현재 차단 및 차단 프로세스를 가져올 수도 있습니다 . 일반적으로이를 SQL 프로파일 러 인스턴스와 함께 실행하여 차단 프로세스를 찾고 spid가 프로파일 러에서 실행 한 최신 명령을 확인합니다.

답변

아래 스크립트를 통해 세부 정보를 찾을 수 있습니다.

-- List all Locks of the Current Database 
SELECT TL.resource_type AS ResType
      ,TL.resource_description AS ResDescr
      ,TL.request_mode AS ReqMode
      ,TL.request_type AS ReqType
      ,TL.request_status AS ReqStatus
      ,TL.request_owner_type AS ReqOwnerType
      ,TAT.[name] AS TransName
      ,TAT.transaction_begin_time AS TransBegin
      ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
      ,ES.session_id AS S_Id
      ,ES.login_name AS LoginName
      ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
      ,PARIDX.name AS IndexName
      ,ES.host_name AS HostName
      ,ES.program_name AS ProgramName
FROM sys.dm_tran_locks AS TL
     INNER JOIN sys.dm_exec_sessions AS ES
         ON TL.request_session_id = ES.session_id
     LEFT JOIN sys.dm_tran_active_transactions AS TAT
         ON TL.request_owner_id = TAT.transaction_id
            AND TL.request_owner_type = 'TRANSACTION'
     LEFT JOIN sys.objects AS OBJ
         ON TL.resource_associated_entity_id = OBJ.object_id
            AND TL.resource_type = 'OBJECT'
     LEFT JOIN sys.partitions AS PAR
         ON TL.resource_associated_entity_id = PAR.hobt_id
            AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
     LEFT JOIN sys.objects AS PAROBJ
         ON PAR.object_id = PAROBJ.object_id
     LEFT JOIN sys.indexes AS PARIDX
         ON PAR.object_id = PARIDX.object_id
            AND PAR.index_id = PARIDX.index_id
WHERE TL.resource_database_id  = DB_ID()
      AND ES.session_id <> @@Spid -- Exclude "my" session 
      -- optional filter  
      AND TL.request_mode <> 'S' -- Exclude simple shared locks 
ORDER BY TL.resource_type
        ,TL.request_mode
        ,TL.request_type
        ,TL.request_status
        ,ObjectName
        ,ES.login_name;



--TSQL commands
SELECT
       db_name(rsc_dbid) AS 'DATABASE_NAME',
       case rsc_type when 1 then 'null'
                             when 2 then 'DATABASE'
                             WHEN 3 THEN 'FILE'
                             WHEN 4 THEN 'INDEX'
                             WHEN 5 THEN 'TABLE'
                             WHEN 6 THEN 'PAGE'
                             WHEN 7 THEN 'KEY'
                             WHEN 8 THEN 'EXTEND'
                             WHEN 9 THEN 'RID ( ROW ID)'
                             WHEN 10 THEN 'APPLICATION' end  AS 'REQUEST_TYPE',

       CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
                                     WHEN 2 THEN 'CURSOR'
                                     WHEN 3 THEN 'SESSION'
                                     WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',

       OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME',
       PROCESS.HOSTNAME ,
       PROCESS.program_name ,
       PROCESS.nt_domain ,
       PROCESS.nt_username ,
       PROCESS.program_name ,
       SQLTEXT.text
FROM sys.syslockinfo LOCK JOIN
     sys.sysprocesses PROCESS
  ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT
where 1=1
and db_name(rsc_dbid) = db_name()



--Lock on a specific object
SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = object_id('Specific Table');