안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹입니다.

TL;DR

트랜잭션과 잠금의 관계를 다루며, 잠금이 발생하는 상태를 예제로 설명합니다. 잠금의 종류와 잠금 단위, 잠금 힌트와 잠금 모드에 대해 소개하고, 잠금 에스컬레이션에 대한 내용을 설명합니다.

트랜잭션과 잠금은 밀접한 관계가 있습니다. 트랜잭션 작업이 여러 형태의 잠금을 유발할 수 있기 때문입니다.

공유 폴더에서 워드 문서 파일을 수정할 때

잠금을 처음 설명할 때 좋은 샘플이 있습니다. 예를 들어, 사내 공유 폴더에 워드 문서를 두고 작업합니다. 내가 워드 문서를 오픈해 수정하다가, 다른 사용자가 같은 워드 파일을 오픈하면,

다른 프로세스나 사용자가 현재 해당 파일을 오픈하고 있습니다. 읽기 전용으로 여시겠습니까?

라는 메시지와 함께 파일을 읽기 전용으로 열었을 거예요. 이런 상황이 바로 오늘 공부할 잠금 개념과 비슷합니다. 두 사용자가 동시에 테이블의 같은 로우에 쓰기 작업을 하면, 어느 것을 먼저 적용시켜야 할지 알 수 없겠지요. 

DBMS와 잠금

DBMS는 기본적으로 여러 명의 사용자 또는 프로세스가 동시에 접속해 작업하는 동시 다중 사용성이 있습니다. 위의 상황과 같은 병행처리가 DBMS는 반드시 제공되어야 합니다.

잠금 상황을 SQL 쿼리로 시뮬레이션

그 개념이 바로 잠금이며 SQL Server는 이 잠금이 대단히 다양하고 세분화되어 있습니다.
먼저 잠금 상황을 SQL 쿼리로 재현해 보겠습니다. 말 그대로, 억지로 잠금과 블로킹(Blocking)을 보실 수 있는 쿼리입니다.

-- 운영 환경에서 수행하지 마시고, 개인 개발 환경에서만 수행하세요.
USE AdventureWorks;
GO

-- 트랜잭션 격리 수준을 가장 높은 단계인 SERIALIZABLE로 설정
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- ListPrice * 2 수행
BEGIN TRAN;
UPDATE Production.Product SET ListPrice = ListPrice * 2;

BEGIN TRAN만 있고, COMMIT TRAN이나 ROLLBACK이 없습니다. 이 상태에서, 새 쿼리를 수행해 새로운 세션을 열고 아래 쿼리를 수행해 블로킹을 유발합니다.

USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 테이블 데이터 조회
SELECT * FROM Production.Product;
 

실행하면, 결과가 나오지 않고, 계속 대기 중인 상태입니다. 다시 새 쿼리를 수행해 새로운 세션을 열고 블로킹 상태를 체크합니다.

-- dm_tran_locks 시스템 카탈로그에서 잠금 정보 확인
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = (SELECT db.database_id
FROM sys.databases AS db
WHERE name = 'Adventureworks');
GO
-- sp_lock도 가능하지만, 차기 버전에서 지원하지 않음.
-- dm_tran_locks 사용을 권장
EXEC sp_lock;
GO

-- BlkBy 컬럼에서 블로킹을 유발하는 세션 확인 가능
EXEC sp_who2;
GO

-- sp_who2에서 블로킹을 유발하는 세션이 71번이면, 아래 쿼리로 상세 정보 출력
DBCC INPUTBUFFER(71);
GO
-- 또는 dm_exec_input_buffer 사용
SELECT * FROM sys.dm_exec_input_buffer (71, 0);
GO

-- 또는 아래 쿼리를 수행해 블로킹 유발 세션의 상세 정보 확인 가능
-- https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking#analyze-blocking-data
SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
GO

잠금 정보 조회

dm_tran_locks 시스템 카탈로그 조회 결과를 조금 더 상세히 살펴보겠습니다. 먼저 resource_type으로 잠금 타입이며, 잠금이 걸리는 형식입니다. 보시면 Database, PAGE, KEY, OBJECT 등이 있습니다. 

잠금 단위

