Dead Lock
-
Can any show some light to avoid Dead Lock.below error message i was getting from Error logs
Deadlock encountered .... Printing deadlock information
2005-05-29 12:40:56.43 spid3
2005-05-29 12:40:56.43 spid3 Wait-for graph
2005-05-29 12:40:56.43 spid3
2005-05-29 12:40:56.43 spid3 Node:1
2005-05-29 12:40:56.43 spid3 KEY: 7:1938158000:1 (620131e87622) CleanCnt:1 Mode: X Flags: 0x0
2005-05-29 12:40:56.43 spid3 Grant List 0::
2005-05-29 12:40:56.43 spid3 Owner:0x7a633da0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:137 ECID:0
2005-05-29 12:40:56.43 spid3 SPID: 137 ECID: 0 Statement Type: SELECT Line #: 12
2005-05-29 12:40:56.43 spid3 Input Buf: RPC Event: sp_cursor;1
2005-05-29 12:40:56.43 spid3 Requested By:
2005-05-29 12:40:56.43 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:76 ECID:0 Ec
0x6DF3F528) Value:0x7a633de0 Cost0/1AC4)2005-05-29 12:40:56.43 spid3
2005-05-29 12:40:56.43 spid3 Node:2
2005-05-29 12:40:56.43 spid3 KEY: 7:1938158000:1 (67012b41e573) CleanCnt:1 Mode: X Flags: 0x0
2005-05-29 12:40:56.43 spid3 Grant List 0::
2005-05-29 12:40:56.43 spid3 Owner:0x7a632300 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:76 ECID:0
2005-05-29 12:40:56.43 spid3 SPID: 76 ECID: 0 Statement Type: SELECT Line #: 12
2005-05-29 12:40:56.43 spid3 Input Buf: RPC Event: sp_cursor;1
2005-05-29 12:40:56.43 spid3 Requested By:
2005-05-29 12:40:56.43 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:137 ECID:0 Ec
0x1C7EF528) Value:0x7a633aa0 Cost0/1890)2005-05-29 12:40:56.43 spid3 Victim Resource Owner:
2005-05-29 12:40:56.43 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:137 ECID:0 Ec
0x1C7EF528) Value:0x7a633aa0 Cost0/1890) -
This information is not sufficient to help you out.But from the spid given, you can find out the Pid(process Id)from sysprocess(select hostprocess from master..sysprocesses where spid=137). Using the pid you can find out the exact process causing dead loack from the task manager.Then analyse it further.
Or use profiler to find the sql query and tune the sql query further.dbcc inputbuffer(spid) also will give you the sql query.
-
Dear John
Thanks ! But did you think this dead lock is happening because of bad indexes .
If i run dbcc inputbuffer(spid) i get the below results
EventType Parameters EventInfo
--------- ---------- -----------
RPC Event 0 sp_cursor;1
What is that mean
-
A deadlock can happen when two processes does the same thing, but in reverse order.
Say you have procA that within a transactions wants to modify tableA and then tableB. At the same time procB, also within a transaction wants the same thing, only that procB starts with tableB and then wants tableA.
ProcA grabs tableA and does it's thing at the same time as procB grabs tableB. Now, procA waits for tableB to be released, while procB waits for tableA to be released. You now have a deadlock.
Are you doing anything that resembles this scenario? If you are, then the solution to avoiding deadlocks is to design all accesspatterns the same way. Always do stuff in the same order.
Hope it helps some?
/Kenneth
-
Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead. Dead locks can be redused in following ways.
1.Access Objects in the Same Order
If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions obtain a lock on the Supplier table, and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues. A deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.
2.Avoid User Interaction in Transactions
Avoid writing transactions that include user interaction because the speed of batches running without user intervention is much faster than the speed at which a user can manually respond to queries, such as replying to a prompt for a parameter requested by an application. For example, if a transaction is waiting for user input, and the user goes to lunch, or even home for the weekend, the user holds up the transaction from completing. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked, waiting for the transaction to complete.
3.Keep Transactions Short and in One Batch
A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.
Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.
4.Use a Low Isolation Level
Determine whether a transaction can run at a lower isolation level. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level such as serializable, thereby reducing locking contention.
5.Use Bound Connections
Using bound connections, two or more connections opened by the same application can cooperate. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa, and therefore do not block each other.
Regards
Binu John
-
Please do not confuse deadlocking behaviour with 'normal' locking behaviour - aka 'blocking'.
Deadlocks occur when two processes ends up waiting for each other's resources already held by the other part. This is a no-win situation for either, since this waiting state will be held for eternity. SQL Server is smart enough to detect such situations and mercifully kills one in order for the other to keep going. Deadlocks is a process-flowdesign issue.
'Normal' locks - aka 'blocking' - is expected behaviour. Processes simply stands in line waiting for their turn until the wanted resource is released. This is not a deadlock. Extended blocking that may be considered taking too long may cause concurrency issues instead. Blocking is a concurrency-design issue.
The important thing is that the two are not the same, deadlocking and blocking are vastly different behaviours, and I just want to point that out in order to avoid confusion
/Kenneth
-
Thanks Ken,
But hope that I have made the statement against deadlocks and not of normal blocks.
Rgds
Binu John
-
Well, I wasn't aiming at you in particular, John, though I think that you too in your post may have used 'blocking' and 'deadlocking' somewhat interchangeably. ( 1) describes how to avoid deadlocks, while 2) describes how to avoid blocking )
Though many times it may just be a play with words, I believe that many times also people in general doesn't realize the difference between them, thus it can be confusing when you try to explain a situation of either case.
Again, wasn't ment at any particular, just wanted to raise the awareness about the differences between the two.
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply