[MSSQL] Lock

 

[MSSQL] Lock

 

 

트랜잭션은 LOCK 과 떨어져서 설명될 수 없는 관계이고, LOCK 도 트랜잭션 없이 설명될 수 없다.

 

LOCK 즉 잠금의 문제는 동시성 문제이다. 어떤 자원을 누군가 사용하고 있다면 어떤 형태로든

잠금이 설정된다. 그리고 그 잠금의 종류에 따라서, 해당 자원은 다른 사람이 어느 한계까지만을

사용할 수도 있고 아예 엑세스 자체도 불가능할 수도 있다.

그래서 여러 사용자가 함께 사용하는 응용프로그램이거나 특히 웹사이트 같은 경우엔 트랜잭션

사용 시 잠금에 관한 설정을 꼭 고려해야 한다.

 

 

 

1. LOCK 개념

 

 

LOCK 은 잠금이다. 잠금이란 어떤 자원(개체 등)에 대해 자신이 사용하고 있다는 것을 알리는 것이다.

그렇게 되면, 다른 연결이 해당 리소스를 엑세스할 때 제한을 받게 된다. 이렇게 하는 이유는 한 리소스를

한 사람만 변경하도록 함으로써, 데이터에 대한 일관성을 보장받기 위해서이다.

SQL Server 는 잠금기법을 사용하여 트랜잭션 무결성 및 데이터베이스 일관성을 유지한다.

잠금은, 다른 사용자가 변경 중인 데이터를 읽을 수 없게 하며 여러 사용자가 동시에 같은 데이터를

변경할 수 없게 한다. 잠금을 사용하지 않으면 데이터베이스 내의 데이터가 논리적으로 잘못될

가능성이 매우 크고, 이것은 데이터베이스 자체에 대한 심각한 오류가 된다.

우리는 이러한 잠금 매커니즘을 이해해서, 트랜잭션 제어 시에 참고하여 성능을 높이면서 동시성도

높일 수 있도록 해야 한다.

 

 

잠금의 종류는 여러 가지가 있지만 크게 공유 잠금(SHARED LOCK)과 배타적 잠금(EXCLUSIVE LOCK)으로

나눈다.

 

- 공유 잠금 : SELECT

- 배타적 잠금 : INSERT, UPDATE, DELETE

 

 

공유 잠금은 SELECT 문에 의해 설정되고, 배타적 잠금은 DML(INSERT,UPDATE,DELETE) 에 의해서 설정된다.

 

잠금은 서로 다른 사용자간의 자원쟁탈을 막기 위한 설정이므로, 이제부터는 두 CONNECTION을

예로 들면서 설명할 것이다. SESSION 1을 첫 번째 연결이라 하고, SESSION 2 를 두 번째 커넥션이라고 하자.

 

SESSION 1에서 실행하는 작업이 아래와 같다.

 

 

< SESSION1>

 

CREATE TABLE ISOL_1

(IID INT IDENTITY(1,1), INAME VARCHAR(10))

GO

 

BEGIN TRAN

INSERT ISOL_1 VALUES('AAA')

-- ISOL_1 테이블에 대해 잠금이 걸려 있다.

DBCC OPENTRAN

EXEC SP_LOCK 54

 

 

ROLLBACK TRAN

 

 

현재 하나의 트랜잭션이 진행중이고, 이 트랜잭션은 ISOL_1 테이블에 데이터를 입력하려는 작업이다.

이 때 INSERT 작업을 실행하는 ISOL_1 테이블에는 잠금이 설정되게 되는데, 여기 설정되는 잠금은

기본적으로 X 잠금이다.

X 는 EXCLUSIVE를 의미하여 잠금의 종류 중 하나로서, 배타적 잠금을 의미한다. 배타적 잠금은

어떤 연결도 해당 리소스(테이블 혹은 행 등)를 엑세스하지 못하도록 하는 잠금이다.

 

 

<SESSION2>

 

SELECT * FROM ISOL_1

 

ISOL_1 테이블을 보고자 하나, 현재 배타적 잠금이 SESSION1

에 의해서 배타적 잠금이 걸려 있어서 볼 수 없다.

 

 

위의 SELECT 문은 현재 실행되지 못하고, 그렇다고 끝나지도 않고 있는 상태이다. 그러므로

이 SELECT 문을 실행한 사용자는 어떤 다른 명령도 실행할 수 없다. 그리고 이 상태는 영원히 계속된다.

이것이 배타적 잠금이다.

현재 다른 연결인 SESSION1에서 ISOL_1 테이블을 변경하는 작업을 하고 있으므로, ISOL_1 테이블에

배타적 잠금을 설정하고 있다. 그래서 다른 연결이 엑세스하는 시도는 BLOCKING 당하게 된다.

이 상태를 해제하려면 다음과 같은 두 가지 방법을 써야 한다.

 

 

- SESSION 1에서 해당 트랜잭션을 COMMIT /ROLLBACK 문으로 완료한다.

- 아니면, SESSION 1이나, SESSION 2 프로세스를 강제로 종료(KILL)한다.

 

이 중 우리는 트랜잭션을 완료하는 쪽으로 실행해 보겠다. 이제 SESSION 1에서 COMMIT 문을 실행하자.

그러면, ISOL_1 테이블에 대해서 잠금을 풀기를 기다리던 SESSION 2 연결의 SELECT 문은 즉시 실행된다.

 

 

< SESSION1>

 

CREATE TABLE ISOL_1

(IID INT IDENTITY(1,1), INAME VARCHAR(10))

GO

 

BEGIN TRAN

INSERT ISOL_1 VALUES('AAA')

-- ISOL_1 테이블에 대해 잠금이 걸려 있다.

 

SELECT @@TRANCOUNT

 

 

DBCC OPENTRAN

EXEC SP_LOCK 54

 

COMMIT TRAN

 

 

 

<SESSION2>

 

SELECT * FROM ISOL_1

-- ISOL_1 테이블을 보고자 하나, 현재 배타적 잠금이 SESSION1

-- 에 의해서 배타적 잠금이 걸려 있어서 볼 수 없다.

 

 

위에서(두번째 세션1 처리) COMMIT TRAN 문을 실행하자마자, 기다렸다는 듯이 SESSION 2의

명령은 실행된다. 실제로도 기다리고(BLOCKED) 있었던 것이 맞다.

이렇게 배타적 잠금은 다른 잠금을 허용하지 않는다. 즉 다른 배타적 잠금이나 공유 잠금을

시도하려는 모든 연결을 허용하지 않는다는 것이다. 이것을 호환이 되지 않는다고 말한다.

배타적 잠금은 다른 잠금과 호환되지 않는다.

 

이번엔 좀 다른 경우를 보자. 다시 그림1-1과 같은 트랜잭션을 실행한다.

그리고 이제 SESSION 2에서 다르게 명령을 내려본다. 먼저 그림1-5를 보자.

SESSION 2에서 실행하는 명령이다.

 

 

INSERT ISOL_1(INAME) VALUES('BBB')

INSERT ISOL_1(INAME) VALUES('CCC')

 

BEGIN TRAN

UPDATE ISOL_1

SET INAME = 'ZZZZ'

WHERE IID = 1

-- 1) ISOL_1 테이블에 대해 잠금이 걸려 있다.

 

-- 2) 현재 활성화된(커밋되지않은) 트랜잭션의 프로세스ID를 확인한다.

 

DBCC OPENTRAN

 

-- 3) 프로세스ID의 잠금 정보를 확인한다.

 

EXEC SP_LOCK 54

 

ISOL_1 테이블에 데이터 두 개를 더 입력하고 다시 트랜잭션을 시작해서 UPDATE 문을 IID 1번에

대해 실행하고 있다. 그리고 현재 설정된 잠금의 상태를 보기 위해서 EXEC SP_LOCK 명령을

사용하고 있다. 그런데, 그냥 SP_LOCK을 실행하면 모든 연결에 대한 잠금 정보가 모두 출력되어

보기가 불편하므로, DBCC OPENTRAN 명령을 사용해서 현재의 프로세스ID만을 구해서,

해당 프로세스 ID를 매개변수로 해서 SP_LOCK 명령을 실행하고 있는 것이다.

 

그 중 잠금에 대한 구체적인 정보가 TYPE 컬럼과 MODE 컬럼의 값이다. TYPE 은 잠금의 형태를

말하는 것이고, MODE 는 잠금의 종류를 말하는 것이다. 기본적으로 SQL Server 가 사용하는

잠금의 형태는 KEY 레벨 잠금 혹은 ROW 레벨 잠금이다. 사실 둘은 같은 뜻이며 둘 다 행 단위 잠금을 말한다.

다만 잠금이 걸리는 테이블에 클러스터드 인덱스가 있으면

KEY 레벨 LOCK 이라고 부르고, 클러스터드 인덱스가 없으면 ROW 레벨 LOCK 이라고 부를 뿐이다.

이것은 용어상의 문제일 뿐 결국 같은 뜻이다.

그래서, 만약 현재 걸린 잠금이 기본 잠금인 행 단위 잠금이라고 한다면,

다음 아래쿼리의 명령은 실행되어야 할 것이다. 그러나 실행은 실패하고 역시 BLOCKED 되게 된다.

아래 쿼리의 명령은 SESSION 1에서 실행하는 작업이다.

 

 

SELECT * FROM ISOL_1 WHERE IID = 4

