SQL Server - 데이터베이스 교착 상태 Database Deadlock

교착 상태란?  What is a deadlock?

데이터베이스에서의 교착 상태는 두 개나 그 이상의 프로세스가 자신의 리소스를 잠가버리고 서로의 리소스에 request를 하는 것을 말한다. 한마디로 아다리가 맞지 않아 서로 일을 하지 못하는 상태이다. 각 트랜잭션은 request로 잠금이 풀리길 기다리기 때문에 더 이상 진행이 될 수 없다. 

In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock.

 

이러한 교착 상태가 일어난 경우에는 한 프로세스를 희생시켜 롤백시키고 다른 프로세스를 먼저 진행하는 수밖에 없다.

When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward.

 

 

SQL Server 가 교착 상태를 감지하는 방법

How SQL Server detects deadlocks

 

SQL Server의 잠겨있는 감시 스레드는 기본적으로 5초마다 실행이 되어 교착 상태가 발생했는지 감시한다. 만약 감시 스레드가 교착 상태를 발견한다면, 교착 상태 감시 간격이 교착 상태 발생 빈도수에 따라 5초에서 100밀리초로 줄어든다. 만약 감시 쓰레드가 교착 상태 찾는 것을 멈춘다면, 데이터베이스 엔진이 간격을 다시 5초로 늘릴 것이다.

Lock monitor thread in SQL Server, runs every 5 seconds by default to detect if there are any deadlocks. If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks. If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds

 

 

 

데드락이 발견되면 무슨 일이 일어나는가?

What happens when a deadlock is detected

 

교착 상태가 감지되면, 데이터베이스 엔진은 하나의 프로세스를 희생자로 선택하여 교착 상태를 끝낸다. 희생자로 선택된 트랜잭션은 롤백이 되고 애플리케이션에 1205 오류를 리턴한다. 그러면 다른 트랜잭션이 요청한 것들이 잠금이 풀리고 진행이 된다.

When a deadlock is detected, the Database Engine ends the deadlock by choosing one of the threads as the deadlock victim. The deadlock victim's transaction is then rolled back and returns a 1205 error to the appliction. Rolling back the transaction of the deadlock victim releases all locks held by that transaction. This allows the other transactions to become unblocked and move forward.

 

 

데드락_우선순위는 무엇인가?

What is DEADLOCK_PRIORITY

 

기본 세팅에서 SQL Server는 가장 값지지 않은 트랜잭션을 골라 교착 상태를 해결하는 희생자로 선택한다. 그러나 우리가 우선순위를 SET DEADLOCK_PRIORITY 명령문으로 정할 수 있다.

By default, SQL Server chooses a transaction as the deadlock victim that is least expensive to roll back. However, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. The session with the lowest deadlock priority is chosen as the deadlock victim.

 

DEADLOCK_PRIORITY

1. 기본값은 노멀 The default is Normal

2. LOW, NORMAL, HIGH로 설정 가능 Can be set to LOW, NORMAL, or HIGH

3. 정수로도 설정이 가능 Can also be set to a integer value in the range of -10 to 10

LOW : -5

NORMARL : 0

HIGH : 5

 

 

 

1. 만약 교착 상태의 우선순위 레벨이 다르다면, 세션이 가장 낮은 것을 골라 희생시킨다

If the DEADLOCK_PRIORITY is defferent, the session with the lowest priority is selected as the victim

 

2. 만약 두 개의 세션이 같은 우선순위 레벨을 가지고 있다면, 덜 값진 트랜잭션을 희생시키고 롤백시킨다.

If both the sessions have the same priority, the transaction that is least expensive to rollback is selected as the victim

 

3. 만약 두개의 세션이 같은 레벨이고 같은 가치를 가지고 있다면, 무작위로 선택할 것이다.

If both the sessions have the same deadlock priority and the same cost, a victim is chosen randomly

 

SQL Server에서 교착 상태 로그

Logging deadlocks in SQL Server

 

교착 상태가 발생했을 때, SQL Server는 하나의 트랜잭션을 희생자로 선택해 희생시켜 롤백을 시킨다. 교착 상태를 일으키는 쿼리를 찾고 추적하는 데에는 여러 가지 방법이 있다. 하나의 방법으로 SQL Server의 trace flag 1222를 사용하여 SQL Server 에러 로그에 기록하는 것이 있다.

When deadlocks occur, SQL server chooses one of the transactions as the deadlock victim and rolls it back. There are several ways in SQL Server to track down the queries that are causing deadlocks. One of the options is to use SQL Server trace flag 1222 to write the deadlock iformation to the SQL Server error log.

 

--추적 시작 (두번째 파라미터 없으면 세션 레벨, -1이면 글로벌 레벨)Set SQL Server trace flag 1222
DBCC Traceon (1222,-1)

--추적 상태 확인 Check the status of the trace flag
DBCC TraceStatus (1222)

