MSSQL 5.5 · 5부. 성능

잠금과 격리 수준

블로킹과 교착이 생기는 자리, 그리고 READ COMMITTED SNAPSHOT 입니다.

예상 학습 시간 24분 난이도 고급

준비

창이 두 개 필요합니다

이 단원은 두 사람이 같은 자료를 동시에 건드릴 때 무슨 일이 생기는지를 봅니다. 혼자서는 재현되지 않습니다.

SSMS 에서 새 쿼리 창을 두 개 열어 두십시오. 아래에서는 왼쪽 창과 오른쪽 창으로 부릅니다. 둘 다 MssqlLab 에 연결합니다.

여기서는 Board.POSTS(507행)를 사용합니다. 잠금은 자료가 많고 적음과 상관이 없습니다.

개념 설명

고치는 동안은 잠급니다

한 사람이 고치는 중인 자료를 다른 사람이 읽으면 반쯤 고쳐진 것을 보게 됩니다. 그래서 엔진은 고치는 동안 그 자료를 잠급니다.

왼쪽 창

BEGIN TRAN;
UPDATE Board.POSTS SET title = N'A 가 고치는 중' WHERE num = 1;
-- 커밋하지 않고 그대로 둡니다.

오른쪽 창

-- 8초만 기다리고 포기하도록 해 둡니다.
SET LOCK_TIMEOUT 8000;
SELECT num, title FROM Board.POSTS WHERE num = 1;

결과 — 오른쪽 창

메시지 1222, 수준 16, 상태 51 Lock request time out period exceeded. 8,002 밀리초를 기다리다 끝났습니다.

SET LOCK_TIMEOUT 을 두지 않으면 왼쪽 창이 커밋하거나 롤백할 때까지 기다립니다.

읽기만 하는데도 막힙니다. 왼쪽이 커밋할지 롤백할지 아직 모르기 때문입니다. 커밋되면 새 값이 맞고 롤백되면 옛 값이 맞는데, 지금은 어느 쪽도 확정이 아닙니다.

누가 누구를 막고 있는지 봅니다

세 번째 창

SELECT r.session_id AS 세션, r.blocking_session_id AS 막고있는세션,
       r.wait_type AS 대기종류, r.wait_time AS 대기시간
FROM sys.dm_exec_requests r
WHERE r.session_id > 50 AND r.session_id <> @@SPID;

결과

세션	막고있는세션	대기종류	대기시간
51	0	WAITFOR	9,534
52	51	LCK_M_S	220

52 가 51 에 막혀 있습니다. LCK_M_S 는 공유 잠금을 기다린다는 뜻입니다.

blocking_session_id 가 실제 장애를 볼 때 가장 먼저 보는 열입니다. 화면이 멈췄다는 신고가 들어오면 이 한 줄로 누가 붙잡고 있는지 알 수 있습니다.

어떤 잠금이 걸려 있습니까

세 번째 창

SELECT l.request_session_id AS 세션, l.resource_type AS 자원,
       l.request_mode AS 방식, l.request_status AS 상태,
       OBJECT_NAME(p.object_id) AS 표
FROM sys.dm_tran_locks l
    LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()
ORDER BY l.request_session_id;

결과

세션	자원	방식	상태	표
51	KEY	X	GRANT	POSTS
51	PAGE	IX	GRANT	POSTS
51	OBJECT	IX	GRANT	POSTS
52	KEY	S	WAIT	POSTS
52	PAGE	IS	GRANT	POSTS
52	OBJECT	IS	GRANT	POSTS

51 이 그 행(KEY)에 X 를 잡았고, 52 의 S 가 WAIT 로 멈춰 있습니다.

방식	언제	함께 걸릴 수 있는 것
S (공유)	읽을 때	다른 S 와는 함께 걸립니다
X (배타)	고칠 때	아무것과도 함께 걸리지 않습니다
U (갱신)	고칠 행을 찾는 동안	S 와는 함께, U·X 와는 못 겁니다
IS · IX (의도)	위 단계에 표시해 둘 때	아래에 무엇이 걸렸는지 알립니다