-- 없는 데이터인 4번을 보려고 하고 있지만, ISOL_1 테이블자체에

-- 잠금이 걸려있으므로 여전히 BLOCKED 되고 있다.

 

 

SESSION 2에서는 IID 4번을 SELECT 하려고 시도하고 있다.

그리고 SQL Server 의 기본 잠금단위는 행 단위 잠금이다. 그렇다면, 현재 ISOL_1 테이블의

IID 1번에 대해서만 LOCK 이 설정되어 있다는 뜻이다. 우리가 보려고 하는 것은 없는 데이터 4번이다.

그러나, 보지 못하고 대기 상태에 있다.

잠금의 종류 즉 MODE 컬럼을 보자. 현재 개체ID 1220199397 에 대해서 어떤 잠금이 걸려져 있는가?

 

X 가 포함된 것은 모두 배타적 잠금이다. 개체ID 1220199397 에 대해서 현재,

 

- “TAB" 즉 테이블에 대해 배타적 잠금이,

- “PAG" 즉 데이터가 있는 페이지에 대해서 배타적 잠금이,

- “RID" = ROW ID 즉, 행에 대해서 배타적 잠금이

 

설정되어 있는 것을 확인할 수 있다.

 

만약 RID 에 대해서만 LOCK 이 걸려 있다면, 위의 SELECT 문은 실행되었을 것이다.

그러나 현재 TABLE 에 대해 배타적 잠금이 걸려져 있는 상태이기 때문에 위의 SELECT 문은

실행되지 않는 것이다. 일반적으로 어떤 개체에 변경작업을 하면 개체자체에 배타적 잠금을

건다는 것을 여기서 알 수 있겠다. 다시 SESSION 1을 COMMIT 해서 트랜잭션을 완료하자.

 

INSERT ISOL_1(INAME) VALUES('BBB')

INSERT ISOL_1(INAME) VALUES('CCC')

 

BEGIN TRAN

UPDATE ISOL_1

SET INAME = 'ZZZZ'

WHERE IID = 1

-- 1) ISOL_1 테이블에 대해 잠금이 걸려 있다.

 

-- 2) 현재 활성화된(커밋되지않은) 트랜잭션의 프로세스ID를 확인한다.

DBCC OPENTRAN

 

-- 3) 프로세스ID의 잠금 정보를 확인한다.

EXEC SP_LOCK 54

 

-- 4) 트랜잭션 완료

COMMIT TRAN

-- 5) 현재 연결에 대해 잠금정보 표시

EXEC SP_LOCK 54

 

SESSION 1의 트랜잭션을 COMMIT 하고 다시 위의 명령을 실행해서 잠금 정보를 확인하면,

현재 ISOL_1 테이블에 걸려있는 잠금은 하나도 없음이 확인된다.

 

 

 

 

1) 잠금의 단위

 

SQL Server 가 잠금을 설정할 수 있는 개체의 단위에는 다음과 같다.

 

1] RID : 행 식별자이다. 테이블 내에서 행 하나를 잠글 때 사용된다.

2] 키(key) : 인덱스가 있을 때의 행 잠금단위이다. 키 범위를 보호하기 위해 사용한다.

3] 페이지(page) : 8KB 데이터 페이지 또는 인덱스 페이지이다.

4] 익스텐트(extent) : 인접한 8개의 데이터 페이지 또는 인덱스 페이지 이다.

5] 테이블 : 데이터와 인덱스가 포함된 전체 테이블을 뜻한다.

6] DB : 데이터베이스

위와 같은 단위에 대해서 SQL Server 는 상황에 따라 공유 잠금, 배타적 잠금등을 설정할 수 있다.

 

2) 잠금의 종류

 

SQL Server 의 잠금의 종류는 다음과 같다.

 

 

1] SHARED(S) - 읽기 동안에만 일어나며, 다른 S 락에 대해서는 공유하나 X 에 대해서는 배타적이다.

데이터를 변경하거나 업데이트하지 않는 작업(읽기 전용 작업)에 사용한다.

공유(S) 잠금을 사용하면, 여러 트랜잭션이 동시에 하나의 개체를 읽을(SELECT) 수 있다.

즉 공유 잠금은 다른 공유 잠금과 호환된다. 공유 잠금끼리는 서로 충돌되지 않는 것이다.

그러나, 특정 리소스에 공유 잠금이 설정되어 있는 동안에는, 다른 트랜잭션이 데이터를 변경할

수는 없다. 즉 공유 잠금은 다른 배타적 잠금을 허용하지 않으며 호환되지 않는다고 말한다.

한편, 리소스에 대한 공유 잠금은 다음 경우를 제외하고는, 데이터를 읽자마자 바로 해제된다.

- 트랜잭션 고립 수준을 REPEATABLE 레벨 이상으로 설정

- LOCK HINT를 써서, 해당 공유 잠금을 트랜잭션 끝까지 유지한다.

LOCK HINT를 이용하면 잠금은 계속 유지된다.

 

 

BEGIN TRAN

-- 1) 1번부터 10번까지 SELECT..

SELECT * FROM EMP

WHERE EID BETWEEN 1 AND 10

-- 그러나, 1) 의 SELECT 문은 공유잠금이 풀린상태이다.

 

위의 쿼리는 트랜잭션을 시작하고, 직원테이블의 직원ID(EID)가 1번~10인 데이터를 조회하는 명령을

실행한 것이다. 그러나 1)명령으로는 이 예제를 성공적으로 확인할 수 가 없다. 그것은 이미 잠금이

풀렸기 때문이다. 1)의 SELECT 문을 실행하면 바로 결과를 볼 수 있다. 그 결과가 출력되는 동안만

잠금이 걸리는 것이다. 아주 짧은 시간이다. 이것이 SELECT 문에 걸리는 잠금이다. 그러므로

위의 쿼리의 SP_LOCK 을 실행하면, EMP 테이블에 대해 아무런 잠금도 걸려있지 않은 것을 확인할

수 있을 것이다.

 

그러나, 잠금을 유지하도록 하는 잠금 힌트가 있다. 바로 HOLD LOCK 옵션이다. 이 옵션을 실행하면

SELECT 문을 실행하는 동안에만 잠금이 설정되었다고 결과가 출력되면 바로 끝나는 것이 아니라,

배타적 잠금처럼 트랜잭션이 끝날 때까지 공유 잠금이 유지된다. 아래의 쿼리가 바로 그 예이다.

 

 

<SESSION1>

BEGIN TRAN

-- 1) 1번부터 10번까지 SELECT..

SELECT * FROM EMP ( HOLDLOCK)

WHERE EID BETWEEN 1 AND 10

-- 공유 잠금은 계속 유지되고 있다.

 

SP_LOCK

 

잠금이 계속 유지되고 있으므로 이번엔 SP_LOCK 명령에서 EMP 테이블에 걸린 잠금 정보에 대한

결과물이 출력된다. RangeS-S 잠금은 BETWEEN 명령 때문에 생긴 것이다. EID 1번~10번까지의

범위가 잠금이 설정되어 있기 때문에, 해당 데이터 10개에 대해서 각각 KEY 레벨 잠금이 RangeS-S

로 설정되어 있는 것이다. 모두 S LOCK 이다. 그리고 TABLE 레벨과 PAGE 레벨에도 전부 공유 잠금이

유지되고 있는 것을 볼 수 있다. 그러면, 공유 잠금에 대해 다른 연결이 실행하는 것을 확인할 수가 있겠다.

 

<SESSION2>

SELECT * FROM EMP

 

이상 없이 EMP 테이블전체에 대한 SELECT 문이 실행되는 것을 확인할 수 있다. 테이블 단위,

페이지 단위, 행 단위 모두 공유 잠금이기 때문에 다른 공유 잠금과 호환이 되어서 실행이 제대로 된다.

그러면, SESSION 2에서 다음과 같이 실행해 보자.

 

<SESSION2>

-- 1) EMP 테이블 전체를 조회

SELECT * FROM EMP

 

-- 2) EMP 테이블의 5번 데이터를 수정

UPDATE EMP

SET SAL = 900000

WHERE EID = 5

 

EID 5번에 대해서 데이터를 수정하고 있다. EID 5 번은 그러나 SESSION 1에 의해 현재 공유 잠금이

걸려있는 상태다. 그래서 해당 데이터는 KEY 레벨 공유 잠금이 설정되어 있기 때문에 수정할 수

없는 것이다. 이것을 공유 잠금은 배타적 잠금과 호환되지 않는다고 말한다. 역시 EID 1번~10번외의

데이터를 수정하려 해도, 테이블전체에 공유 잠금이 걸려있기 때문에 데이터 수정은 실패하게 된다.

 

이제 LOCKING 상태를 해결해야 하는데, 먼젓번엔 트랜잭션을 COMMIT 하는 방법으로 해결했지만,

이번에는 다른 방법으로 해결해보도록 하겠다.

 

 

프로세스 관리

EM에서 관리-현재동작-잠금/프로세스 ID를 보면 잠금 정보를 그래피컬하게 확인할 수 있다.

 

잠금 정보를 보니 현재 51번,52 번 프로세스가 빨간 색으로 표시가 되어 있는데, 그중 51번 프로세스는

차단주체(BLOCKED) 라고 되어 있고, 52번 프로세스는 차단하는 중(BLOCKING)이라고 되어있다.

