SQL: How to debug SQL deadlocks

What is a deadlock?

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, Andrew holds a lock on table A and requests a lock on table B; Lindsay holds a lock on table B and requests a lock on table A.

The lock manager(Lock Monitor)Æs thread checks for deadlocks. When a lock managerÆs deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.

Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Killing the SPID frees the resources and allows the other SPID to continue. In a well-designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction. Although deadlocks can be minimized, they cannot be completely avoided. That is why the front-end application should be designed to handle deadlocks.

So what can cause deadlocks?

1. Locks
2. Worker threads
3. Memory
4. Parallel query execution-related resources
5. Multiple Active Result Sets (MARS) resources.

How to debug deadlocks

Turn on deadlock trace

          DBCC TRACEON(1204,1222)

This will enable deadlock tracing for all existing connetions and new. You can check out KB832524 for more details. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

Use SQL Profiler to trace deadlock events and get the resource ID of the table or index under contention. The steps to do this are:

1. Start SQL profiler
2. On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3. Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
4. Click the Data columns tab, add DatabaseID, IndexID, ObjectID

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention. To translate the database id and object id into names (although DatabaseName and ObjectName are selectable columns, the data is not always presented in the trace), you can do

           SELECT database_name(DatabaseID)

           SELECT object_name(ObjectID)

Use SQL Profiler to trace batch statements or RPC statements, to trace which statement causes the deadlock. The restriction of this is that if the repro is far between and you have a busy system, the trace file will grow too large to be useful.

You can also run following query to check which process is being blocked.

            SELECT * FROM sys.sysprocesses WHERE blocked <> 0

            Get the SPID from blocked column

            DBCC inputbuffer (SPID)

            sp_who2

            sp_lock2

You can also use SP_LOCK2 to receive detailed locking view.

개인과 기업 그리고 사회에 스며들어 있는 습관의 형태와 그 힘에 대하여 이야기하기 위해,

저자 찰스 두히그는 700여편의 학술 논문을 파헤치고,

300여명이 넘는 과학자와 경영자들을 인터뷰 하였다고 한다.

신호 – 반복행동 – 보상 이라는 연결 고리로 모든 습관을 규정 지을 수 있다고 하는데,

예를 들면 알코올 중독자 들은 일상의 괴로움(신호)으로 술을 찾아 마시게 되며(반복행동)

그로 인한 외로움을 달래는(보상) 형태가 습관의 연결 고리이다.

수많은 연구 사례로 습관을 고치는 간단하고도 명확한 답은 반복행동을 바꾸는 것인데,

알코올 중독자를 예로 들자면,

술을 마시는 반복 행동대신 외로움을 달래줄 수 있는 무엇인가 (사교모임)를 통해

반복 행동을 바꾸어 주면 같은 신호와 같은 보상을 유지하며

쉽게 습관을 바꿀 수 있다는 것이다.

하지만 단순하게 생각하여 반복 행동만을 바꿀 수 있는 것은 아니고,

습관 고리를 순환시키는 핵심인 열망 즉 반복행동을 통해서 얻게 되는

확실한 보상에 대한 열망에 대하여 정확히 파악하여야 반복 행동을 바꿀 수 있다고 한다.

책에는 수많은 흥미로운 연구 사례가 소개되고 있으며,

기업이 일반 인들의 습관을 이용한 마케팅 기법과, 습관 자체를 바꿔 버리는 기법 등등은

우리가 알아채지도 못하는 사이에 습관을 통해 자연스럽게 지배 당하고 있다는 생각이 들게 한다.

책에서 소개 되었던 사례 중, P&G의 페브리즈는

단순히 액체를 분사해 주기만 하면 어떠한 냄새든 없애줄 수 있는 획기적인 발명이었지만

초기에 최악의 실패작이라는 고전을 면치 못하였는데

이는 사람들의 습관고리를 잘못 파악(냄세 – 페브리즈 – 탈취)하여 마케팅 했던 결과였으며,

습관고리를 다른 방향으로(청소후 – 페브리즈 – 마무리) 잡고 마케팅 한 결과

엄청난 판매고를 올릴 수 있었다는 이야기가 있다.

사람들은 냄새에 익숙해질 수 있지만, 청소 후 청소로 인한 보상 (깨끗해 졌다는 느낌)에

페브리즈를 더하여 청소를 완벽하게 마무리 했다는 느낌을 주게 함으로서

페브리즈를 청소 후 꼭 사용해야 하는 제품으로 만들었다는 이야기다.

그래서 처음에는 무색 무취의 제품이었지만

지금은 다양한 향기를 품고 있는 제품들이 출시 되고 있는 것이다.

이 외에도 모든 미국인들에게 양치하는 습관을 만들어 준 펩소던트 치약 광고,

매출과 상관 없을 것 같은 안전 수칙 준수(습관)를 사내 개혁으로 삼아

매출을 2배로 성장시킨 알코아, 부모와 친구도 몰랐던 그녀의 임신 사실을 알고 있는

대형 마트 등등 흥미로운 사례들이 많이 있다.

잘 몰랐던 습관 형성의 원리와,

잘못된 습관을 의미 있고 올바른 습관으로 바꾸기 위한 노력을 하고 계신 분들,

그 외에도 수많은 연구사례들에 관심이 있는 분이라면 한번쯤 읽어 보시길…