자원에는 층이 있습니다. 행(KEY) 하나를 잠글 때 그 행이 든 페이지(PAGE)와 표(OBJECT)에 의도 잠금을 함께 겁니다. 그래야 표 전체를 잠그려는 사람이 아래를 뒤지지 않고도 안에 무엇이 걸려 있는지 알 수 있습니다.

현상

막지 않으면 무엇이 보입니까

잠금을 느슨하게 하면 빨라집니다. 대신 세 가지가 보이기 시작합니다. 하나씩 실제로 만들어 봅니다.

(1) 커밋되지 않은 값 — 더티 리드

왼쪽 창

BEGIN TRAN;
UPDATE Board.POSTS SET title = N'아직 커밋하지 않은 제목' WHERE num = 1;
-- 잠시 두었다가
ROLLBACK;

오른쪽 창

-- 왼쪽이 아직 커밋하지 않은 사이에
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT num, title FROM Board.POSTS WHERE num = 1;

-- 힌트로 적어도 같습니다.
SELECT num, title FROM Board.POSTS WITH (NOLOCK) WHERE num = 1;

결과 — 오른쪽 창

num	title
1	아직 커밋하지 않은 제목

왼쪽이 롤백한 뒤 다시 읽으면

num	title
1	조인 질문드립니다

막히지 않고 읽었지만, 읽은 값은 어느 시점에도 존재한 적이 없습니다.

NOLOCK 이 "빠르게 하는 힌트" 로 잘못 알려져 있습니다. 실제로는 없던 값을 읽어도 좋다는 뜻입니다. 그 밖에 같은 행을 두 번 읽거나 아예 건너뛰는 일도 생깁니다. 페이지가 나뉘는 중에 읽으면 그렇게 됩니다. 금액이나 재고를 이 힌트로 읽지 마십시오.

(2) 같은 행이 두 번 다르게 — 반복 읽기 불가

오른쪽 창 — 기본 수준입니다

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SELECT title FROM Board.POSTS WHERE num = 1;
-- 이 사이에 왼쪽 창이 UPDATE 하고 커밋합니다.
SELECT title FROM Board.POSTS WHERE num = 1;
COMMIT;

결과

읽은 차례	READ COMMITTED	REPEATABLE READ
처음	조인 질문드립니다	조인 질문드립니다
다시	그 사이에 바뀐 제목	조인 질문드립니다
고치려던 쪽은	그대로 고쳤습니다	1222 로 막혔습니다(3,005 밀리초)

읽는 쪽이 값을 지키면 고치는 쪽이 기다립니다. 둘 다 얻을 수는 없습니다.

READ COMMITTED 는 읽고 나면 바로 놓습니다. 그래서 같은 트랜잭션 안이라도 두 번째 읽기는 새 값을 봅니다. REPEATABLE READ 는 트랜잭션이 끝날 때까지 붙잡고 있습니다.

(3) 없던 행이 생김 — 팬텀

오른쪽 창

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- 또는 SERIALIZABLE
BEGIN TRAN;
SELECT COUNT(*) FROM Board.POSTS WHERE board_num = 1;
-- 이 사이에 왼쪽 창이 board_num = 1 인 글을 하나 넣습니다.
SELECT COUNT(*) FROM Board.POSTS WHERE board_num = 1;
ROLLBACK;

결과

읽은 차례	REPEATABLE READ	SERIALIZABLE
처음 센 값	35	35
다시 센 값	36	35
넣으려던 쪽은	넣었습니다	1222 로 막혔습니다

REPEATABLE READ 는 읽은 행만 지킵니다. 없던 행이 끼어드는 것은 막지 못합니다.

SERIALIZABLE 은 읽은 행이 아니라 읽은 범위를 잠급니다. 그래서 그 범위에 새 행을 넣는 것까지 막습니다. 가장 안전하고 가장 많이 막습니다.

