Lock 과 DeadLock

 

1.   Lock 과 Dead Lock

1)   Deadlock 정의

 

A.     프로세스가 자원을 얻지 못하는 상태

B.      교착상태라고 불림

C.      RDBMS의 경우 Transaction이 Database의 Resource를 서로 Lock을 발생시켜 완료하지 못하도록 차단하여 발생되는 교착상태를 의미한다.

D.     Dead Lock이 발생 시 둘 중 하나 또는 2개의 Transaction이 Rollback 되기 전까지 자원을 점유하여 다른 Transaction들의 Wait가 발생되어 서비스에 영향을 끼치게 된다.

2)   RDMS의 Lock이란?

A.     Lock이 발생되는 상황적인 부분과 범위적인 부분에 따라 구분한다.

B.      상황적인 Lock

                                  i.         Shared Lock

가)    공유Lock, Read Lock 이라고도 불림

나)    Transaction이 데이터 조회 시 다른 Transaction이 데이터를 조회할 수 있으나 데이터를 변경할 수 없는 상태

                                 ii.         Exclusive Lock

가)    베타 Lock, Write Lock 이라고도 불림

나)    보통 데이터의 입력, 변경, DDL 등에서 발생

다)    조회 및 변경이 모두 불가하도록 함

C.      범위적인 부분(Lock Level)

                                  i.         Row Lock : 해당 행에만 발생된 Lock

                                 ii.         Page Lock : 해당 행이 담긴 Page에 발생된 Lock

                                iii.         Table Lock : Table에 연관된 모든 Resource에 발생된 Lock(Table, Index 등)

                                iv.         Database Lock :

가)    Database의 모든 작업에 발생된 Lock

나)    Database의 이름 변경, 파일 추가, 복구 등의 작업에서 발생

2.   Lock 테스트 및 확인 방법

1)   Lock 발생 테스트

A.     테스트에 사용될 간단한 Table 생성

CREATE TABLE TBL_TEST1 (            A INT ,          B INT ) GO   INSERT TBL_TEST1 VALUES (1,1),(2,2),(3,3),(4,4) GO   SELECT * FROM TBL_TEST1

B.      Transaction이 열린 상태에서 다른 Transaction에서 Resource 접근을 시도

                                  i.         첫 번째 Transaction에서 해당 행에 변경을 시도

-- 경합이 발생하고 10초 후에 Timeout 되도록 설정 SET LOCK_TIMEOUT 10000 SELECT @@LOCK_TIMEOUT   BEGIN TRAN            DECLARE @A INT              SELECT @A=A FROM TBL_TEST1 WHERE B=1              UPDATE TBL_TEST1 SET B=1 WHERE A=@A --ROLLBACK TRAN --COMMIT TRAN

                                 ii.         두 번째 Transaction에서 첫 번째 Transaction이 완료되지 않을 상태에서 같은 행의 변경을 시도

-- 경합이 발생하고 10초 후에 Timeout 되도록 설정 SET LOCK_TIMEOUT 10000 SELECT @@LOCK_TIMEOUT   BEGIN TRAN            DECLARE @B INT              SELECT @B=B FROM TBL_TEST1 WHERE A=1              UPDATE TBL_TEST1 SET A=1 WHERE B=@B --ROLLBACK TRAN --COMMIT TRAN

C.      두 번째 Transaction에서 Timeout 발생을 확인할 수 있다.

 

2)   DMV를 통한 Lock 확인

A.     Transaction이 열린 상태에서 다른 Transaction에서 Resource 접근을 시도하는데 Lock Timeout을 무한대로 재설정한다.

                                  i.         첫 번째 Transaction

-- Timeout 시간이 무제한 되도록 설정 SET LOCK_TIMEOUT -1 SELECT @@LOCK_TIMEOUT    BEGIN TRAN            DECLARE @A INT              SELECT @A=A FROM TBL_TEST1 WHERE B=1              UPDATE TBL_TEST1 SET B=1 WHERE A=@A --ROLLBACK TRAN --COMMIT TRAN

                                 ii.         두 번째 Transaction