어떤 것이 잠금을 설정한 프로세스이고 어떤 것이 해당 잠금 때문이 실행이 거부되고 있는 프로세스일까?

물론 해당 프로세스를 더블클릭하면 정보를 볼 수 있다. 그러나, 여기서 충분히 예상가능하다.

 

느낌표(!)가 표시되어 있는 것이 현재 잠금을 설정한 프로세스이다. 즉 “SELECT ...(HOLDLOCK)...“을

실행한 명령임을 알 수 있다. 그리고 목에서 출혈(?)이 나고 있는 듯한 그림을 가진 프로세스가 바로

엑세스를 거부당한 프로세스이다. 그럼 BLOCKED 된 프로세스인 51번 프로세스를 더블클릭해서

확인해 보자.

 

--EM 에서 직접 정보들을 보면서 비교

 

프로세스 중지” 단추를 누르면, 해당 프로세스 51번이 KILL 되면서 UPDATE 문은 ROLLBACK 된다.

프로세스를 KILL 할 수 있는 권한은 기본적으로 SYSADMIN 서버역할과 DB_OWNER 서버역할의 멤버들,

그리고 PROCESSADMIN 서버역할의 멤버들에게만 부여된다.

또 프로세스를 KILL 하는 명령은 “KILL" 명령으로 실행할 수 도 있다. 단 해당 트랜잭션 내에서는 KILL 명령을

사용할 수 없다.

 

KILL 52

 

 

* LOCK_TIMEOUT 옵션

 

잠금이 걸린 상태를 생각해 보자. HOLDLOCK 옵션에 의한 SELECT 문의 공유 잠금은 트랜잭션이

끝날 때까지 계속 유지될 것이고, 그러므로 SESSION2의 UPDATE 문은 SESSION1의 트랜잭션이

끝날 때까지 계속 거부되어 사용할 수 없을 것이다. 그래서 이런 상황에서 SESSION1의 트랜잭션이

끝나거나 KILL 되길 무조건 기다리는 것이 아니라, 얼마동안만 기다렸다가 그 기간이 끝나면 SESSION1의

트랜잭션이 끝나지 않아도 자동으로 기다리기를 멈추고 작업을 종료하게끔 할 수 있다는 것이다.

그것을 가능하게 하는 명령이 LOCK_TIMEOUT 명령이다.

 

 

<SESSION1>

BEGIN TRAN

-- 1) 1번부터 10번까지 SELECT..

SELECT * FROM EMP ( HOLDLOCK)

WHERE EID BETWEEN 1 AND 10

-- 공유 잠금은 계속 유지되고 있다.

 

SP_LOCK

 

 

<SESSION2>

-- 1) EMP 테이블 전체를 조회

SELECT * FROM EMP

 

-- 2) EMP 테이블의 5번 데이터를 수정

UPDATE EMP

SET SAL = 900000

WHERE EID = 5

 

<LOCK_TIMEOUT>

-- 5초만 기다렸다가, 자동으로 프로세스를 중자하게 한다.

-- 1000 은 1초를 뜻한다.

SET LOCK_TIMEOUT 5000

 

UPDATE EMP

SET SAL = 900000

WHERE EID = 5

 

SESSION2 대신 위와 같이, 먼저 해당 연결에 대해서 LOCK_TIMEOUT 설정을 하고, 트랜잭션을 실행하게

되면, BLOCKED 되었다고 하더라도 마냥 기다리지 않고 설정한 시간 후에 바로 실행을 중지하게 된다.

이것은 잠금에 대한 대기시간을 짧게 함으로써, 다른 명령을 실해할 수 있도록 SESSION을 OPEN 하는

결과를 가져온다. 대기시간이 길어질수록 사용자는 시스템의 성능자체를 의심하게 될 것이다.

시간의 설정은 밀리세컨드 단위로 설정된다.

--쿼리분석기 하단에서 확인

기본적으로 모든 사용자에게 SET LOCK_TIMEOUT 사용 권한이 부여된다.

 

또한, 위와 같이 설정해서 LOCK_TIMEOUT 설정보다 오래 기다린 경우, 차단된 명령문은 자동으로

취소되고 오류 메시지 1222 "잠금 요청 제한 시간이 초과되었습니다"가 응용 프로그램으로 반환된다.

그러나 명령문을 포함하는 모든 트랜잭션이 SQL Server에 의해 ROLLBACK 되거나 취소되는 것은

아니다. 따라서 응용 프로그램에는 오류 메시지 1222를 잡을 수 있는 오류 처리 루틴이 있어야 한다.

응용 프로그램이 오류를 잡지 않으면 트랜잭션 내의 각 명령문이 취소된 것을 모른 채 계속 진행하여

트랜잭션의 명령문이 나중에 실행되지 않은 명령문을 참조할 경우 오류가 발생할 수 있기 때문이다.

 

-- 5초만 기다렸다가, 자동으로 프로세스를 중자하게 한다.

-- 1000 은 1초를 뜻한다.

SET LOCK_TIMEOUT 5000

 

UPDATE EMP

SET SAL = 900000

WHERE EID = 5

 

IF @@ERROR = 1222

BEGIN

EXEC MASTER..XP_CMDSHELL 'NET SEND HAN 수정실패'

-- WINDOWS 2000의 EVENT LOG 에 해당 에러를 기록한다.

RAISERROR ( 'EMP 에 대한 SAL 컬럼수정이 실패함',

10,1) WITH LOG

END

GO

 

 

 

2] EXCLUSIVE(X)

 

배타적(X) 잠금이 설정되면 동시에 여러 연결이 한 리소스에 액세스할 수 없게 된다. 오직 하나의

연결만이 해당 리소스를 점유한다. 이 잠금은 수정 시에, 행단위로 설정되게 된다.

예를들어 누군가 특정 직원테이블의 데이터 중 “한창현”이라는 데이터를 변경

(insert, update, delete)하고 있다고 하자. 그 상황에서 배타적 잠금이 해당 테이블(직원)의

해당 행(한창현인 데이터)에 걸리게 되는 것이다. 그러면 해당 행을 엑세스하려는 모든 다른 연결은,

그것이 읽기 위한 것이든 수정을 위한 것이든 모두 거부되고 Blocking 당하게 된다.

 

배타적 잠금으로 잠근 데이터는 다른 트랜잭션이 읽거나 수정할 수 없다. 이것은 공유 잠금에도 적용된다.

배타적 잠금은 그래서 공유 잠금을 포함한 대부분의 락에 대해서 배타적이고 호환되지 않는다.

 

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 400000

WHERE EID = 1

 

 

<SESSION2>

-- 1) 해당 데이터를 엑세스하려 하므로, BLOCKED 됨

SELECT * FROM EMP WHERE EID = 1

 

-- 2) EID 1번을 엑세스하려는 것이 아니므로, 실행됨

SELECT * FROM EMP WHERE EID = 8

 

SESSION1에서 EID 1번에 대해서는 엑세스가 안되지만, 8번에 대해서는 무리없이 실행된다.

이것은 SQL Server 가 기본적으로 행하는 잠금이, 행 단위 잠금이라는 것을 확실히 보여준다.

 

 

 

3] UPDATE(U)

 

수정 시에 X 락을 걸기 전 데이터영역을 읽기 위해 거는 락이다. 해당 리소스에 대한 잠금을 미리 체크하여

데드락(교착상태)을 방지하기 위해서 사용된다. 공유 잠금과 호환된다.

 

업데이트(U) 잠금을 사용하면 일반적인 형태의 교착 상태가 방지된다.

일반적인 업데이트 패턴은 레코드를 읽고, 리소스(페이지 또는 행)에 대한 공유(S) 잠금을 얻은 다음 행을

수정하는 트랜잭션으로 구성되는데, 행을 수정할 때는 먼저 배타적 잠금으로 잠금을 변환해야 한다.

두 트랜잭션에서 리소스에 대해 공유 모드 잠금을 얻은 다음 데이터를 동시에 업데이트하려고 하면

한 트랜잭션이 배타적 잠금으로 잠금을 변환하려 할 것이다. 이 때, 한 트랜잭션의 배타적 잠금은

다른 트랜잭션의 공유 모드 잠금과 호환되지 않으므로, 공유 모드를 단독 모드로 변환할 때는

잠금 대기가 발생하게 된다. 그리고 두 번째 트랜잭션도 해당 업데이트에 대해 배타적 잠금을

얻으려고 할 것이다. 이 경우 두 트랜잭션 모두 배타적 잠금으로 변환 중이고 각각 상대 트랜잭션이

공유 모드 잠금을 해제하기를 기다리므로 교착 상태가 발생하는 것이다.

 

이러한 교착 상태를 방지하기 위해서 SQL Server는 업데이트 잠금을 사용한다. 이 업데이트 잠금은

한 번에 한 트랜잭션만 리소스에 대한 업데이트 잠금을 얻을 수 있게 하기 때문에 교착상태가

방지될 수 있다. 트랜잭션이 리소스를 수정하면 업데이트 잠금이 배타적 잠금으로 변환되고

그렇지 않으면 잠금이 공유 모드 잠금으로 변환된다. 그러나 업데이트 잠금을 사용하더라도 교착상태를

완전히 방지할 수는 없다. 그것은 모든 RDBMS 에서도 마찬가지이다.

 

 

4] INTENT(I)

 

공유 잠금 OR 배타적 잠금이 걸린 특정 데이터영역에 표시하는 알림간판이라고 생각하면 된다.

