'blocking'에 해당되는 글 1건

  1. 2013.12.13 Deadlock (1)
MS SQL2013.12.13 14:01

 

[내용]

   

Deadlock에 대략 적인 내용을 확인하고, 테스트를 통해서 Deadlock을 구현해보록 하겠습니다.

   

 

[환경]

   

OS: Windows Server 2008 R2

Product: SQL Server 2008 R2

   

   

[정의]

   

Deadlock이란?

트랜잭션1과 트랜잭션2가 처리 되고 있는 상황에서 트랜잭션1은 트랜잭션2가 원하는 데이터를 잠그고 있으면서 트랜잭션2가 잠그고 있는 데이터를 원하고, 트랜잭션2는 트랜잭션1이 원하는 데이터를 잠그고 있으면서 트랜잭션 1이 잠그고 있는 데이터를 원하는 상황에 발생하는 형태를 데드락 이라고 하며, 교착 상태라고도 부릅니다.

   

이때, SQL Server는 한쪽 트랜잭션 처리를 강제로 종료시키면서 무한 루프의 상황을 제거할 수 있습니다.

   

 

[TEST]

   

세션60에서 먼저 아래의 쿼리를 실행합니다.

BEGIN TRANSACTION

UPDATE dbo.EMPLOYEE

SET EmpName = N'홍길퉁'

WHERE EmpID = 'S0001'

WAITFOR DELAY '00:00:30'

SELECT EmpName, EMail

FROM dbo.EMPLOYEE

WHERE EmpID = 'S0002'

ROLLBACK TRAN

GO

   

그리고 세션61번에서 아래의 쿼리를 실행합니다.

BEGIN TRANSACTION

UPDATE dbo.EMPLOYEE

SET EmpName = N'일주매'

WHERE EmpID = 'S0002'

WAITFOR DELAY '00:00:30'

SELECT EmpName, EMail

FROM dbo.EMPLOYEE

WHERE EmpID = 'S0001'

ROLLBACK TRAN

GO

   

이러한 경우, 아래와 같은 오류 메시지가 발생하게 됩니다.

   

 

[교착 상태 감지 방법]

1. sp_readerrorlog

새 쿼리에서 sp_readerrorlog를 실행하여 확인이 가능합니다.

   

2. Profiler

아래와 같은 이벤트로 데드락을 감지할 수 있습니다.

   

   

   

[방지 및 해결 방안]

   

1. SET DEADLOCK_PRIORITY

필요하다면 SET DEADLOCK_PRIORITY 문을 사용해 교착 상태 발생 시 트랜잭션의 중요도로 희생 쿼리를 선택할 수 있다.

위 상황에서 세션61번이 중요하다고 가정하고 TEST를 진행해보겠습니다.

   

A. 방법

각 트랜잭션 쿼리 안에 아래의 내용을 기입합니다.

SET DEADLOCK_PRIORITY HIGH -- 세션 61

SET DEADLOCK_PRIORITY LOW -- 세션 60

   

B. 결과

   

2. 테이블 힌트

WITH(NOLOCK) 혹은 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED를 사용합니다.

   

A. 방법

각 세션에 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED를 입력하고 진행합니다.

   

B. 결과

정상적으로 쿼리가 실행 됩니다.

   

 

[참고]

http://technet.microsoft.com/en-us/library/ms172453.aspx

참고 서적: SQL Server 운영과 튜닝, 이장래와 함께하는 SQL Server 2012

   

   

 

   

신고

'MS SQL' 카테고리의 다른 글

주의 대상 데이터베이스 복구 방법  (0) 2014.02.11
Backup, Restore로 데이터베이스 복사 방법  (0) 2014.02.04
Deadlock  (1) 2013.12.13
UDL 파일로 SQL Server 연결 테스트  (0) 2013.12.03
Column Level 암호화  (0) 2013.11.20
[Test] SQL Server mdf file 파티션  (1) 2013.11.11
Posted by TM ~ing