--추적 끄기 Turn off the trace flag
DBCC Traceof (1222)


--에러 로그 읽기 To read the error log
Execute sp_readerrorlog

 

---

 

교착 상태 분석 및 예방

Deadlock analysis and prevention

 

에러 로그에 있는 교착 정보는 아래 표에 설명한 것 같이 세 가지의 섹션으로 이루어짐. 

The deadlock information in the error log has three sections

Section	Description
교착 희생 Deadlock Victim	교착 희생으로 양보해준 프로세스의 ID Contains the ID of the process that was selected as the deadlock victim and killed by SQL Server
프로세스 목록 Process List	교착 상태가 있었던 프로세스들의 목록 Contain the list of the processes that participated in the deadlock
리소스 목록 Resource List	교착 상태에 있던 프로세스에 관여되어진 데이터베이스 객체들 Contains the list of the resources(Database objects) owned by the processes involved in the deadlock

 

프로세스 목록 섹션

Process List Section

 

Node	Description
loginname	프로세스에 연관된 로그인 이름  The loginname associated with the process
isolationlevel	어떤 격리 수준이었는지 What isolation level is used
procname	저장된 프로시저 이름 The stoerd procedure name
Inputbuf	교착 상태가 발생되었을때 실행되었던 코드 The code the process is executing when the deadlock occured

 

---

 

교착 로그 예시

Deadlock log example

deadlock-list
 deadlock victim=process1 b48 b772108
 

process-list
   👉process id=process1 b48 b772108 taskpriority=0 logused=308 waitresource=KEY: 5:72057594043891712 (8194443284 a 0) waittime=4529 ownerId=320350 transactionname=user_transaction lasttranstarted=2020-12-10T 14:29:21.800 XDES=0x1 b497604428 lockMode=U schedulerid=4 kpid=1908 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2020-12-10T14:29:45.050 lastbatchcompleted=2020-12-10T14:29:45.047 lastattention=1900-01-01T00:00:00.047 clientapp=Microsoft SQL Server Management Studio - Query hostname=LAPTOP-SKVGRGJD hostpid=13228 loginname=LAPTOP-SKVGRGJD\Youngbin Ha isolationlevel=read committed (2) xactid=320350 currentdb=5 currentdbname=sample lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=1 stmtend=150 sqlhandle=0x020000004ef27315aaa0b80e77d872f611cc1403a829ba730000000000000000000000000000000000000000
unknown    
    inputbuf
Update TableA Set Name = Name + ' Transaction 2' where Id IN (1, 2, 3, 4, 5)    

 

 

   👉process id=process1b497fb6108 taskpriority=0 logused=964 waitresource=KEY: 5:72057594043957248 (8194443284a0) waittime=7899 ownerId=320329 transactionname=user_transaction lasttranstarted=2020-12-10T14:29:17.813 XDES=0x1b49ddd4428 lockMode=X schedulerid=2 kpid=13580 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2020-12-10T14:29:41.680 lastbatchcompleted=2020-12-10T14:29:41.677 lastattention=1900-01-01T00:00:00.677 clientapp=Microsoft SQL Server Management Studio - Query hostname=LAPTOP-SKVGRGJD hostpid=13228 loginname=LAPTOP-SKVGRGJD\Youngbin Ha isolationlevel=read committed (2) xactid=320329 currentdb=5 currentdbname=sample lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=1 stmtstart=58 stmtend=162 sqlhandle=0x02000000afc9fb00bf36152bcc5a8f96b61be868c5074a1e0000000000000000000000000000000000000000
unknown    
     frame procname=adhoc line=1 stmtend=120 sqlhandle=0x02000000b0c78c1a8559b462d71345691d9f6af434f016350000000000000000000000000000000000000000
unknown    
    inputbuf
Update TableB Set Name = Name + ' Transaction 1' where Id = 1    
  resource-list
   keylock hobtid=72057594043891712 dbid=5 objectname=sample.dbo.TableA indexname=PK__TableA__3214EC07E1ABF345 id=lock1b48e78c100 mode=X associatedObjectId=72057594043891712
    owner-list
     owner id=process1b497fb6108 mode=X
    waiter-list
     waiter id=process1b48b772108 mode=U requestType=wait
   keylock hobtid=72057594043957248 dbid=5 objectname=sample.dbo.TableB indexname=PK__TableB__3214EC073D946EFD id=lock1b48e793400 mode=X associatedObjectId=72057594043957248
    owner-list
     owner id=process1b48b772108 mode=X
    waiter-list
     waiter id=process1b497fb6108 mode=X requestType=wait 

 

---

SQL 프로파일러를 이용하여 교착 상태 캡처하기

Capturing deadlocks in sql profiler

 

1. 파란색 X가 있는 원은 SQL 서버에서 교착 상태 희생자로 선택한 트랜잭션을 뜻함. 
The oval on the graph, with the blue corss, represents the transaction that was chosen as the deadlock victim by SQL server.

