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

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

쿼리 배치에 대해 어떤 행에 어떤 데이터베이스 잠금이 적용되는지 어떻게 확인할 수 있습니까?

테이블 열 수준 잠금을 실시간으로 강조하는 도구가 있습니까?

DB: SQL Server 2005

잠긴 행이 정확히 표시되지는 않지만 도움이 될 수 있습니다.

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

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

또한 각 블록이 무엇을 기다리고 있는지 알려줍니다.따라서 어떤 문이 다른 블록을 유발한 첫 번째 블록을 발생시켰는지 확인하기 위해 위까지 추적할 수 있습니다.

@MikeBlandford의 의견을 추가하려면 편집:

블럭화된 열은 블럭화 공정의 범위를 나타냅니다.kill {spid}을(를) 실행하여 수정할 수 있습니다.

다른 반응에 추가하려면,sp_lock실행 중인 모든 프로세스에 대한 전체 잠금 정보를 덤프하는 데도 사용할 수 있습니다.출력이 압도적일 수 있지만, 무엇이 잠겨 있는지 정확히 알고 싶다면 실행하는 것이 좋습니다.저는 보통 그것을 함께 사용합니다.sp_who2잠금 문제를 빠르게 해결할 수 있습니다.

"친환경"에는 여러 가지 버전이 있습니다.sp_lock문제의 SQL Server 버전에 따라 온라인에서 사용할 수 있는 절차입니다.

SQL Server 2005의 경우sp_lock를 계속 사용할 수 있지만 더 이상 사용하지 않기 때문에 이제는 보기를 사용하는 것이 좋습니다.여기에서 sp_lock 함수를 "나만의" 롤링하는 방법의 예를 찾을 수 있습니다.

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

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_http://locks 을 참조하십시오.

동일한 request_owner_type의 인스턴스가 여러 개 있는 경우 request_owner_id 열을 사용하여 각 인스턴스를 구분합니다.분산 트랜잭션의 경우 request_owner_type 열과 request_owner_guid 열에 서로 다른 엔티티 정보가 표시됩니다.

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

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

(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;

자세한 내용은 아래 스크립트를 통해 확인할 수 있습니다.

-- 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');

기본 제공 기능을 사용할 수도 있습니다.sp_who2SQL Server 인스턴스에서 현재 차단 및 차단 프로세스를 가져오는 저장 프로시저입니다.일반적으로 이 명령을 SQL Profiler 인스턴스와 함께 실행하여 차단 프로세스를 찾고 프로파일러에서 실행된 spid의 최신 명령을 확인합니다.

다음을 실행하여 차단 sql을 찾고 sql을 기다릴 수 있습니다.

SELECT
    t1.resource_type ,
    DB_NAME( resource_database_id) AS dat_name ,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.wait_duration_ms,
    ( SELECT TEXT FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) WHERE r.session_id = t1.request_session_id ) AS wait_sql,
    t2.blocking_session_id,
    ( SELECT TEXT FROM sys.sysprocesses p CROSS apply sys.dm_exec_sql_text ( p.sql_handle ) WHERE p.spid = t2.blocking_session_id ) AS blocking_sql 
FROM
    sys.dm_tran_locks t1,
    sys.dm_os_waiting_tasks t2 
WHERE
    t1.lock_owner_address = t2.resource_address

언급URL : https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table