의도적 잠금이라고 하며 다른 INTENT 잠금 들과 호환된다.

 

의도적 잠금은 SQL Server가 리소스에 대해 공유 잠금 또는 배타적 잠금을 얻으려 할 때 같이 발생한다.

예를 들어, 의도적 공유 잠금(IS)을 테이블 수준에서 설정하려고 한다면, 이것은 해당 트랜잭션이

해당 테이블의 페이지 또는 행에 대해 공유 잠금을 설정하려고 한다는 것을 의미한다.

이렇게 테이블 수준에서 의도적 잠금을 걸면, 이후에 다른 트랜잭션이 해당 페이지를 포함하는 테이블에

대해 배타적 잠금을 얻을 수 없게 된다.

 

SQL Server는 테이블 수준에서만 의도적 잠금을 확인하여, 트랜잭션이 해당 테이블에 대해 잠금을

얻을 수 있는지 확인하므로 의도적 잠금을 사용하면 성능이 향상된다. 왜냐하면, 직접 테이블의 모든 행

또는 페이지 잠금을 확인할 필요가 없기 때문이다.

생각을 해보자. 우리가 살고 있는 집엔 방(TABLE)이 있다. 그 방 중 어떤 방에 들어가서 방을 사용하려고

하는 데, 벌써 누군가 그 방을 쓰고 있다. 이때 그 방을 쓰는 사람이 방문에다 “방청소 중“ 이라고 방문에다

알림판을 하나 걸어 놓는 다면, 우리는 굳이 그 방을 두드릴 필요도 없고 방문을 열어볼 필요도 없다.

다만 방청소가 끝날때까지(트랜잭션 완료) 방바깥에서 기다리면 되는 것이다. 그만큼 우리가 덜 움직여도

된다는 것이다. 그럼으로 해서 성능이 향상되는 효과를 볼 수 있다.

 

BEGIN TRAN

UPDATE EMP

SET SAL = 1

WHERE EID = 1

DBCC OPENTRAN

EXEC SP_LOCK 51 -- 현재 EMP 테이블의 개체ID는 695673526 이다.

ROLLBACK

 

의도적 잠금은 세 가지로 나누어지는 데, 그 종류는 다음과 같은 것들이 있다.

 

- 의도적 배타적 잠금(IX)

잠금을 걸려는 트랜잭션이 각 리소스계층(테이블,페이지,행등...)에 대해 X 잠금을 설정하여 계층의

아래쪽에 있는 일부 리소스를 수정하려 하는 것을 말한다. IX는 IS의 상위 집합이 된다.

 

- 공유 및 의도적 배타적 잠금(SIX)

잠금을 걸려는 트랜잭션이 각 리소스계층에 대해 IX 잠금을 설정하여 계층의 아래쪽에 있는 모든 리소스에

대해서는 읽기 작업을 하고, 일부 리소스에 대해서는 수정작업을 하려고 하는 것을 말한다.

최상위 수준 리소스에서는 동시 IS 잠금이 허용된다.

예를 들어, 테이블에 대한 SIX 잠금은 테이블에 대해 SIX 잠금을 설정하여 동시 IS 잠금을 허용하고,

수정 중인 페이지에 IX 잠금을 설정하고 수정된 행에 대해서는 X 잠금을 설정한다. 각 리소스 당

한 번에 하나의 SIX 잠금을 설정할 수 있으므로 다른 트랜잭션이 테이블 수준에서 IS 잠금을 얻어

계층 아래쪽에 있는 리소스를 읽을 수 있게 된다. 그라나, 그 상황에서 다른 트랜잭션이 리소스를

수정할 수는 없게 된다

 

 

 

5] SCHEMA(Sch)

 

스키마 잠금은 다음 두 가지로 나누어서 설명된다.

 

- Sch-M (Schema Manipulation)

스키마를 변경하는 작업 즉, DDL 문 실행 시에 SQL Server 가 SCHEMA 자체에 대해서 건다.

이 잠금은 모든 잠금에 대해서 배타적이며, 어떤 작업도 허락하지 않는다.

그도 그럴것이 데이터베이스의 구조를 변경하는 누군가 그 데이터베이스 내의 데이터를 엑세스해서는

안될 것이기 때문이다. 이것을 스키마 변경 잠금이라고 한다.

 

- Sch-S (Schema Stability)

쿼리문 컴파일 시에만 발생한다. S or X 와 호환된다. 스키마 안정성(Sch-S) 잠금은 의도적 잠금 등

다른 트랜잭션 잠금을 차단하지 않는다. 따라서 쿼리가 컴파일되는 동안에도, 테이블에 대한

의도적 잠금을 포함하여 다른 트랜잭션을 계속 실행할 수 있다. 그러나 해당 테이블에서의 DDL 작업은

수행할 수는 없다.

 

참조) 그림.. 호환성.bmp

 

SQL Server 2000은 한 트랜잭션으로 여러 유형의 리소스를 잠글 수 있는 세분화된 잠금의 단계를

제공한다. 잠금에 대한 비용을 최소화하기 위해 SQL Server는 자동으로 해당 작업에 맞는 수준에서

리소스를 잠근다. 행과 같이 작은 수준에서 잠그면 동시성이 많이 향상된다. 그러나, 많은 행을 잠글

경우엔 더 많은 잠금을 보유해야 하므로 오버헤드가 늘어날 수 밖에 없다.

이때 테이블과 같이 큰 수준에서 잠그면 테이블자체를 잠궈서 다른 트랜잭션이 테이블자체를

액세스하지 못하게 제한하므로, 동시성은 떨어지지만 처리할 잠금 수가 적으므로 오버헤드는

줄어들게 된다. 이것은 상황에 따라 SQL Server 의 선택은 자동으로 처리된다. SQL Server 의

기본 잠금 단위는 행 단위 잠금이다.

 

잠금의 단계에 따른 동시성과 비용을 그래프로 확인.

참조) 그림.. 잠금대비동시성.bmp

 

 

 

 

 

2. 잠금의 수준

 

잠금은 그 레벨에 대해서 SQL-92 표준을 가지고 있다. 이것은 SQL Server 에만 적용되는 것이 아니고,

모든 RDBMS에서 자신들의 제품에 맞도록 적용하는 기준이다. 잠금수준의 표준은 네 가지가 있으며

SQL Server 2000 은 네 가지 모두를 지원한다. 오라클은 그 중 두 수준만을 지원한다.

이러한 잠금 수준은 트랜잭션 고립수준, 혹은 트랜잭션 격리수준 혹은

TRANSACTION ISOLATION LEVEL 이라고 부른다.

 

다음은 잠금 수준에 대한 내용을 설명한 것인데, 그 수준은 다음과 같이 레벨을 가진다.

오른쪽으로 갈수록 잠금의 정도가 심함을 나타낸다.

 

READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE

 

 

1) READ COMMITTED

 

가장 낮은 단계인 READ UNCOMMITTED부터 설명해야 함이 옳으나, READ COMMITTED 가

SQL Server 의 기본 잠금 레벨이므로, READ COMMITTED부터 설명하는 것이 이해가 더 빠르리라 생각된다.

READ COMMITTED 는 말그대로, COMMIT 된 트랜잭션만을 읽자는 것이다.

이것은 잠금을 건 트랜잭션에서의 입장이 아니다. 잠금을 건 트랜잭션에 대해서 SELECT 하려는

또 다른 트랜잭션에서의 입장이다. 현재는 해당 트랜잭션에 대해 잠금이 걸려 있으면 해당 잠금의

종류에 따라 볼 수도 있고 보지 못할 수도 있다. 공유 잠금에 대해서는 볼 수 있으며, 배타적 잠금이 걸려

있으면 볼 수 없는것이 현재 상태이다. SELECT 한다는 것은 공유 잠금이고, 공유 잠금은 공유 잠금과는

호환되지만 배타적 잠금에 대해서는 호환되지 않기 때문이다.

 

이것이 READ COMMITTED 의 뜻이다. 배타적 잠금에 대해서 공유 잠금을 허용하지 않겠다는 뜻이다.

SQL Server 의 기본 잠금 수준이기 때문에 현재, SESSION1과 SESSION2의 1)번을 실행했을 때의 상황이

발생하게 되는 것이다.

----------------------------------

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 400000

WHERE EID = 1

 

 

<SESSION2>

-- 1) 해당 데이터를 엑세스하려 하므로, BLOCKED 됨

SELECT * FROM EMP WHERE EID = 1

 

-- 2) EID 1번을 엑세스하려는 것이 아니므로, 실행됨

SELECT * FROM EMP WHERE EID = 8

---------------------------------------

 

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 80000

WHERE EID = 1

SP_LOCK -- 배타적 잠금이 걸려 있음이 확인된다

 

 

<SESSION2>

 

SELECT * FROM EMP

WHERE EID = 1

-- SESSION1에서 EMP 테이블에 배타적 잠금을 설정했고,

-- 트랜잭션이 끝나지 않았으므로, 볼수 없다.

-- 잠금 설정이 되지 않은 나머지 행들은 볼 수 있다.

 

SESSION1에서 현재 개체ID 695673526 에 대해 KEY 단위로 X 잠금이 설정되어 있는 것을 볼 수 있다.

물론 이 개체에는 PAGE 와 TABLE 단위에 IX 잠금이 설정되어 있으나, 이 IX 잠금은 IS 잠금과 호환이

