sp_lock2

sp_lock : Lock에 대한 정보

sp_lock은 Lock에 관련된 정보를 반환하는 System Stored Procedure입니다. sp_lock에 의해서 반환되는 정보는 다음과 같습니다.

sp_lock의 기본적인 구문은 다음과 같습니다.

- Syntax
sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

[@spid1 =] 'spid1'

master.dbo.sysprocesses에서 Microsoft?? SQL Server™ 프로세스 ID 번호입니다. spid1은 int이며 기본값은 NULL입니다. 잠금에 관한 프로세스 정보를 가져오려면 sp_who를 실행합니다. spid1을 지정하지 않은 경우에는 잠금에 관한 모든 정보가 표시됩니다.

[@spid2 =] 'spid2'

잠금 정보를 확인할 또 다른 SQL Server 프로세스 ID 번호입니다. spid2는 int이며 기본값은 NULL입니다. spid2는 spid1과 동시에 잠금을 할 수 있는 또 다른 spid이며 사용자는 spid2의 정보 또한 필요로 합니다.

sp_lock에 의해서 반환되는 결과는 다음과 같습니다.

열 이름 Data Type 설명
spid smallint SQL Server Process ID
dbid smallint Lock을 요청하는 Database ID
OjbId int Lock을 요청하는 개체(Object) ID
IndId smallint Index ID 번호
Type nchar(4) Lock의 유형
  • DB = Database
  • FIL = File
  • IDX = Index
  • PG = Page
  • KEY = Key
  • TAB = Table
  • EXT = Extent
  • RID = Row Identifier
Resource nchar(16) syslockinfo.restext의 값에 해당되는 Lock Resource
Mode nvarchar(8) Lock 요청자의 Lock Mode입니다.
  • granted mode
  • convert mode
  • waiting mode
Status int Lock 요청 상태
  • GRANT
  • WAIT
  • CNVRT

다음의 그림은 sp_lock에 의해서 반환되는 정보입니다.

그림1. sp_lock의 예제

보시는 것과 같이 ID와 같이 요약된 정보를 표기하고 있습니다.

이를 보완하기 위한 것이 sp_lock2입니다.

다음의 그림2는 그림1과 같은 결과를 sp_lock2에 의해서 반환 받은 결과입니다.

다음의 그림은 sp_lock에 의해서 반환되는 정보입니다.

그림2. sp_lock2

참고 sp_lock2는 가급적이면 master database에서 생성하시는 것이 좋습니다. 그래야만 어떠한 Database에서 실행을 하여도 Lock에 대한 정보를 반환할 수 있습니다.

/*DROP PROCEDURE sp_lock2
FUNCTION : sp_lock의 내용을 좀 더 편하게 볼 수 있도록 정보제공
작성일 : 2000-11-23, 작성자 : 성지용
*/
CREATE PROCEDURE sp_lock2
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
--WITH ENCRYPTION
AS

SET NOCOUNT ON

IF @spid1 IS NOT NULL
BEGIN
   SELECT CONVERT (smallint, req_spid) AS spid, 
                 rsc_dbid AS dbid, 
                 d.[name] AS Database_Name, 
                 rsc_objid AS ObjId, 
                 ISNULL(o.[name], '해당 사항 없슴') AS [Object Name], 
                 rsc_indid AS IndId, 
                 CASE SUBSTRING (v.name, 1, 4) 
                               WHEN 'DB ' THEN 'DATABASE' 
                               WHEN 'KEY ' THEN 'KEY' 
                               WHEN 'EXT ' THEN 'EXTENT' 
                               WHEN 'TAB ' THEN 'TABLE' 
                               WHEN 'PAG ' THEN 'PAGE' 
                               WHEN 'RID ' THEN 'ROW IDENTIFIER' 
                 END AS Type, 
                 CASE SUBSTRING (v.name, 1, 4) 
                               WHEN 'EXT ' THEN 'FIELD : PAGE = ' + SUBSTRING (rsc_text, 1, 16) 
                               WHEN 'PAG ' THEN 'FIEL ID : PAGE' + SUBSTRING (rsc_text, 1, 16) 
                               WHEN 'RID ' THEN 'Field : Page : RID = ' + SUBSTRING (rsc_text, 1, 16) 
                               ELSE SUBSTRING (rsc_text, 1, 16) 
                 END AS Resource, 
                 CASE SUBSTRING (u.name, 1, 8) 
                               WHEN 'Sch-S ' THEN 'Schema Stability(Sch-S)' 
                               WHEN 'Sch-M ' THEN 'Schema Modification(Sch-M)' 
                               WHEN 'IS ' THEN 'Intent Shared Lock(IS)' 
                               WHEN 'SIU ' THEN 'Shared Intent Update Lock(SIU)' 
                               WHEN 'IS-S ' THEN 'Intent Shared-Shared Lock(IS-S)' 
                               WHEN 'IX ' THEN 'Intent Exclusive Lock(IX)' 
                               WHEN 'SIX ' THEN 'Shared Intent Exclusive Lock(SIX)' 
                               WHEN 'S ' THEN 'Shared Lock(S)' 
                               WHEN 'U ' THEN 'Update Lock(U)' 
                               WHEN 'IIn-Nul ' THEN 'Intent Insert-NULL(IIn-Null)' 
                               WHEN 'IS-X ' THEN 'Intent Shared-Exclusive Lock(IS-X)' 
                               WHEN 'IU ' THEN 'Intent Update Lock(IU)' 
                               WHEN 'IS-U ' THEN 'Intent Shared-Update Lock(IS-U)' 
                               WHEN 'X ' THEN 'Exclusive Lock(X)' 
                               WHEN 'I ' THEN 'Intent Lock(I)' 
                               WHEN 'Sch ' THEN 'Schema Lock(Sch)' 
                               WHEN 'BU ' THEN 'Bulk Operations(BU)' 
                 END AS Mode, 
                 SUBSTRING (x.name, 1, 5) AS Status, 
                 CASE req_ownertype 
                               WHEN 1 THEN 'Transaction' 
                               WHEN 2 THEN 'Session' 
                               WHEN 3 THEN 'Cursor' 
                               ELSE 'N/A' 
                 END AS Owner

   FROM mASter.dbo.syslockinfo,
              mASter.dbo.spt_values v, 
              mASter.dbo.spt_values x, 
              mASter.dbo.spt_values u, 
              mASter.dbo.sysdatabASes d, 
              mASter.dbo.sysobjects o

   WHERE mASter.dbo.syslockinfo.rsc_type = v.number 
                                   and v.type = 'LR' 
                                   and mASter.dbo.syslockinfo.req_status = x.number 
                                   and x.type = 'LS' 
                                   and mASter.dbo.syslockinfo.req_mode + 1 = u.number 
                                   and u.type = 'L' 
                                   AND rsc_dbid = d.dbid 
                                   AND rsc_objid = o.[id] 
                                   and req_spid in (@spid1, @spid2)

   END