정리

다섯 가지 수준

격리 수준	더티 리드	반복 읽기 불가	팬텀	막습니까
READ UNCOMMITTED	생깁니다	생깁니다	생깁니다	막지 않습니다
READ COMMITTED (기본)	막습니다	생깁니다	생깁니다	읽는 동안만
REPEATABLE READ	막습니다	막습니다	생깁니다	읽은 행을 끝까지
SERIALIZABLE	막습니다	막습니다	막습니다	읽은 범위를 끝까지
SNAPSHOT	막습니다	막습니다	막습니다	막지 않습니다

마지막 줄이 눈에 띕니다. 셋을 다 막으면서 아무도 막지 않습니다. 어떻게 그럴 수 있는지가 다음 절입니다.

스냅숏

막는 대신 예전 값을 보여 줍니다

SNAPSHOT 은 잠금을 기다리지 않습니다. 대신 트랜잭션이 시작한 시점의 값을 tempdb 에 남겨 둔 사본에서 읽습니다.

SQL

-- 데이터베이스에서 먼저 켜야 합니다.
ALTER DATABASE MssqlLab SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

왼쪽 창

BEGIN TRAN;
UPDATE Board.POSTS SET title = N'A 가 고치는 중' WHERE num = 1;
-- 커밋하지 않고 그대로 둡니다.

오른쪽 창

SET LOCK_TIMEOUT 4000;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT title FROM Board.POSTS WHERE num = 1;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT title FROM Board.POSTS WHERE num = 1;
COMMIT;

결과

격리 수준	결과	걸린 시간
READ COMMITTED	1222 잠금 제한 시간 초과	4,006 밀리초
SNAPSHOT	조인 질문드립니다	0 밀리초

더티 리드가 아닙니다. 커밋된 적이 있는 값이고, 다만 지금 값이 아닐 뿐입니다.

	READ UNCOMMITTED	SNAPSHOT
읽는 값	커밋되지 않은 값	커밋된 예전 값
존재한 적이	없을 수도 있습니다	있습니다
값을 치르는 곳	정확성	tempdb

둘은 전혀 다릅니다. 하나는 없던 값을, 하나는 조금 전 값을 읽습니다. NOLOCK 을 붙이고 싶어지는 자리라면 대개 이쪽이 맞습니다.

기본 수준 자체를 바꿀 수도 있습니다

SQL

-- READ COMMITTED 를 잠금 대신 사본으로 처리하게 합니다.
ALTER DATABASE MssqlLab SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

무엇이 달라집니까

문장을 하나도 고치지 않아도 READ COMMITTED 가 잠금을 기다리는 대신 사본에서 읽게 됩니다. 읽는 쪽이 쓰는 쪽을 막지 않고, 쓰는 쪽도 읽는 쪽을 막지 않습니다.

WITH ROLLBACK IMMEDIATE 는 연결이 있으면 끊고 바꾸겠다는 뜻입니다. 운영 중에는 조심하십시오.

값이 없지는 않습니다. 고쳐진 행마다 예전 값을 tempdb 에 쌓아 두므로 tempdb 가 커지고, 행마다 14바이트가 붙습니다. 그래도 읽기가 많은 웹 서비스라면 대개 켜는 편이 낫습니다. 신규 데이터베이스에서는 기본으로 켜져 있는 경우도 있으니 sys.databases 에서 확인하십시오.

교착

서로가 서로를 기다립니다

둘이 같은 자원을 다른 순서로 잡으면 아무도 진행하지 못합니다.

왼쪽 창

BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = 1;
WAITFOR DELAY '00:00:03';
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = 2;
COMMIT;

오른쪽 창 — 번호 순서가 반대입니다

BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = 2;
WAITFOR DELAY '00:00:04';
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = 1;
COMMIT;

결과 — 한쪽만 죽습니다