되기 때문에 다른 트랜잭션에서의 SELECT 작업을 허용한다. 해당 트랜잭션에서 IX 잠금이 설정되면,

업데이트 중인 행을 제외한 일부 행을 다른 트랜잭션이 읽거나 업데이트할 수 있다.

SESSION2의 예제에서는 해당 행을 보려고 했기 때문에 대기 상태가 된것이다.

 

 

 

2) READ UNCOMMITTED

 

READ UNCOMMITTED 수준은 잠금의 4가지 레벨 중 가장 낮은, 즉 가장 느슨한 단계의 잠금 수준이다.

이 수준은 특정 트랜잭션에서 배타적 잠금을 설정하고 있어도, 해당 데이터를 볼 수는 있게 한다.

물론 해당 리소스에 대한 수정작업은 모든 잠금 수준에서 불가능하다.

READ UNCOMMITTED는 배타적 잠금이 설정된 리소스(데이터)라고 하더라도, 볼 수(SELECT) 있게 하는,

잠금 중 가장 느슨한 단계이다. 배타적 잠금을 공유 잠금과 호환되게 하는 것이라고 보면 되겠다.

먼저 예제를 위한 테이블 만들기이다.

 

CREATE TABLE ISOL_2( IID INT PRIMARY KEY , INAME CHAR(5))

GO

INSERT ISOL_2(IID,INAME) VALUES(1, 'AAAAA')

INSERT ISOL_2(IID,INAME) VALUES(2, 'BBBBB')

INSERT ISOL_2(IID,INAME) VALUES(3, 'CCCCC')

INSERT ISOL_2(IID,INAME) VALUES(6, 'FFFFF')

INSERT ISOL_2(IID,INAME) VALUES(7, 'GGGGG')

INSERT ISOL_2(IID,INAME) VALUES(8, 'HHHHH')

INSERT ISOL_2(IID,INAME) VALUES(9, 'IIIII')

INSERT ISOL_2(IID,INAME) VALUES(10, 'JJJJJ')

GO

SELECT * FROM ISOL_2

 

8 개의 데이터가 ISOL_2 테이블에 입력되어 있으며, 중간에 IID 컬럼값 4번과 5번 데이터는 비어있다.

다음 예제는 트랜잭션 잠금 수준을 변경하고 트랜잭션을 시작하는 예제이다.

 

<SESSION1>

 

-- 1) IID 10번 행에 대해, 배타적 잠금을 발생하는 트랜잭션 실행

BEGIN TRAN

UPDATE ISOL_2

SET INAME = 'ZZZZZ'

WHERE IID = 10

EXEC SP_LOCK

 

SESSION1을 보면, 현재 1) 번의 명령을 실행했기 때문에, IID 10번에 대해 배타적 잠금이 설정되어 있다.

이번에도 다른 연결을 열어서 해당 트랜잭션에 대해 접근을 해보자.

 

 

<SESSION2>

-- 1) 트랜잭션 잠금 수준을 READ UNCOMMITTED 로 변경

SET TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED

-- 2) 트랜잭션 고립수준 확인

DBCC USEROPTIONS

GO

-- 3) 배타적 잠금이 설정된 IID 10번 행을 SELECT 한다.

SELECT * FROM ISOL_2 WHERE IID = 10

-- 4) 테이블의 모든 행을 SELECT 한다.

SELECT * FROM ISOL_2

 

트랜잭션 고립 수준, 즉 잠금 수준을 변경하는 명령은,

“ SET TRANSACTION ISOLATION LEVEL ..." 이다. 이 명령을 실행하고, 현재 연결에 대한 트랜잭션

고립 수준을 확인하려면, “ DBCC USEROPTIONS"를 사용하면 된다.

” SET TRANSACTION ISOLATION..." 명령을 한 번이라도 실행하지 않으면,

“ DBCC USEROPTIONS"명령을 실행하더라도 잠금 수준 정보를 볼 수 없다.

그러나 그것은 기본값인 READ COMMITTED 로 설정되어 있다는 말이므로 혼동할 필요는 없다.

 

SESSION 2에서 3)번을 실행하건 4)번을 실행하건, 모두 잘 실행되고 있음을 볼 수 있다.

이것은 SESSION 2 트랜잭션이 SESSION 1 트랜잭션의 ISOL_2 개체의 각 행에 대해 공유 잠금을

잘 설정했다는 뜻이다.

READ COMMITTED 설정에서는 허락되지 않는 일이었지만, READ UNCOMMITTED 에서는 느슨하게

단계를 낮춤으로서 보는 것이 가능하도록 되어 있다

 

 

 

3) REPEATABLE READ

 

읽는 데이터의 일관성에 관한 얘기이다. 잠금이 설정되어 있을 때, 트랜잭션 시작 전과 시작 후의 데이터를

동일하게 읽게 함으로써, 읽는 데이터의 일관성을 보장하는 방법이 된다.

 

<SESSION1>

BEGIN TRAN

UPDATE ISOL_2

SET INAME = 'YYYYY'

WHERE IID = 8

DBCC OPENTRAN

EXEC SP_LOCK 51

 

 

commit tran

 

SESSION1에선 현재 SESSION 1 연결이 트랜잭션을 시작하고, IID 8번에 대해서 수정작업을 실행하고

있다. 그러므로 이 행엔 배타적 잠금이 설정되어 있다.

 

<SESSION2>

-- 1) 트랜잭션 잠금 수준을 READ UNCOMMITTED 로 변경

SET TRANSACTION ISOLATION LEVEL

READ COMMITTED

-- 2) 트랜잭션 고립수준 확인

DBCC USEROPTIONS

GO

-- 3) 배타적 잠금이 설정되지 않은 IID 7 번 행을 SELECT 한다. 잘 실행된다.

SELECT * FROM ISOL_2 WHERE IID = 7

-- 4) 잠금이 설정된 8번행을 포함한 범위검색을 한다. 대기상태가 된다...

SELECT * FROM ISOL_2

WHERE IID BETWEEN 7 AND 10

 

SESSION 1에서 실행한 수정 때문에 배타적 잠금이 설정되어 있으므로, SESSION2의 명령은 거부되고

대기중인 상태이다. 이것이 READ COMMITTED 잠금 수준이다.

 

그러나, 만약 SESSION 1이 시작되기 전에 SESSION 2의 4)번 명령을 실행했다고 생각해보자.

그때는 잠금이 없으므로 잘 보일 것이다. 그리고 SESSION 1의 명령을 실행하고 또 보자.

이 때는 보이지 않는다. 그렇다고 해서 READ UNCOMMITTED 로 설정하면 볼 수는 있지만, 해당 정보는

불확실한 정보가 된다는 것이다.

즉, READ UNCOMMITTED 로 설정했을 때 보는 것은 가능하지만, 그때 보는 데이터는 SESSION 1에서

COMMIT을 하느냐 ROLLBACK을 하느냐에 따라서 결정되지 않은 데이터를 보는 것이다.

SESSION 1에서 COMMIT을 했으면 다행이 제대로 된 데이터를 보는 것이지만, SESSION 1에서

ROLLBACK을 했다면 SESSION 2의 4)에서 본 데이터는 결국은 잘못된 정보인 것이다.

 

오라클의 잠금 수준은 기본적으로 READ UNCOMMITTED 로 되어 있기 때문에 이것을 가능하게 한다.

그러나, SQL Server 는 가능하지 못하도록 READ COMMITTED 가 기본설정값이다.

그래서 REPEATABLE READ 수준이 있다. REPEATABLE READ 로 잠금 수준을 설정하면,

트랜잭션이 아직 끝나지 않았을 때 변경전과 똑같은 데이터를 보도록 한다.

데이터가 변경된 것이 확실하게 완료되기 전까지는, 변경이 적용되기 전의 데이터를 일관성있게

보게 하겠다는 것이다. 그러므로 이것은 좀 더 데이터베이스 일관성을 보장해 준다.

그러므로 아래의 쿼리에서 SELECT 한 데이터는 변경되기 전의 데이터를 보여준다.

 

<SESSION2>

-- 1) 트랜잭션 잠금 수준을 REPEATABLE READ 로 변경

SET TRANSACTION ISOLATION LEVEL

REPEATABLE READ

-- 2) 트랜잭션 고립수준 확인

DBCC USEROPTIONS

GO

 

-- 3) 잠금이 설정된 9번행을 포함한 범위검색을 한다.

-- SESSION 1의 변경이 적용되기 전의 데이터를 보여준다...

SELECT * FROM ISOL_2

WHERE IID BETWEEN 7 AND 10

 

REPEATABLE READ 수준은 트랜잭션 진행 중인 개체에 엑세스해서 데이터를 읽으려고 할 때,

읽고자 하는 데이터에 일어나고 있는 변경이 성공될지 취소될지 불확실한 상태에서,

확실하게 트랜잭션이 완료되기 전까지는, 변경전의 데이터를 읽게 함으로써 데이터의 일관성을

보장해 주는 기법이다.

 

 

 

4) SERIALIZABLE

 

데이터의 범위에 대한 입력작업에 관한 이야기이다. 현재 ISOL_2 테이블엔 4번과 5번 데이터가

존재하지 않는다. 여기에 우리는 얼마든지 4번과 5번 데이터를 입력할 수 있다.

그러나 누군가 이 범위를 읽고 있다면, 이 입력은 위험하다

 

<SESSION1>

