SQL server 2005 DeadLock

Question

Hi,

What this deadlock graph indicates and how to resolve this issue?

Answer 1

check this link for more info

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/41a510cc-0e54-4bfb-92d2-12996abf99f6

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

This deadlock graph shows that SPID 370 & 593 is causing deadlock. But you need not to do any thing rights now, SQL Server himself analysis & stop the SPID 370 as culprit & let SPID 593 continue to complete its task.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

Hi Rohit,

You are right.

But here the problem is, From past 2 years we didn't faced these kind of deadlocks and blocking.but from last 2 months we are getting frequently.

One this i am not getting that why these deadlock and blocking occurring from past 2 months. this SQL code was written around 3 years back.We have nothing changed.

Can you look into below 2 graphs and analyze and let me know how to resolve this.

Answer 4

Hello,

As your thread is not related to SQL Data Access , i am moving it to the Database Engine forum where this kind of problem is usually treated.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

The underlying data in the tables would have changed/grown, to make the update/insert/delete/select statements take more time than previous which can cause the transactions to be bigger and in effect result in a deadlock.

Are you doing proper index maintenances?

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 6

Hi Vijya

1. share the table growth in last some time.
2. Last date time of statistics updation
3. Fregmantation level of indexes
4. share the queries causing blocking

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 7

Deadlocks are not something you "fix", they are something to be avoided by proper coding practices in the application.  This is not a DBA or database issue, it is an application logic issue.

Please see:

http://msdn.microsoft.com/en-us/library/aa892828(v=ax.10).aspx

Answer 8

If this is the case (changed/grown) what can we do?

Answer 9

Do you have the deadlock graph from the sql server error log?

Make sure that you have a proper reindexing strategy and you do update the stats frequently. This should be the first step for any kind of performance issues with SQL Server.

Once that is done, enable this trace flag 1222 by running this command DBCC traceon (1222,-1). Once the deadlocks that you need to capture is captured in the error log. stop the trace and post the deadlock graph here. 

If you are interested in understanding and resolving the deadlocks on your own please read this blog. This is THE THE best article to resolve deadlocks.

http://blogs.msdn.com/b/bartd/archive/2006/09/08/deadlock-troubleshooting_2c00_-part-1.aspx

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 10

Hello Vijay,

It is very hard to look at the picture. If you give a clearer pic to comment on then it will be appreciated.

If you can try following basics then it should give you good head-way:

1. Queries involved in the deadlock, run them through Database Tuning Advisor.
2. Look at the isolation level that is been used by these transaction. Use minimal isolation level which could be as low as read uncommitted.
3. Keep transaction are small to avoid blocking time.
4. Improve query efficency - changing the query or look for index improvement

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

Answer 11

Hi anurag,

Here i am sending errorlog for the same.

2013-01-15 23:02:36.91 spid10s     Node:1

2013-01-15 23:02:36.91 spid10s     PAGE: 5:1:10742871             CleanCnt:2 Mode:U Flags: 0x2
2013-01-15 23:02:36.91 spid10s      Grant List 3:
2013-01-15 23:02:36.91 spid10s        Owner:0x0C89A560 Mode: U        Flg:0x0 Ref:0 Life:00000001 SPID:343 ECID:0 XactLockInfo: 0x5D6DEDE4
2013-01-15 23:02:36.91 spid10s        SPID: 343 ECID: 0 Statement Type: UPDATE Line #: 27
2013-01-15 23:02:36.91 spid10s        Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 2107922631]
2013-01-15 23:02:36.91 spid10s      Requested By:
2013-01-15 23:02:36.91 spid10s        ResType:LockOwner Stype:'OR'Xdes:0x249F8E18 Mode: U SPID:68 BatchID:0 ECID:2 TaskProxy:(0x75127BE8) Value:0x28e9a860 Cost:(0/0)
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:2

2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:3

