안녕하세요. 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 단위까지는 자주 보시게 될 겁니다.

 

리소스
Description
RID 행 식별자는 힙 내의 단일 행을 잠그는 데 사용됩니다.
KEY 인덱스 내의 행 잠금은 직렬화 가능한 트랜잭션에서 키 범위를 보호하는 데 사용됩니다.
PAGE 데이터 또는 인덱스 페이지와 같은 데이터베이스의 8KB 페이지입니다.
EXTENT 데이터 또는 인덱스 페이지와 같은 인접한 8개의 페이지 그룹입니다.
HoBT 힙 또는 B-트리입니다. 클러스터형 인덱스가 없는 테이블에서 힙 데이터 페이지나 B-트리(인덱스)를 보호하는 잠금입니다.
TABLE 모든 데이터와 인덱스가 포함된 전체 테이블입니다.
FILE 데이터베이스 파일입니다.
APPLICATION 애플리케이션이 지정한 리소스입니다.
METADATA 메타데이터 잠금입니다.
ALLOCATION_UNIT 할당 단위입니다.
DATABASE 전체 데이터베이스입니다.
Xact 최적화된 잠금에 사용되는 TID(트랜잭션 ID) 잠금입니다.

 

 

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 - 내재된 배타적) 잠금 모드가 걸릴 수 있습니다. 

 
잠금 모드
Description
공유(S) SELECT 문처럼 데이터를 변경하거나 업데이트하지 않는 읽기 작업에 사용합니다.
업데이트(U) 업데이트할 수 있는 리소스에 사용합니다. 여러 개의 세션이 리소스를 읽고, 잠그고, 나중에 업데이트할 때 발생하는 일반적인 교착 상태를 방지합니다.
배타적(X) INSERT, UPDATE, DELETE와 같은 데이터 수정 작업에 사용합니다. 여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 이루어지지 못하게 합니다.
의도 잠금 계층 구조를 만드는 데 사용합니다. 의도 잠금의 종류에는 내재된 공유(IS), 내재된 배타(IX), 공유 내재된 배타(SIX)가 있습니다.
스키마 테이블의 스키마에 종속되는 작업이 실행될 때 사용합니다. 스키마 잠금에는 스키마 수정(Sch-M)과 스키마 안정성(Sch-S) 잠금이 있습니다.
대량 업데이트(BU) 데이터를 테이블로 대량 복사하는 경우와 TABLOCK 힌트가 지정된 경우에 사용합니다.
키 범위 직렬화 가능 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호합니다. 쿼리가 다시 실행될 경우 직렬화 가능 트랜잭션의 쿼리에 대해 반환되는 행을 다른 트랜잭션이 삽입할 수 없도록 합니다.


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

 

SQL 강좌 책 구매

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

 

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

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 28245
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 15455
2314 SQL강좌: 14-7. 트랜잭션과 잠금처리 - 교착상태(데드락-DeadLock) 관리 [1] 코난(김대우) 2023.08.18 179
2313 SQL강좌: 14-6. 트랜잭션과 잠금처리 - 잠금 관리 file 코난(김대우) 2023.08.18 51
2312 SQL강좌: 14-5. 트랜잭션과 잠금처리 - 잠금과 트랜잭션 격리 수준 코난(김대우) 2023.08.18 36
» SQL강좌: 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹 코난(김대우) 2023.08.18 61
2310 SQL강좌: 14-3. 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트 [1] file 코난(김대우) 2023.08.18 115
2309 SQL강좌: 14-2. 트랜잭션과 잠금처리 - 트랜잭션 종류 코난(김대우) 2023.08.18 76
2308 SQL강좌: 14-1. 트랜잭션과 잠금처리 - 트랜잭션 이해 코난(김대우) 2023.08.18 75
2307 SQL강좌: 13-5. 백업과 복원 - 로그 전달, Always On 고가용성과 재해 복구 구현 file 코난(김대우) 2023.08.18 37
2306 SQL강좌: 13-4. 백업과 복원 - 유지 관리 계획 수립 file 코난(김대우) 2023.08.18 52
2305 SQL강좌: 13-3. 백업과 복원 - 백업과 복원 전략 실행 file 코난(김대우) 2023.08.18 46
2304 SQL강좌: 13-2. 백업과 복원 - 백업과 복원 전략 file 코난(김대우) 2023.08.18 48
2303 SQL강좌: 13-1. 백업과 복원 - 백업과 복원 이해 file 코난(김대우) 2023.08.18 62
2302 SQL강좌: 12-9. 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자) file 코난(김대우) 2023.08.18 50
2301 SQL강좌: 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축 코난(김대우) 2023.08.18 50
2300 SQL강좌: 12-7. 인덱스 생성과 관리 - 인덱스 옵션 코난(김대우) 2023.08.18 52
2299 SQL강좌: 12-6. 인덱스 생성과 관리 - 클러스터형 vs 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 47
2298 SQL강좌: 12-5. 인덱스 생성과 관리 - 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 44
2297 SQL강좌: 12-4. 인덱스 생성과 관리 - 클러스터형 인덱스 file 코난(김대우) 2023.08.18 57
2296 SQL강좌: 12-3. 인덱스 생성과 관리 - 인덱스 생성 file 코난(김대우) 2023.08.18 38
2295 SQL강좌: 12-2. 인덱스 생성과 관리 - 인덱스 종류 코난(김대우) 2023.08.18 74





XE Login