-- 1~8번까지의 범위를 조회

BEGIN TRAN

SELECT * FROM ISOL_2

WHERE IID BETWEEN 1 AND 8

-- 4,5번은 현재 존재하지 않는다...

현재 보는 데이터는 4,5번을 제외한 6개의 데이터이다. 이 때 공유 잠금이 설정되어 있다.

트랜잭션 내에서의 작업이긴 하지만, 이미 SELECT 작업은 종료되었으므로 누군가 새롭게

4번데이터를 입력하려 한다면 이것은 성공한다.

 

<SESSION 2>

-- SESSION1에서 공유잠금을 건 범위내에 데이터를 입력한다.

INSERT ISOL_2(IID, INAME)

VALUES(4, 'DDDDD')

GO

SELECT * FROM ISOL_2 -- 입력되었다.

 

그런데, 문제가 있다. 현재 SESSION 1의 트랜잭션은 끝나지 않은 상태이다. 그렇다면,

현 상황이라면 아까 실행해서 본 결과는 의미없게 된다. 데이터가 부정확하게 된 것이다.

이러므로 데이터의 일관성을 보장받지 못하게 되는 결과를 낳게 된다.

그래서 이러한 데이터불일치를 막기 위해서, SERIALIZABLE 설정을 할 수 있다.

 

<SESSION1>

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

-- 1~8번까지의 범위를 조회

BEGIN TRAN

SELECT * FROM ISOL_2

WHERE IID BETWEEN 1 AND 8

-- 5 번은 현재 존재하지 않는다...

 

--ROLLBACK

 

위의 상황은 잠금 수준 설정을 하고, 다시 SELECT를 1~8번에 대한 범위 내에서 실행하고

있는 상황이다. 이 때 잠금 수준 설정을 보는 쪽에서 한다는 것에 유의하자.

SESSION 2에서의 작업을 확인한다.

 

<SESSION2>

INSERT ISOL_2(IID, INAME)

VALUES(5, 'EEEEE')

-- 더 이상 해당 범위내에는 입력하지 못한다...

 

SESSION2에서 확인한 바로는, 이제는 범위 내에 데이터를 입력하지 못한다. 이것이 잠금 수준에서의

최고 설정이다. 가장 느슨한 레벨인 READ UMCOMMITTED 이고 제일 엄격한 것이 이 SERIALIZABLE 이다.

이렇게 네 가지의 잠금 수준 설정이 있지만, 사용자가 제어할 일은 그렇게 많지 않다.

 

 

 

 

3. 잠금 고려사항

 

 

1) BLOCKING 시나리오

 

잠금에 대한 BLOCKING 문제의 대부분은 단일 프로세스가 오래도록 잠금을 유지하여 다른

모든 프로세스가 잠금 대기 상태로 되는 블로킹된 프로세스 체인을 초래하기 때문에 발생한다.

일반적인 BLOCKING 시나리오는 다음과 같다.

 

 

1] 실행 시간이 긴 쿼리 전송

실행 시간이 긴 쿼리는 다른 쿼리를 BLOCKING 할 가능성이 크다. 예를 들어, 많은 행에 영향을 주는

DELETE, UPDATE 작업에서는 테이블 전체에 대한 잠금과 상관없이, 다른 쿼리를 BLOCKING 하는

잠금을 많이 설정할 수 있다. 따라서, 일반적으로 실행 시간이 긴 OLAP 용 질의를 OLTP 와 동일한

데이터베이스나 서버에서 사용하는 것은 결코 권장하지 않는다. 물론 대용량 데이터베이스에서는

이런 일이 없을뿐더러, 중소규모 데이터베이스에서도 이런 일은 발생하지 않아야 한다.

이에 대한 해결 방법은 인덱스를 변경하거나, 크고 복잡한 쿼리를 간단한 쿼리로 분할하거나,

한가한 시간에 또는 별도의 컴퓨터에서 쿼리를 실행하여 쿼리를 최적화할 수 있는

방법을 찾는 것이 최고다.

 

쿼리가 오래도록 실행되어 BLOCKING 을 초래하게 되는 한 가지 이유는 커서를 잘못 사용하는 경우를

들 수 있다. 커서는 결과 집합을 편리하게 탐색할 수 있는 방법이지만, 커서를 사용하게 되면

레코드셋 지향의 쿼리보다 속도가 느려질 가능성이 크다.

 

 

2] COMMIT /ROLLBACK 되지 않은 쿼리를 취소하는 경우

응용 프로그램에서 쿼리를 취소하는 경우, 예를 들면 ROLLBACK과 COMMIT 문을 필요한 횟수만큼

실행하지 않고서 ODBC sqlcancel 함수를 사용하는 경우 발생할 수 있다.

쿼리를 취소하면 트랜잭션이 자동으로 롤백되거나 커밋되지 않는다. 따라서 트랜잭션 안에서 얻은

모든 잠금은 쿼리가 취소된 후에도 유지된다. 이런 상황이라면, 응용 프로그램에서는 취소된 트랜잭션을

커밋하거나 롤백함으로써, 트랜잭션 중첩 수준을 적절히 관리해야 할 것이다.

 

 

3] 모든 결과 처리를 완료하지 않는 응용 프로그램

응용프로그램에서 특정 쿼리를 DB 서버로 보낸 후, 모든 응용 프로그램에서는 모든 결과 행을 완료하기

위해 즉시 반입해야 한다. 응용 프로그램에서 모든 결과 행을 반입하지 않으면 테이블에 잠금이 남게

되어 다른 사용자를 BLOCKING 하게 될 수도 있다. Transact-SQL 문을 서버로 투명하게 전송하는

응용 프로그램을 사용하는 경우에는 응용 프로그램에서 모든 결과 행을 반입해야 한다. 그렇지 않은 경우

및 그렇게 구성할 수 없는 경우에는 BLOCKING 문제를 해결하지 못할 수도 있다.

이러한 응용 프로그램을 보고 또는 의사 결정 지원 데이터베이스로 제한하면 문제를 피할 수 있다.

 

 

4] 분산 클라이언트/서버 교착 상태

기본 교착 상태와 달리, 안타깝게도 SQL Server 2000은 분산 교착 상태를 자동으로 검색할 수 없다.

분산 클라이언트/서버 교착 상태는 응용 프로그램에서 SQL Server에 대해 여러 개의 연결을 열고

쿼리를 비동기식으로 전송하는 경우에 발생할 수 있다.

 

예를 들어, 단일 클라이언트 응용 프로그램 스레드에 두 개의 연결이 열려 있고, 비동기식으로 트랜잭션을

시작하며 첫 번째 연결에 대해 쿼리를 실행한다. 그런 다음, 응용 프로그램에서 다른 트랜잭션을 시작하고,

또 하나의 연결에 대해 쿼리를 실행하고, 결과를 기다린다. SQL Server에서 한 개의 연결에 대한 결과를

반환하면 응용 프로그램이 결과를 처리하기 시작한다. 결과를 생성하는 쿼리는 다른 연결에 대해 실행되는

쿼리에 의해 BLOCKING 되므로 응용 프로그램은 더 이상 사용 가능한 결과가 없을 때까지 결과를 처리한다.

이 시점에서 첫 번째 연결이 BLOCKING 되어 처리할 결과를 무한정 기다리게 된다. 두 번째 연결은

잠금 상태로 BLOCKING 되지는 않지만, 응용 프로그램에게 결과 반환을 시도한다.

그러나 응용 프로그램이 BLOCKING 되어 첫 번째 연결에 대한 결과를 기다리므로, 두 번째 연결에 대한

결과는 처리되지 않는다.

다음 중 하나를 사용하여 이 문제를 방지할 수 있다.

 

- 각 쿼리에 대한 쿼리 제한 시간

- 각 쿼리에 대한 잠금 제한 시간.

- 바운드 연결.

 

SQL Server는 기본적으로 클라이언트 응용 프로그램의 명령에 따라 움직인다. 서버에서 얻는 잠금에

대해서는 클라이언트 응용 프로그램이 거의 모든 제어권(및 책임)을 가진다. SQL Server 잠금 관리자는

잠금을 사용하여 자동으로 트랜잭션을 보호하지만, 이것은 클라이언트 응용 프로그램이 보낸 쿼리

유형과 결과가 처리되는 방식에 의해 직접 이루어진다. 따라서, BLOCKING 문제를 해결할 때는

대부분의 경우 클라이언트 응용 프로그램 검사가 수반된다.

 

BLOCKING 문제를 해결하기 위해서는 응용 프로그램이 전송하는 SQL 문과 연결 관리,

모든 결과 행 처리 등에 관련된 응용 프로그램의 동작이 모두 정확한지 검사해야 하는 경우가 많다.

개발 도구에서 연결 관리, 쿼리 제한 시간, 결과 처리 등에 대한 명시적인 제어가 허용되지 않으면

BLOCKING 문제는 해결되지 않을 수도 있다.

 

 

 

2) 블로킹을 방지하기 위한 응용 프로그램 디자인 지침

 

1] 실행 시간이 긴 쿼리가 생성될 수 있는 응용 프로그램은 사용하거나 디자인하지 않는 것이 좋다.

예를 들어, 응용프로그램에서 특정 필드를 공백으로 두거나, 와일드카드를 입력할 수 있게 허용하지 않고

사용자에게 입력을 요구하는 응용 프로그램은, 사용하거나 디자인하지 말자. 이렇게 하면 응용 프로그램에서