/*
** No parameters, so show all the locks.
*/
ELSE
BEGIN 
   SELECT CONVERT (smallint, req_spid) AS spid, 
                rsc_dbid AS dbid, 
                d.[name] AS [Database Name], 
                rsc_objid AS ObjId, 
                ISNULL(o.[name], '해당 사항 없슴') AS [Object Name], 
                rsc_indid AS IndId, 
                CASE SUBSTRING (v.name, 1, 4) 
                             WHEN 'DB ' THEN 'DATABASE' 
                             WHEN 'KEY ' THEN 'KEY' 
                             WHEN 'EXT ' THEN 'EXTENT' 
                             WHEN 'TAB ' THEN 'TABLE' 
                             WHEN 'PAG ' THEN 'PAGE' 
                             WHEN 'RID ' THEN 'ROW IDENTIFIER' 
                END AS Type, 
                CASE SUBSTRING (v.name, 1, 4) 
                             WHEN 'EXT ' THEN 'FIELD : PAGE = ' + SUBSTRING (rsc_text, 1, 16) 
                             WHEN 'PAG ' THEN 'FIEL ID : PAGE' + SUBSTRING (rsc_text, 1, 16) 
                             WHEN 'RID ' THEN 'Field : Page : RID = ' + SUBSTRING (rsc_text, 1, 16) 
                             ELSE SUBSTRING (rsc_text, 1, 16) 
                END AS Resource, 
                CASE SUBSTRING (u.name, 1, 8) 
                             WHEN 'Sch-S ' THEN 'Schema Stability(Sch-S)' 
                             WHEN 'Sch-M ' THEN 'Schema Modification(Sch-M)' 
                             WHEN 'IS ' THEN 'Intent Shared Lock(IS)' 
                             WHEN 'SIU ' THEN 'Shared Intent Update Lock(SIU)' 
                             WHEN 'IS-S ' THEN 'Intent Shared-Shared Lock(IS-S)' 
                             WHEN 'IX ' THEN 'Intent Exclusive Lock(IX)' 
                             WHEN 'SIX ' THEN 'Shared Intent Exclusive Lock(SIX)' 
                             WHEN 'S ' THEN 'Shared Lock(S)' 
                             WHEN 'U ' THEN 'Update Lock(U)' 
                             WHEN 'IIn-Nul ' THEN 'Intent Insert-NULL(IIn-Null)' 
                             WHEN 'IS-X ' THEN 'Intent Shared-Exclusive Lock(IS-X)' 
                             WHEN 'IU ' THEN 'Intent Update Lock(IU)' 
                             WHEN 'IS-U ' THEN 'Intent Shared-Update Lock(IS-U)' 
                             WHEN 'X ' THEN 'Exclusive Lock(X)' 
                             WHEN 'I ' THEN 'Intent Lock(I)' 
                             WHEN 'Sch ' THEN 'Schema Lock(Sch)' 
                             WHEN 'BU ' THEN 'Bulk Operations(BU)' 
                END AS Mode, 
                SUBSTRING (x.name, 1, 5) AS Status, 
                CASE req_ownertype 
                             WHEN 1 THEN 'Transaction' 
                             WHEN 2 THEN 'Session' 
                             WHEN 3 THEN 'Cursor' 
                             ELSE 'N/A' 
                END AS Owner

   FROM mASter.dbo.syslockinfo, 
                mASter.dbo.spt_values v, 
                mASter.dbo.spt_values x, 
                mASter.dbo.spt_values u, 
                mASter.dbo.sysdatabASes d, 
                mASter.dbo.sysobjects o

   WHERE mASter.dbo.syslockinfo.rsc_type = v.number 
                and v.type = 'LR' 
                and mASter.dbo.syslockinfo.req_status = x.number 
                and x.type = 'LS' 
                and mASter.dbo.syslockinfo.req_mode + 1 = u.number 
                and u.type = 'L' 
                AND rsc_dbid = d.dbid 
                AND rsc_objid *= o.[id] 
   ORDER BY spid
END

RETURN (0) -- sp_lock 

크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by 발가락


티스토리 툴바