2. 파란색 십자가가 없는 원은 성공적으로 완료된 트랜잭션을 나타냄. 
The oval on the graph without blue cross represents the transaction that completed successfully.

3. 마우스를 타원 위로 올리면 교착 상태를 일으킨 SQL 코드를 볼 수 있음.
When you move the mouse pointer over the oval, you can see the SQL code that was running that caused the deadlock.

4. 타원형 기호는 프로세스 노드를 나타냄. 
The oval symbols represent the process nodes

- Server Process Id : 만약 SQL Server Management Studio를 사용하는 경우 하단 정보 표시 줄에서 Server Process Id를 확인할 수 있음.
If you are using SQL Server Management Studio you can see the server process id on information bar at the bottom.
 - Deadlock Priority : SET DEADLOCK PRIORITY를 사용하여 DEADLOCK PRIORITY를 명시적으로 설정하지 않은 경우, 두 프로세스 모두 동일한 기본 우선순위 normal(0)을 가진다.
If you have not set DEADLOCK PRIORITY explicitly using SET DEADLOCK PRIORITY statement, then both the processes should have the same default deadlock priority NORMARL(0)

 

5. 직사각형은 리소스 노드임.
The rectangles represent the resource nodes.

6. 화살표는 각 프로세스가 각 리소스 노드에 가지고 있는 잠금 유형을 나타냄.
The arrows represent types of locks each process has on each resource node.

---

SQL Server 데드락/교착 상태 에러 관리

SQL Server deadlock error handling

에러는 지난번과 같이 Try... Catch문법으로 잡는다.

예제)

Alter procedure spTransaction1
as  
Begin  
    Begin Tran
 Begin Try  
  Update TableA Set Name = 'Mark Transaction 1' where Id = 1  
  Waitfor delay '00:00:05'  
  Update TableB Set Name = 'Mary Transaction 1' where Id = 1  
  -- If both the update statements succeeded. 
  -- No Deadlock occurred. So commit the transaction.
  Commit Transaction
  Select 'Transaction Successful' 
 End Try
 Begin Catch
  -- Check if the error is deadlock error
  If(ERROR_NUMBER() = 1205)
  Begin
   Select 'Deadlock. Transaction failed. Please retry'
  End
  -- Rollback the transaction
  Rollback
 End Catch 
End

Alter procedure spTransaction2  
as  
Begin  
    Begin Tran
 Begin Try
  Update TableB Set Name = 'Mary Transaction 2' where Id = 1
  Waitfor delay '00:00:05'
  Update TableA Set Name = 'Mark Transaction 2' where Id = 1
  Commit Transaction
  Select 'Transaction Successful' 
 End Try
 Begin Catch
  If(ERROR_NUMBER() = 1205)
  Begin
   Select 'Deadlock. Transaction failed. Please retry'
  End
  Rollback
 End Catch 
End

 

 

 

---

트랜잭션을 막는 트랜잭션을 찾기

How to find blocking queries in sql server

DBCC OpenTran

DBCC OpenTran 명령은 가장 오래된 트랜잭션을 보여준다. 이 뜻은 모든 트랜잭션을 보여주는 것이 아니다.

DBCC OpenTran will display only the oldest active transaction. It is not going to show you all the open transactions

SQL Server process can be killed using

1. SQL Server Activity Monitor

2. Using SQL command KILL Process_ID

 

세션을 강제 종료하면 생기는 일

What happends when you kill a session

모든 작업이 끝이나고 롤백이 된다. 그리고 데이터베이스는 반드시 트랜잭션이 시작하기 전으로 돌아가야 한다.

All the work that the transaction has done will be rolled back. The database must be put back in the state it was in, before the transaction started

 

---

 

교착 상태의 필요조건 When occurs a deadlock

다음의 네 가지의 조건이 모두 충족되어야 교착상태가 발생한다. There is four conditions of deadlock.

1. 상호 배제 : 최소한 하나는 비공유 방식으로 점유되어야 한다.
                   비공유 방식의 점유란 한 번에 하나의 프로세스만 자원을 사용할 수 있음을 말한다.

2. 점유와 대기 : 프로세스는 최소한 하나의 자원을 점유하고 있고, 다른 프로세스가 점유하고 있는 다른 프로세스를 
                       기다리고 있어야 한다.

3. 비선점 : 점유된 자원은 강제로 해제될 수 없고, 프로세스가 자원의 사용을 끝마치고 자발적으로 해제할 때 까지는
                그 자원을 얻을 수 없어야 한다.

4. 순환 대기 : T0, T1, T2 순으로 프로세스의 집합이 존재하여야 한다.

                    T1은 T0의 자원을 기다리고 있어야 하고, T2는 T1의 자원 해제를 기다리고 있어야 한다.