잠금이 걸리는 단위는 무엇일까요? 테이블 단위? 로우 단위? 아래 표가 잠금의 단위 리소스입니다. RID 행 식별자부터 TABLE 단위까지는 자주 보시게 될 겁니다.

SQL Server는 SQL 쿼리 구문을 분석해 잠금 범위를 자동으로 설정합니다. 물론 사용자가 쿼리 힌트(Query hint)를 이용해 잠금 범위를 의도적으로 설정할 수도 있습니다. 하지만 이렇게 사용자가 직접 잠금을 설정하는 것은 권장하지 않으며, BCP나 Bulk insert 같은 대용량 벌크 작업 시 TABLE Lock을 의도적으로 거는 경우와 SELECT시 일부 쿼리에 NOLOCK을 거는 경우를 제외하고는 권장하지 않습니다.

잠금 힌트

SQL 구문에서 잠금 힌트를 사용해, 잠금 상황에 따라 쿼리가 가능합니다.

USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 테이블 데이터 조회 - NOLOCK 옵션으로 잠금을 무시하며 테이블에서 데이터를 읽습니다.
-- Dirty Read 가능성이 있어도 데이터를 읽습니다.
SELECT * FROM Production.Product WITH(NOLOCK);

아래와 같은 다양한 잠금 힌트가 제공됩니다.

NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT, NOEXPAND

참조링크: Table Hints (Transact-SQL) - SQL Server | Microsoft Learn　

NOLOCK 힌트

NOLOCK 힌트는 SELECT 구문에만 적용되며, 잠금이 걸린 상태에서도 데이터 읽기를 허용하는 쿼리 힌트입니다. Dirty Read(더티 읽기)는 데이터 수정이 진행되는 잠금 상황에서 데이터를 읽는 방법입니다. 잠금을 무시하고 데이터를 읽지만, 데이터 수정이 ROLLBACK 될 경우 업데이트된 값을 읽거나(롤백되었음에도 불구하고), Phantom Read(팬텀 리드) 같은 로우가 더 많이 출력되는 상황도 발생할 수 있습니다. 현업에서 잠금 상황에 무조건 대비해 NOLOCK을 남발하는(?) 경우가 많은데, 이렇게 Dirty Read나 Phantom Read가 발생할 수 있는 상황을 고려해 영향을 평가하고 선택해 사용해야 합니다. 또한 NOLOCK은 READUNCOMMITTED 격리 수준과 같습니다.

다시 강조하지만, 일부 SELECT  쿼리에서 Dirty Read나 Phantom Read 영향이 없을 경우 NOLOCK 처리, 또는 대용량 데이터 BULK 처리를 위해 TABLE LOCK을 의도적으로 거는 처리를 제외하면, SQL Server가 잠금 범위를 자동으로 정하는 것이 대부분의 상황에서 최선이며, 잠금의 범위가 커질 경우 역시 SQL Server가 자동으로 락을 에스컬레이션 하도록 유지하는 것이 좋습니다.

☑️ 챗GPT 활용: 트랜잭션 처리 중 발생하는 Dirty Read(더티 읽기)와 Phantom Read(팬텀 리드)에 대해 알려줘

잠금 모드

잠금도 계층(Hierarchy)이 있습니다. 예를 들어, 여러 개의 로우에 잠금이 걸린다면 - 페이지 잠금으로 에스컬레이션(Escalation) 시켜 많은 수의 미세 잠금(fine-grain lock)을 적은 수의 큰 잠금(coarse-grain lock)으로 변환하는 과정도 자동으로 실행됩니다. 잠금 에스컬레이션으로 동시성 경합 가능성(Probability of concurrency contention)은 높아지지만, 오버헤드는 낮아집니다.

예를 들어, 하위 잠금 단위에서 여러 X(Exclusive - 배타적) 잠금이 걸릴 경우, 상위 잠금 단위는 IX(Intent exclusive - 내재된 배타적) 잠금 모드가 걸릴 수 있습니다. 

☑️ 챗GPT 활용: 잠금 에컬레이션(Lock escalation), 동시성 경합 가능성(Probability of concurrency contention), 오버헤드(Overhead) 대해서 알려줘
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022