2013-01-15 23:02:36.91 spid10s     PAGE: 5:1:10745359             CleanCnt:3 Mode:U Flags: 0x2
2013-01-15 23:02:36.91 spid10s      Wait List:
2013-01-15 23:02:36.91 spid10s        Owner:0x1D818A40 Mode: U        Flg:0x2 Ref:1 Life:00000001 SPID:68 ECID:5 XactLockInfo: 0x539A1E34
2013-01-15 23:02:36.91 spid10s        SPID: 68 ECID: 5 Statement Type: UPDATE Line #: 27
2013-01-15 23:02:36.91 spid10s        Input Buf: No Event:
2013-01-15 23:02:36.91 spid10s      Requested By:
2013-01-15 23:02:36.91 spid10s        ResType:LockOwner Stype:'OR'Xdes:0x0B505E18 Mode: U SPID:343 BatchID:0 ECID:2 TaskProxy:(0x578F5B90) Value:0x42cbe660 Cost:(0/0)
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:4

2013-01-15 23:02:36.91 spid10s     PAGE: 5:1:10745359             CleanCnt:3 Mode:U Flags: 0x2
2013-01-15 23:02:36.91 spid10s      Grant List 3:
2013-01-15 23:02:36.91 spid10s        Owner:0x2CEA1A40 Mode: U        Flg:0x0 Ref:0 Life:00000001 SPID:343 ECID:0 XactLockInfo: 0x249F8CC4
2013-01-15 23:02:36.91 spid10s      Requested By:
2013-01-15 23:02:36.91 spid10s        ResType:LockOwner Stype:'OR'Xdes:0x539A1E10 Mode: U SPID:68 BatchID:0 ECID:5 TaskProxy:(0x75127E08) Value:0x1d818a40 Cost:(0/0)
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     -- next branch --
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:2

2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:6

2013-01-15 23:02:36.91 spid10s     PAGE: 5:1:10743783             CleanCnt:3 Mode:U Flags: 0x2
2013-01-15 23:02:36.91 spid10s      Wait List:
2013-01-15 23:02:36.91 spid10s        Owner:0x3DC6DE00 Mode: U        Flg:0x2 Ref:1 Life:00000001 SPID:68 ECID:7 XactLockInfo: 0x2CE51264
2013-01-15 23:02:36.91 spid10s        SPID: 68 ECID: 7 Statement Type: UPDATE Line #: 27
2013-01-15 23:02:36.91 spid10s        Input Buf: No Event:
2013-01-15 23:02:36.91 spid10s      Requested By:
2013-01-15 23:02:36.91 spid10s        ResType:LockOwner Stype:'OR'Xdes:0x26A0FE10 Mode: U SPID:343 BatchID:0 ECID:4 TaskProxy:(0x578F5C90) Value:0xb8b0ae0 Cost:(0/0)
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Node:7

2013-01-15 23:02:36.91 spid10s     PAGE: 5:1:10743783             CleanCnt:3 Mode:U Flags: 0x2
2013-01-15 23:02:36.91 spid10s      Grant List 3:
2013-01-15 23:02:36.91 spid10s        Owner:0x1D80D940 Mode: U        Flg:0x0 Ref:0 Life:00000001 SPID:343 ECID:0 XactLockInfo: 0x282F3E7C
2013-01-15 23:02:36.91 spid10s      Requested By:
2013-01-15 23:02:36.91 spid10s        ResType:LockOwner Stype:'OR'Xdes:0x2CE51240 Mode: U SPID:68 BatchID:0 ECID:7 TaskProxy:(0x75127E68) Value:0x3dc6de00 Cost:(0/0)
2013-01-15 23:02:36.91 spid10s     
2013-01-15 23:02:36.91 spid10s     Victim Resource Owner:
2013-01-15 23:02:36.91 spid10s      ResType:LockOwner Stype:'OR'Xdes:0x539A1E10 Mode: U SPID:68 BatchID:0 ECID:5 TaskProxy:(0x75127E08) Value:0x1d818a40 Cost:(0/0)

Answer 12

Can you tell what is this object  [Database Id = 5 Object Id = 2107922631] ?? This belongs to spid 343. Its line #27 which is the update which is being deadlocked.

There are mainly two spids 343 and 68 both doing updates.

Spid 68 is the victim.

Not really able to find what is spid 68?

Is it sql 2000?? if its sql 2005 or above can you use trace flag 1222 so you get a better graph.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 13

Now i am getting Id = 2107922631 as blank (using sys.objects).Now that dead lock is not happening.

We are using SQL 2005.

Answer 14

Did you check in the database id 5? It should not be blank i guess.

Can you put in trace 1222 and post that deadlock graph please. the deadlock graph you posted is for sql 2000. 2005 deadlock graph has more information in it.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com