실행 시간이 너무 긴 쿼리를 전송하게 되어 BLOCKING 문제를 초래할 수 있기 때문이다.

 

2] 트랜잭션 내에서 사용자 입력을 허용하는 응용 프로그램을 사용하거나 디자인하지 않는다.

 

3] 쿼리 취소를 허용하게 한다.

 

4] 쿼리 또는 잠금 제한 시간을 사용하여 쿼리가 무한정 실행되지 않게 하며, 분산 교착 상태를 방지한다.

 

5] 모든 결과 집합을 즉시 반입하여 완료한다.

 

6] 트랜잭션을 되도록 간략하게 유지한다.

 

7] 연결 관리를 명시적으로 제어한다.

 

8] 예상되는 전체 동시 사용자 로드로 응용 프로그램의 문제 상황을 테스트한다.

 

 

 

 

 

4. DEADLOCK 방지

 

1) 교착 상태(DEADLOCK) 개요

 

교착상태 란, 둘 이상의 스레드 간에 특정 자원(테이블,행..) 대한 양방향 참조(종속) 관계가 발생한 경우를

말한다. 교착 상태는 주로 단순한 RDBMS 보다는, MULTI THREAD 가 가능한 시스템에서 발생할 가능성이

더 크다.

특정 트랜잭션의 스레드는 하나의 이상의 자원에 잠금을 얻을 수 있다. 지금까지 보았던 것처럼 잠겨진

자원을 다른 트랜잭션의 스레드가 엑세스하려 한다면, 대상 리소스가 해제될 때까지 기다려야 하는

BLOCKING 이 발생한다. 이 때 대기 중인 스레드는 리소스를 소유한 스레드에 대해 해당 리소스에 대한

종속 관계를 갖고 있다고 말한다.

 

예를 들어, 트랜잭션 1을 실행하는 스레드 T1에는 EMP 테이블에 대한 배타적 잠금이 있고, 트랜잭션 2를

실행하는 스레드 T2는 CUST 테이블에 대한 배타적 잠금이 있다.

이때 T2는 EMP에 대해 잠금을 요청할 수 있다. 그러나 T1이 현재 배타적 잠금을 가지고 있으므로

BLOCKING 이 발생한다. T2는 차단되고 T1이 완료되기를 기다릴 수밖에 없는 상황이다.

이것은 일반적인 BLOCKING 이다.

그러나, 이런 상황에서 T1이 CUST 테이블에 잠금을 요청하려 하면, 역시 T2가 잠궈 놓았으므로

BLOCKING 이 발생한다. 일반적인 BLOCKING 이라면, 잠금을 건 해당 트랜잭션이 COMMIT/ROLLBACK

전까지만 기다리면 된다. 하지만 지금은 일반적인 BLOCKING 이 아니다.

이런 때는 서로 잠금을 해제할 수 없으며, 또한 커밋하거나 롤백할 수도 없다.

이렇게 서로 잠금을 소유하고 있는 두 트랜잭션의 스레드에서, 상대방이 소유한 자원을 원하는 경우에는

교착 상태가 발생한다. 두 스레드 모두 트랜잭션을 커밋하거나 롤백할 때까지 자신이 소유한 리소스를

해제할 수 없고, 또 다른 트랜잭션이 소유한 리소스를 대기하고 있으므로 트랜잭션을 커밋하거나

롤백할 수도 없다.

 

 

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 40000

WHERE EID = 1

DBCC OPENTRAN

EXEC SP_LOCK 51

 

실행한 SP_LOCK명령에 의해, 현재 해당 키인 EID 1번에 대해 X 락이 걸려 있는 것이 확인된다.

 

<SESSION2>

BEGIN TRAN

UPDATE CUST

SET CTEL = '032-333-9999'

WHERE CID = 2

DBCC OPENTRAN

EXEC SP_LOCK 52

 

역시 키인 CID 1번에 대해 X 락이 걸려 있다.

이번엔 첫 번째 트랜잭션에서 두 번째 트랜잭션에서 배타적 잠금을 설정한 CID 1번을 수정시도 해보자.

SQL Server 가 잠금을 요청할 것이나, 현재 배타적 잠금이 벌써 두 번째 트랜잭션에 의해 잠금이

설정된 상태이므로 BLOCKING 이 발생한다. 이것은 일반적인 BLOCKING 이다.

그러므로 계속 대기상태에 접어든다.

 

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 40000

WHERE EID = 1

DBCC OPENTRAN

EXEC SP_LOCK 51

DELETE CUST

WHERE CID = 2

 

 

SELECT @@TRANCOUNT

ROLLBACK TRAN

 

현재 BLOCKING 이 설정되어 있다.

아래의 쿼리를 보면, 이번에는 두 번째 트랜잭션에서 다시 첫 번째 트랜잭션에서 배타적 잠금을 걸고

있는 자원인 EMP 테이블의 EID 값에 대해서 삭제작업을 시도해보자. 또 BLOCKING 이 발생한다.

그러나 이번에는 DEADLOCK 이 발생하게 되고, 그리고 두 트랜잭션 중 하나만 계속 진행 중인

상태로 남고 나머지 하나는 자동으로 ROLLBACK 된다.

 

<SESSION2>

BEGIN TRAN

UPDATE CUST

SET CTEL = '032-333-9999'

WHERE CID = 2

DBCC OPENTRAN

EXEC SP_LOCK 52

DELETE EMP

WHERE EID = 1

ROLLBACK

 

위에서 보여주는 바로는 두 번째 트랜잭션이 에러번호 1205번을 출력하고, 취소되었다.

그리고 첫 번째 트랜잭션은 여전히 진행 중이다.

 

<SESSION1>

BEGIN TRAN

UPDATE EMP

SET SAL = 40000

WHERE EID = 1

DBCC OPENTRAN

EXEC SP_LOCK 51

DELETE CUST

WHERE CID = 2

 

SELECT @@TRANCOUNT

ROLLBACK TRAN

 

이렇게 BLOCKING 상태가 계속 진행되지 않고 하나의 트랜잭션이 바로 취소되는 이유는, DEADLOCK

상태를 취소하지 않으면 두 트랜잭션 모두 자신의 잠금을 풀 수 없기 때문에 잠금상태가 영원히

지속되기 때문이다. DEADLOCK 상태는 트랜잭션이 스스로 잠금을 풀 수 없으므로, SQL Server 가

자동으로 둘 중 하나의 잠금을 풀고 트랜잭션을 취소시키는 것이다.

 

여러분들이 해야할 일은 이렇게 DEADLOCK 일어나 트랜잭션 중 하나가 취소된 것을 감지하고

해당 트랜잭션을 다시 실행하거나 취소하는 처리이다. DEADLOCK 감지는 @@ERROR 1204번으로 한다.

어떤 트랜잭션에서 @@ERROR 1205번 에러가 발생했다면 데드락이 발생했다는 것이고,

해당 트랜잭션을 제어해주는 로직을 여러분들이 해당 트랜잭션 내에 추가해야 하는 것이다.

 

/* 참고

교착 상태는 종종 일반적인 BLOCKING 과 혼동된다. 한 트랜잭션이 다른 트랜잭션이 원하는

리소스를 잠그고 있으면 두 번째 트랜잭션이 해당 잠금이 해제되기를 기다린다. 기본적으로,

LOCK_TIMEOUT이 설정되지 않는 한, SQL Server 트랜잭션 시간은 제한되지 않는다.

이 경우 두 번째 트랜잭션이 차단되지만 교착 상태는 아니다.

*/

 

 

4) 교착상태 분석

 

추적 플래그 1204 사용

 

이것은 명령프로프트에서 실행하는 명령어이다. SQL Server 시작 옵션 중의 하나인 /T 옵션(대문자)를

사용해서 SQL Server를 시작하게 되면 볼 수 있다.

교착 상태에서 이 추적 플래그 1204는 대기 상태의 스레드, 이 스레드가 대기하고 있는 리소스,

리소스 간의 종속 관계 주기를 나타낸다.

 

추적 플래그 1204 보고서 용어

 

추적 정보 1204는 관련된 리소스에 따라 다른 정보를 반환하지만 일반적으로 보고서에는

다음과 같은 용어가 포함된다 .

 

- Node:x

교착 상태 체인에서 항목 번호(x)를 표시한다.

 

- Lists

잠금 소유자가 다음 Grant, Convert 및 Wait 목록의 일부일 수 있다.

 

- Grant List

리소스의 현재 소유자를 열거한다.

 

- Convert List

잠금을 더 높은 수준으로 변환하려는 현재 소유자를 열거한다.

 

- Wait List

리소스에 대한 현재 새 잠금 요청을 열거한다.

 

- SPID: x ECID: x

병렬 프로세스의 경우 시스템 프로세스 ID 스레드를 확인한다. 항목 SPID x ECID 0은 주 스레드를

나타내며 항목 SPID x ECID > 0 은 같은 SPID에 대한 하위 스레드를 나타낸다.

 

- Statement Type

SELECT, INSERT, UPDATE 또는 DELETE 문 등이며, 스레드는 이에 대해 사용 권한을 갖는다.

 

-Line #

현재 명령문 배치에 있는 줄을 나열하는 데, 이 줄은 교착 상태가 발생할 때 실행된다.

 

- Input Buf

현재 배치에 있는 모든 명령문을 나열한다.

 

- Mode

