반응형
DeadLock이란 둘 이상의 세션이 서로 맞물려 차단된 상태를 말한다. DeadLock이 발생하면 영원히 지속되기 때문에 SQL 서버가 자동으로 찾아내어 해제시켜 준다. DeadLock에는 두가지 종류가 있다.
1. 순환 교착 (Cycle Deadlock)
교착상태를 설명할 때 보통 예로 드는 것이 이 "Cycle DeadLock"이다. 두 세션이 필요한 리소스를 얻기 위해 서로 상대방이 Lock을 풀기를 기다리는 상태라고 설명할 수 있다.
예를 들면 다음과 같다.
- -- 세션 1
- BEGIN TRAN
- UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10
- WAITFOR DELAY '0:0:5'
- UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10
-- 세션 1
BEGIN TRAN
UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10
WAITFOR DELAY '0:0:5'
UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10
- -- 세션 2 : 세션 1과 연달아서 실행한다.
- BEGIN TRAN
- UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10
- WAITFOR DELAY '0:0:5'
- UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10
-- 세션 2 : 세션 1과 연달아서 실행한다.
BEGIN TRAN
UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10
WAITFOR DELAY '0:0:5'
UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10
위와 같이 실행시키면 한쪽 세션에서는 다음과 같은 결과를 볼 수 있다.
(1개 행 적용됨)
서버: 메시지 1205, 수준 13, 상태 50, 줄 1
트랜잭션(프로세스 ID 54)이 lock 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.
교착상태란 무엇인가를 직관적으로 알려주는 예이다.
2. 변환 교착 (Conversion DeadLock)
Conversion DeadLock 은 잠금모드가 SharedLock에서 UPD-Lock 혹은 X-Lock으로 전환될 때 발생하는 문제로서, 채번(일련번호 매기는 일)과 관련해서 발생하는 경우가 많다.
1) 세션 A가 트랜젝션을 건 후 어떤 Row에 공유잠금(S-Lock)을 걸었다고 가정해보자
2) 세션 B도 트랜젝션을 건 후 그 Row에 공유잠금을 걸었다. 공유잠금끼리는 서로 호환되니까 당근 가능하다.
3) 이 상태에서 세션 A는 그 Row에 Update를 시도한다. 이 Row에는 세션 B에서 공유잠금을 걸었으므로 세션 A는 배타적잠금을 을 걸기 위해 세션 B가 공유잠금을 풀어주기를 기다린다.
4) 이때, 세션 B도 그 Row에 Update를 시도한다
과연 어떻게 될까? A는 B의 공유잠금때문에 Update를 진행하지 못하고, B는 A의 공유잠금때문에 Update를 진행하지 못하게 된다. 이것이 Conversion DeadLock이다.
실제 테스트를 해보면 다음과 같다.
- -- 이 SQL을 세션 1, 2에서 연달아 실행한다.
- DECLARE @NUM INT
- BEGIN TRAN
- SELECT @NUM = VAL + 1 FROM TAB1
- WITH (REPEATABLEREAD)
- WHERE NUMTYPE = 'TestApp'
- WAITFOR DELAY '0:0:5'
- UPDATE TAB1 SET VAL = @NUM WHERE NUMTYPE = 'TestApp'
-- 이 SQL을 세션 1, 2에서 연달아 실행한다.
DECLARE @NUM INT
BEGIN TRAN
SELECT @NUM = VAL + 1 FROM TAB1
WITH (REPEATABLEREAD)
WHERE NUMTYPE = 'TestApp'
WAITFOR DELAY '0:0:5'
UPDATE TAB1 SET VAL = @NUM WHERE NUMTYPE = 'TestApp'
테스트해보면 데드락이 발생하는 것을 확인할 수 있다.
만약 위의 SELECT문에서 WITH (REPEATABLEREAD) 잠금힌트가 없다면 어떻게 될까?
머릿속으로 생각할 때는 데드락이 발생하는 것이 불가능할 것 같다.
만약 DB가 엄청나게 바쁜 상황이라면?? 잘 모르겠다. ^^;
위와 같은 SQL이 데드락이 발생하지 않도록 하려면 어떻게 해야 할까?
위의 교착상태는 SELECT시 공유잠금이 동시에 걸렸기 때문에 발생한 것이므로,
SELECT시에 다음과 같이 잠금 힌트를 주어 처음부터 명시적으로 UPDLOCK을 걸어주면
교착상태를 방지할 수 있다.
SELECT @NUM = VAL + 1 FROM TAB1 WITH (UPDLOCK) WHERE NUMTYPE = 'TestApp'
내가 전에 링크해왔던 글 중에
UPDATE Character SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
위와 같은 SQL이 Conversion DeadLock이 발생하기 쉬우므로
UPDATE Character WITH (UPDLOCK) SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
이렇게 써야 한다는 글이 있었는데, 이것은 잘못된 내용이었다.
UPDATE문 실행시 WHERE 필터가 진행될 때는 해당 Row에 Update Lock이 걸리며,
실제로 WRITE가 진행될 때 Exclusive Lock으로 전환된다.
위의 업데이트문에서는 Shared Lock이 걸리지 않으며 따라서 위의 잠금 힌트는 주나마나 한 것이 된다.
3. Transaction을 걸지 않았는데도 DeadLock이 발생하는 경우
가끔은... 아예 BEGIN TRAN이란 문장 자체가 없는데도 데드락이 발생하는 경우가 있다. 이것은 모든 INSERT, UPDATE, DELETE 문장이 실행될 때 명시적으로 TRANSACTION이 지정되지 않을 경우 자동으로 "암시적 트랜잭션"이 생성되기 때문이다.
즉,
- UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10
UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10이라는 문장은 실제로는
- BEGIN TRAN
- UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10
- COMMIT
BEGIN TRAN
UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10
COMMIT과 같은 식으로 실행되는 것이다.
그렇다면 만약 한 UPDATE 문장 내에서 Shard Lock과 Update 혹은 Exclusive Lock이 함께 걸리는 SQL이 있을까?
물론 있다.
- -- Select와 Update가 한 SQL 내에 섞여 있어 Conversion Deadlock에 취약한 SQL
- UPDATE TEST_TAB
- SET VAL = 1
- WHERE PK IN (SELECT PK FROM TEST WHERE VAL = 10)
-- Select와 Update가 한 SQL 내에 섞여 있어 Conversion Deadlock에 취약한 SQL
UPDATE TEST_TAB
SET VAL = 1
WHERE PK IN (SELECT PK FROM TEST WHERE VAL = 10)
위와 같은 문장은 SELECT가 실행될 때 S-Lock이 걸린 후 X-Lock으로 전환된다.
실제로 세션1 에서 먼저 TEST_TAB 에 적당한 UPDATE문을 실행하여 테이블 전체에 X-Lock을 건 후
세션2 에서 위의 Update 문을 실행하고 SP_LOCK으로 세션 2의 잠금상태를 점검해보면 다음과 같이 S-Lock을 먼저 시도하고 있는 것을 확인할 수 있다.
따라서 DB가 바쁠때 위와 같은 문장이 동시다발적으로 실행되면 DeadLock이 발생하게 된다.
(실제로 WHILE 루프로 묶어서 여러 세션에서 돌려보면 테스트 환경에서도 교착상태가 재현된다.)
위와 같이 UPDATE 문 내에 명시적으로 SELECT 서브쿼리가 들어있지 않으면서도 SharedLock을 유발하는 UPDATE 문장들이 있다.
- UPDATE UPDATE_TAB
- SET UPDATE_TAB.MANAGE = 1
- FROM UPDATE_TAB, UPDATE_TAB A
- WHERE A.SEQ = UPDATE_TAB.SEQ AND A.MANAGE = 1
UPDATE UPDATE_TAB
SET UPDATE_TAB.MANAGE = 1
FROM UPDATE_TAB, UPDATE_TAB A
WHERE A.SEQ = UPDATE_TAB.SEQ AND A.MANAGE = 1
위와 같은 JOIN UPDATE문 역시 데드락에 취약하다.
교착상태를 방지하기 위해 가급적이면 한 SQL 내에 UPDATE문장과 SELECT 문장을 섞어 쓰는 것을 피해야 한다.
5. DeadLock을 줄이는 5가지 방법
DeadLock을 줄이기 위해서는 다음과 같은 방법들이 있다.
1) 인덱스를 설정한다. 인덱스가 없으면 Lock이 걸리는 범위가 훨씬 넓어지기 때문에 교착상태가 발생하기 쉬워진다.
2) 자원들을 한쪽으로 사용한다. A와 B라는 테이블이 있다면 모든 세션에서 A->B 순서로 사용하도록 한다.
3) 트랜잭션은 가급적이면 짧게 만든다.
4) 테이블의 크기를 작게 쪼갠다. (정규화)
5) Transaction Isolation Level을 "Read Uncommitted"로 설정한다.
반응형
'연구개발 > SQL2005' 카테고리의 다른 글
| [SQL 2005 GUIDE] 큰 값 데이터 형식 (0) | 2009.07.06 |
|---|---|
| 트랜잭션 로그 백업(Transaction Log Backup)에 관하여 (0) | 2009.06.28 |
| 잠금에 관한 고찰(2) - 격리 수준(Transaction Isolation Level)에 대하여 (0) | 2009.06.28 |
| 잠금에 관한 고찰(1) - 잠금(Lock) 매커니즘에 대하여 (0) | 2009.06.28 |
| tempdb 데이터베이스에 대한 동시성 강화 (0) | 2009.06.28 |