-- Timeout 시간이 무제한 되도록 설정 SET LOCK_TIMEOUT -1 SELECT @@LOCK_TIMEOUT   BEGIN TRAN DECLARE @B INT   SELECT @B=B FROM TBL_TEST1 WHERE A=1   UPDATE TBL_TEST1 SET A=1 WHERE B=@B --ROLLBACK TRAN --COMMIT TRAN

                                iii.         두 번째 Transaction의 실행이 종료되지 않는 것을 확인할 수 있다.,

B.      아래의 DMV를 통해서 Lock의 상황을 확인 할 수 있다.

SELECT    [구분], [내용] FROM     (                           SELECT    CONVERT(NVARCHAR(MAX),dmtl.resource_type) AS [리소스 유형]                            ,             CONVERT(NVARCHAR(MAX),db_name(resource_database_id)) AS [DB명]                            ,             CONVERT(NVARCHAR(MAX),dmtl.request_session_id) AS [대기자 sid]                                                           ,             CONVERT(NVARCHAR(MAX),dmowt.wait_duration_ms) AS [대기 시간]                                                           ,             CONVERT(NVARCHAR(MAX),(                                                      SELECT    SUBSTRING(dmest.text                                                      ,             dmer.statement_start_offset/2                                                      ,             (CASE                                  WHEN dmer.statement_end_offset = -1 THEN                                               LEN(CONVERT(NVARCHAR(MAX), dmest.text)) * 2                                  ELSE dmer.statement_end_offset                     END - dmer.statement_start_offset)/2                         )        FROM     sys.dm_exec_requests AS dmer        CROSS APPLY         sys.dm_exec_sql_text(dmer.sql_handle) AS dmest        WHERE    dmer.session_id = dmtl.request_session_id )) AS [대기자 쿼리 내용] ,      CONVERT(NVARCHAR(MAX),dmowt.blocking_session_id) AS [잠금자 sid] ,      CONVERT(NVARCHAR(MAX),(                     SELECT    text                                                                   FROM     sys.sysprocesses AS p                     CROSS APPLY         sys.dm_exec_sql_text(p.sql_handle) AS dmest                     WHERE    p.spid = dmowt.blocking_session_id )) AS [잠금자 쿼리 내용]                                         FROM     sys.dm_tran_locks as dmtl INNER            JOIN sys.dm_os_waiting_tasks as dmowt ON                dmtl.lock_owner_address = dmowt.resource_address ) AS lock_dmv UNPIVOT  (               [내용] FOR [구분] IN ([리소스 유형], [DB명], [대기자 sid], [대기 시간], [대기자 쿼리 내용], [잠금자 sid], [잠금자 쿼리 내용] ) ) AS UP

 

                                  i.         리소스 유형

가)    DATABASE : 데이터베이스

나)    FILE : 데이터베이스를 이루는 파일(데이터, 로그 파일 등)

다)    OBJECT_ID : 테이블, 뷰, 저장 프로시저, 확장 저장 프로시저 또는 개체 ID가 있는 모든 개체

라)    PAGE : 해당 데이터가 소속된 PAGE

마)    KEY : 인덱스의 행

바)    EXTENT : 데이터 파일 익스텐트

사)    RID : 힙의 물리적 행

아)    APPLICATION : 애플리케이션이 지정한 리소스

자)    METADATA : 메타데이터 정보

차)    HOBT : 힙 또는 B-트리

카)    ALLOCATION_UNIT : 인덱스 파티션과 같은 관련 페이지의 집합

                                 ii.         sid : 해당 쿼리가 실행된 Connection 번호 

3. 출처

1)      https://jwprogramming.tistory.com/12

2)      https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/monitor-database-deadlocks

3)      https://docs.microsoft.com/ko-kr/sql/t-sql/functions/lock-timeout-transact-sql?view=sql-server-ver15

4)      https://docs.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15

5)      https://docs.microsoft.com/ko-kr/sql/t-sql/statements/alter-event-session-transact-sql?view=sql-server-ver15

6)      https://blog.naver.com/jevida/140207526376

7)      https://docs.microsoft.com/ko-kr/sql/relational-databases/extended-events/extended-events?view=sql-server-ver15