메시지 1205, 수준 13, 상태 51 Transaction (Process ID 85) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 다른 쪽은 아무 일 없이 끝났습니다.

엔진이 5초마다 살펴보고 한쪽을 골라 롤백합니다. 되돌릴 것이 적은 쪽이 희생자가 됩니다.

교착은 막을 수 없는 것이 아니라 줄이는 것입니다.

줄이는 방법	까닭
언제나 같은 순서로 건드립니다	순서가 같으면 교착이 생기지 않습니다
트랜잭션을 짧게 둡니다	잡고 있는 시간이 짧을수록 마주칠 일이 적습니다
트랜잭션 안에서 사용자를 기다리지 않습니다	사람이 화면을 보는 동안 잠금이 살아 있습니다
인덱스를 둡니다	찾느라 훑는 행이 적을수록 잠그는 행도 적습니다(5.2)
1205 를 만나면 다시 시도합니다	교착은 되풀이되지 않는 것이 보통입니다

1205 는 예외 처리로 다시 시도해도 되는 몇 안 되는 오류입니다(4.5). 트랜잭션 전체가 이미 롤백되어 있으므로 처음부터 다시 하면 됩니다. 다만 무한정 반복하지 말고 세 번쯤으로 끊으십시오.

연습

직접 해보기

1. 멈춘 화면의 원인을 찾습니다 난이도 하

게시판이 멈췄다는 신고가 들어왔습니다. 누가 무엇을 붙잡고 있는지 한 문장으로 찾아 보세요. 붙잡고 있는 세션을 끊는 방법도 함께 적으십시오.

-- 막고 있는 쪽과 막힌 쪽을 한 번에 봅니다. SELECT r.session_id AS 막힌세션, r.blocking_session_id AS 막고있는세션, r.wait_type AS 대기종류, r.wait_time / 1000 AS 대기초, LEFT(t.text, 100) AS 막힌문장, LEFT(bt.text, 100) AS 막고있는문장, s.login_name, s.host_name, s.program_name FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t LEFT JOIN sys.dm_exec_sessions s ON s.session_id = r.blocking_session_id OUTER APPLY ( SELECT TOP (1) text FROM sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) WHERE c.session_id = r.blocking_session_id) bt WHERE r.blocking_session_id <> 0; -- 막고있는세션이 또 다른 세션에 막혀 있을 수 있습니다. -- 그때는 그 사슬의 맨 앞(blocking_session_id 가 0 인 것)을 찾아야 합니다. -- 끊습니다. 세션 번호를 확인하고 부르십시오. KILL 51; -- 끊기 전에 무엇을 하던 세션인지 반드시 보십시오. -- 롤백에 오래 걸릴 수 있고, 진행 상황은 이렇게 봅니다. KILL 51 WITH STATUSONLY; -- 근본 원인은 대개 셋 가운데 하나입니다. -- 커밋을 빠뜨린 트랜잭션 -- 트랜잭션 안에서 사람을 기다리는 화면 -- 인덱스가 없어 너무 많은 행을 잠그는 문장(5.2)

2. 조회수 올리기가 화면을 막습니다 난이도 중

글을 열 때마다 조회수를 올리고 본문을 읽습니다. 사람이 몰리면 화면이 느려집니다. 무엇이 문제이고 어떻게 고치겠습니까.

SQL

BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = @num;
-- 여기서 본문과 댓글을 읽어 화면에 내려보냅니다.
SELECT * FROM Board.POSTS WHERE num = @num;
SELECT * FROM Board.COMMENTS WHERE post_num = @num;
COMMIT;

UPDATE 가 잡은 X 잠금은 언제 풀립니까. 그 사이에 이 글을 열려는 다른 사람은 무엇을 기다립니까.