스레드가 요청하고 허용하고 또는 대기하는 특정 리소스에 대한 잠금 유형을 보여준다.

모드는 IS(Intent Shared), S(Shared), U(Update), IX(Intent exclusive), SIX(Shared with intent exclusive)

및 X(Exclusive) 등이다.

 

- RID

잠금이 걸려 있거나 요구되는 테이블 안의 단일 행을 확인한다.

 

- RID는 추적 플래그 1204에서 RID: db_id:file_id:page_no:row_no로 표시된다.

예를 들면, RID: 1:1:1253:0입니다.

 

- TAB

잠금이 걸려 있거나 요구되는 테이블을 확인한다. TAB은 추적 플래그 1204에서 db_id:object_id로 표시됩니다.

예를 들면 TAB:2:2009058193 이다.

 

- KEY

잠금이 걸려 있거나 요구되는 인덱스 안의 키 범위를 확인한다. KEY는 추적 플래그 1204에서

KEY: db_id:object_id:index_id로 나타난다. 예를 들면, KEY: 2:1977058079:1 이다.

 

- PAG

잠금이 걸려 있거나 요구되는 페이지 리소스를 확인한다. PAG는 추적 플래그 1204에서

PAG: db_id:file_id:page_no로 나타난다. 예를 들면, PAG: 7:1:168 이다.

 

- EXT

익스텐트 구조를 확인한다. EXT는 추적 플래그 1204에서 EXT: db_id:file_id:extent_no로 나타난다.

예를 들면, EXT: 7:1:9이다.

 

- DB

데이터베이스 잠금을 확인한다. DB는 추적 플래그 1204에서 다음 방법 중 하나로 나타난다.

DB: db_id

DB: db_id[BULK-OP-DB], 이것은 백업 데이터베이스가 갖는 데이터베이스 잠금을 확인한다.

DB: db_id[BULK-OP-LOG], 이것은 특정 데이터베이스에 대해 백업 로그가 갖는 잠금을 확인한다.

 

- IND

인덱스 리소스에서 만들어진 인덱스가 갖는 잠금을 확인한다. IND는 추적 플래그 1204에서

다음 방법 중 하나로 나타난다.

IND: db_id:object_id:index_id

IND: db_id:object_id:index_id[INDEX_ID], 이것은 인덱스 ID가 잠겼음을 나타난다.

IND: db_id:object_id:index_id[INDEX_NAME], 이것은 인덱스 이름이 잠겼음을 나타난다.

 

- APP

응용 프로그램 리소스가 갖는 잠김을 확인한다. APP는 추적 플래그 1204에서 APP: lock_resource로

나타난다. 예를 들면, APP: Formf370f478 이다.

 

SQL Server가 응용 프로그램 리소스를 교착 상태에서 처리하지 않으면, 해당 응용 프로그램

리소스 소유자는 앞에서 설명한 오류 메시지를 받지 않는다.

대신에 이 응용 프로그램은 sp_getapplock 저장 프로시저가 해당 응용 프로그램 리소스에서 실행될 때

"-3" 반환 코드를 받환받는다.

 

- Victim Resource Owner

교착 상태 주기를 끊기 위해 SQL Server가 처리하지 않는 진행 중인 스레드를 보여준다.

선택된 스레드(SPID x ECID 0로 확인)와 기존의 모든 하위 스레드(SPID x ECID > 0로 확인)는 제거된다 .

 

- Next Branch

교착 상태 주기에 관련된 동일한 SPID에서 두 개 이상의 하위 스레드를 나타낸다.

 

교착 상태가 병렬 처리와 관련이 있으면 여러 하위 스레드가 통신 버퍼에서 차단될 수 있으며,

스레드 한 개는 다른 하위 스레드에 대해 대기 상태로 된다. 다른 모든 스레드가 교착 상태와 관련이

있는 경우에만 교착 상태 상황이다. Next Branch는 대체 경로를 추적하는 교착 상태 주기를 나타낸다.

 

 

 

/* 참고

일반적으로 SQL Server는 실행을 취소했을 때 가장 손해가 적은 트랜잭션을 실행하는 스레드를

교착 상태 희생자로 선택한다. 또는 사용자가 SET 문을 사용하여 세션의 DEADLOCK_PRIORITY를

LOW로 설정할 수 있다. DEADLOCK_PRIORITY 옵션은 교착 상태 상황에서 세션의 중요도를 판단하는

방법을 제어한다. 세션이 LOW로 설정되어 있으면 교착 상태가 발생했을 때

해당 세션이 희생자로 선택된다.

*/

 

SET DEADLOCK_PRIORITY

이 명령은 교착 상태에 있을 때 세션이 반응하는 방법을 제어한다. 교착 상태는 두 프로세스에

잠긴 데이터가 있고, 다른 프로세스가 그 잠금을 해제할 때까지 자신의 잠금을 해제하지 않을 때 발생한다.

 

 

 

3) 교착상태 해결

교착상태가 걸린 저장 프로시저나 일괄처리 내에서는 교착상태에 대한 오류처리를 할 수 없다.

해당 처리가 종료된 후 따로 처리해 주어야 한다.

 

<SESSION2>

CREATE PROC UP_LOCKTEST

AS

BEGIN TRAN

UPDATE CUST

SET CTEL = '032-333-9999'

WHERE CID = 2

--DBCC OPENTRAN

--EXEC SP_LOCK 52

DELETE EMP

WHERE EID = 1

COMMIT TRAN

GO

 

IF @@ERROR = 1205

BEGIN

EXEC UP_LOCKTEST

SELECT 'OK'

END

 

 

4) 교착상태 최소화

교착 상태를 완전히 피할 수는 없다. 그러나 교착 상태 수를 최소화할 수는 있다.

교착 상태를 최소화하면 트랜잭션 처리량이 늘어나고 트랜잭션 수가 적어지기 때문에

시스템 오버헤드가 줄어든다.

 

1] 같은 순서로 개체에 액세스하게 한다.

모든 동시 트랜잭션이 같은 순서로 개체에 액세스하면 교착 상태가 일어날 가능성이 줄어든다.

예를 들어, 두 개의 동시 트랜잭션이 EMP 테이블에 대해 잠금을 얻은 다음 CUST 테이블에 대해

잠금을 얻으면, 다른 트랜잭션이 완료될 때까지 한 트랜잭션이 EMP 테이블에서 BLOCKING 된다.

첫 번째 트랜잭션이 COMMIT /ROLLBACK 하면 두 번째 트랜잭션은 계속 진행된다.

그리고 교착 상태는 발생하지 않게 된다. 모든 데이터 수정에 대해 저장 프로시저를 사용하는 것은,

개체 액세스 순서를 표준화할 수 있는 방법이 된다.

 

2] 트랜잭션 중 사용자 상호 작용을 피한다.

사용자 간섭 없이 실행 중인 일괄 처리의 속도는, 응용 프로그램에서 요청한 매개 변수에 대한

프롬프트에 응답하는 등 사용자가 직접 쿼리에 응답하는 속도에 비해 매우 빠르므로

사용자 상호 작용이 필요하도록 트랜잭션을 작성하지 않는 것이 좋다.

예를 들어, 트랜잭션이 사용자 입력을 기다리고 있는데 사용자가 식사를 하러 가거나 퇴근한 경우

사용자는 트랜잭션을 완료할 수 없다. 트랜잭션이 소유한 잠금은 트랜잭션이

COMMIT /ROLLBACK 될 때만 해제되므로 이렇게 하면 시스템 처리량이 현저히 줄어들게 된다.

교착 상태가 발생하지 않아도 같은 리소스에 액세스하는 다른 트랜잭션이 차단되므로 트랜잭션이

완료되려면 기다려야 하기 때문이다.

 

3] 트랜잭션을 하나의 일괄 처리로 짧게 유지한다.

교착 상태는 보통 여러 개의 긴 트랜잭션이 같은 데이터베이스에서 동시에 실행될 때 발생한다.

트랜잭션 실행 시간이 길수록 배타적 또는 업데이트 잠금 시간이 길어지고 다른 작업을 차단하여

교착 상태가 발생할 가능성이 높아진다.

그러나, 트랜잭션을 하나의 일괄 처리로 유지하면 트랜잭션 중 네트워크 왕복이 최소화되므로

트랜잭션을 완료하고 잠금을 해제하는 데 걸리는 시간을 줄일 수 있다.

 

4] 낮은 TRANSACTION_ISOLATION 레벨을 사용한다.

트랜잭션을 더 낮은 격리 수준에서 실행할 수 있는지 확인해 본다. READ UNCOMMITTED 를 구현하면

첫 번째 트랜잭션이 완료될 때까지 기다리지 않고 이전에 읽은(수정하지 않은) 데이터를 읽을 수 있다.

READ UNCOMMITTED 등 낮은 잠금 수준을 사용하면 SERIALIZABLE 등의 높은 잠금 수준보다

짧은 기간 동안 공유 잠금을 보유하므로 잠금 경쟁률이 줄어든다.

 

5] 바운드 연결을 사용한다.

바운드 연결을 사용하면 같은 응용 프로그램에서 열고 있는 둘 이상의 연결을 함께 사용할 수 있다.

두 번째 연결에서 얻은 잠금은 주 연결에서 얻은 것처럼 보유되며 그 반대의 경우도 마찬가지로 처리된다.

따라서 서로를 BLOCKING 하지 않는다.-