-- 문제 -- UPDATE 가 그 행에 X 를 걸고, COMMIT 까지 놓지 않습니다. -- 그 사이에 같은 글을 여는 사람은 모두 그 X 를 기다립니다. -- 인기 글일수록 한 줄에 사람이 몰립니다. -- 본문과 댓글을 읽는 시간까지 잠금을 붙잡고 있습니다. -- 고침 (1) 트랜잭션을 나눕니다. 조회수와 본문은 함께 묶을 까닭이 없습니다. UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = @num; -- 스스로 커밋됩니다 SELECT * FROM Board.POSTS WHERE num = @num; SELECT * FROM Board.COMMENTS WHERE post_num = @num; -- 고침 (2) 읽기를 먼저, 올리기를 나중에 합니다. -- 화면에 내려보낼 것을 먼저 읽으면 잠금을 잡는 시간이 가장 짧아집니다. -- 고침 (3) 읽기가 쓰기를 기다리지 않게 합니다. ALTER DATABASE MssqlLab SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; -- 문장을 고치지 않아도 읽는 쪽이 사본에서 읽습니다. -- 고침 (4) 조회수를 실시간으로 쓰지 않습니다. -- 메모리나 별도 표에 모았다가 일정 시간마다 한 번에 반영합니다. -- 조회수는 몇 초 늦어도 되는 값입니다. -- 대량 반영은 5.7 에서 다룹니다. -- 하지 말 것 SELECT * FROM Board.POSTS WITH (NOLOCK) WHERE num = @num; -- 막히지는 않지만 조회수가 없던 값으로 보일 수 있습니다. -- 무엇보다 원인(잠금을 오래 잡는 것)이 그대로 남습니다.

실습을 마쳤으면 되돌립니다.
ALTER DATABASE MssqlLab SET ALLOW_SNAPSHOT_ISOLATION OFF;
UPDATE Board.POSTS SET title = N'조인 질문드립니다' WHERE num = 1;
열어 둔 창에 커밋하지 않은 트랜잭션이 남아 있지 않은지도 확인하십시오 (SELECT @@TRANCOUNT;).

요약

고치는 동안 그 행에 X(배타) 잠금이 걸리고, 읽기만 하는 쪽도 막힙니다. 커밋될지 롤백될지 아직 모르기 때문입니다.
잠금에는 층이 있습니다. 행(KEY)을 잠그면 페이지와 표에 의도 잠금(IX·IS)을 함께 겁니다.
sys.dm_exec_requests 의 blocking_session_id 가 장애를 볼 때 가장 먼저 보는 열입니다. sys.dm_tran_locks 에서 WAIT 상태를 찾으면 무엇을 기다리는지 보입니다.
SET LOCK_TIMEOUT 을 두면 그만큼만 기다리고 1222 로 끝냅니다. 두지 않으면 상대가 끝날 때까지 기다립니다.
느슨하게 하면 셋이 보입니다 — 더티 리드 · 반복 읽기 불가 · 팬텀. 각각 READ COMMITTED · REPEATABLE READ · SERIALIZABLE 이 막습니다.
NOLOCK 은 빠르게 하는 힌트가 아니라 없던 값을 읽어도 좋다는 뜻입니다. 같은 행을 두 번 읽거나 건너뛰기도 합니다.
SNAPSHOT 은 막는 대신 커밋된 예전 값을 읽습니다. 4,006 밀리초를 기다리다 실패하던 것이 0 밀리초에 끝납니다. 값은 tempdb 로 치릅니다.
READ_COMMITTED_SNAPSHOT 을 켜면 문장을 고치지 않고도 기본 수준이 사본에서 읽습니다. 읽기가 많은 웹 서비스라면 대개 켜는 편이 낫습니다.
교착(1205)은 같은 자원을 다른 순서로 잡을 때 생깁니다. 순서를 맞추고 트랜잭션을 짧게 두는 것이 가장 확실한 예방입니다.
1205 는 다시 시도해도 되는 오류입니다. 트랜잭션이 이미 롤백되어 있기 때문입니다. 다만 횟수를 정해 두십시오.