Deadlock assitance required
-
Hi,
I am hoping someone wiser than me can assist me in determining what is causing so many deadlocks on my server.
I am not sure what steps to take after checking out the log to help stop the deadlocks occuring.
From the Error log:
2007-02-13 10:04:22.64 spid4 ----------------------------------
2007-02-13 10:04:22.64 spid4 Starting deadlock search 52105
2007-02-13 10:04:22.64 spid4 Target Resource Owner:
2007-02-13 10:04:22.64 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce58602007-02-13 10:04:22.64 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce58602007-02-13 10:04:22.64 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:141 ECID:0 Ec
0xAEB1B518) Value:0x1cb590e02007-02-13 10:04:22.64 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce58602007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4 Deadlock cycle was encountered .... verifying cycle
2007-02-13 10:04:22.64 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce5860 Cost0/1FA0)2007-02-13 10:04:22.64 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:141 ECID:0 Ec
0xAEB1B518) Value:0x1cb590e0 Cost0/0)2007-02-13 10:04:22.64 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce5860 Cost0/1FA0)2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4
Deadlock encountered .... Printing deadlock information
2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4 Wait-for graph
2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4 Node:1
2007-02-13 10:04:22.64 spid4 TAB: 7:20911146 [] CleanCnt:2 Mode: S Flags: 0x0
2007-02-13 10:04:22.64 spid4 Grant List 1::
2007-02-13 10:04:22.64 spid4 Owner:0x5fc24fc0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:141 ECID:0
2007-02-13 10:04:22.64 spid4 SPID: 141 ECID: 0 Statement Type: SELECT Line #: 1
2007-02-13 10:04:22.64 spid4 Input Buf: Language Event: SELECT
SB_PATIENT_REGISTER.SB_PATIENT_MRN,
SB_PATIENT_REGISTER.SB_HL7_SOURCE,
SB_PATIENT_REGISTER.SB_PATIENT_SURNAME,
SB_PATIENT_REGISTER.SB_PATIENT_OTHER_NAMES,
SB_PATIENT_EPISODE.SB_HOSPITAL_CODE,
SB_INVOICE.SB_EPISODE_NUMBER,
SB_I
2007-02-13 10:04:22.64 spid4 Requested By:
2007-02-13 10:04:22.64 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:101 ECID:0 Ec
0xB0471518) Value:0x66ce5860 Cost0/1FA0)2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4 Node:2
2007-02-13 10:04:22.64 spid4 PAG: 7:1:99305 CleanCnt:2 Mode: IX Flags: 0x2
2007-02-13 10:04:22.64 spid4 Grant List 0::
2007-02-13 10:04:22.64 spid4 Owner:0x69328760 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:101 ECID:0
2007-02-13 10:04:22.64 spid4 SPID: 101 ECID: 0 Statement Type: INSERT Line #: 1
2007-02-13 10:04:22.64 spid4 Input Buf: Language Event: INSERT INTO SB_INVOICE_ITEM with (rowlock)
(
SB_INVOICE_ID,
SB_SERVICE_DATE,
SB_ITEM_QTY,
SB_INVOICE_COST,
SB_EQUIP_NO,
SB_ITEM_STATUS_CODE,
SB_COLLECTION_POINT,
SB_REQUIREMENT_CODE,
SB_PROVIDER_NUMBER,
2007-02-13 10:04:22.64 spid4 Requested By:
2007-02-13 10:04:22.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:141 ECID:0 Ec
0xAEB1B518) Value:0x1cb590e0 Cost0/0)2007-02-13 10:04:22.64 spid4 Victim Resource Owner:
2007-02-13 10:04:22.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:141 ECID:0 Ec
0xAEB1B518) Value:0x1cb590e0 Cost0/0)2007-02-13 10:04:22.64 spid4
2007-02-13 10:04:22.64 spid4 End deadlock search 52105 ... a deadlock was found.
-
Not sure if you have done the initial analysis of the log output as yet, but here is a start:
SPID
LOCK TYPE
OBJECT
STMT TYPE
NODE 1
Grant List
141
S
TAB 7:20911146
SELECT
Requested By
101
IX
NODE 2
Grant List
101
IX
PAG 7:1:99305
INSERT
Requested By
141
S
Refer to this link (http://support.microsoft.com/kb/832524) and look at the ‘Lock Compatibility Chart’, which says that an Intent Exclusive (IX) lock is NOT compatible with a Shared (S) lock – hence your deadlock situation.
I have two recommendations for you:
1. I see that there is a ROWLOCK hint for the INSERT statement – this hint should only be used for UPDATE and DELETE statements;
2. Read up on the NOLOCK table hint, and use it if this is allowed for your particular business.
Further reading: http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
-
Hi,
I'd already added the WITH (NOLOCK) statements to the query #1 after posting, and intend to remove the statement on the INSERT.
It makes sense a little more sense not that you explain how to match up the stuff from the error log to the compatibility chart.
Awesome!
Is there a guide to know explain what sort of query may create a particular sort of lock?
Thanks,
-
Keep in mind Scott that there is more to a deadlock than simple lock compatibility. It is very important to understand lock compatibility within SQL Server, but there's more to the picture here than the shared lock and the intent exclusive lock not being compatible. SQL Server used the lock compatibility to ensure that all transactions meet the ACID requirement. In any multi-user system, SQL Server will be handling lock incompatibilities all day long without there being a deadlock.
Lock incompatibilities are normal, natural, designed behavior and are not the root cause of a deadlock. The deadlock itself happens when Session1 has resources locked that Session2 is waiting to grab and Session2 has resources locked that Session1 is waiting to grab. Since they both have resources locked that the other is waiting for, a deadlock scenario occurs. Notice that this is different than simple lock incompatibility. Consider this example:
Deadlock: Session1 has resource A locked and is attempting to get a lock on resource B. Session2 has resource B locked and is attempting to get a lock on resource A. They are both waiting for each other to release their locks before they can process their work. This is a deadlock.
Lock incompatibility: Session1 has resource A locked and no other resources locked. Session2 is attempting to get a lock on resource A. Session1 will process it's work and release its lock. Meanwhile, Session2 is waiting until Session1 releases its lock. Once resource A is available, Session2 will acquire its lock and process its work.
Make sense? On top of the resources that Paul has referenced, here's another good one for understanding locking and transactions: http://www.databasejournal.com/features/mssql/article.php/3524891
-
May I ask why Node 1 is attempting to acquire an exclusive table lock? It may be worth going beyond using NOLOCK as a quick fix.
Scott is correct in that the ROWLOCK hint seems out of place. Could the TAB lock be related to lock escalation? How many rows are you trying to insert?
SQL guy and Houston Magician
-
Hi,
Node 1 is running what I think is quite a straight foward query and I do not know why it may be using an exclusive table lock. Why might it do so?
One of the tables in the query is a view, which while not complex, has the potential to be a pain in the proverbial.
Actually, it uses the table that I have the most problems with. As an overview, the table holds for each day of a patient's stay in a hospital the Financial classification. This table is used for deciding on the billing costs across a patient's stay. In a billing system such as what I work on it is accessed quite a lot.
The view:
SELECT
EPISODE.SB_EPISODE_NUMBER,
EPISODE_FIN_CLASS.SB_FIN_DATE,
FINANCIAL_CLASS.SB_FIN_CLASS_CODE,
FINANCIAL_CLASS.SB_FIN_CLASS_DESC,
FINANCIAL_CLASS.SB_FIN_CLASS_TYPE_CODE
FROM SB_PATIENT_EPISODE EPISODE INNER JOIN
SB_EPISODE_FIN_CLASS EPISODE_FIN_CLASS ON
EPISODE.SB_EPISODE_NUMBER=EPISODE_FIN_CLASS.SB_EPISODE_NUMBER AND
EPISODE_FIN_CLASS.SB_EPISODE_FIN_ID = (
SELECT TOP 1 SB_EPISODE_FIN_ID
FROM SB_EPISODE_FIN_CLASS
WHERE SB_EPISODE_NUMBER = EPISODE.SB_EPISODE_NUMBER
ORDER BY SB_FIN_DATE DESC
 
INNER JOINSB_FINANCIAL_CLASSIFICATION FINANCIAL_CLASS ON
EPISODE_FIN_CLASS.SB_FIN_CLASS_CODE=FINANCIAL_CLASS.SB_FIN_CLASS_CODE
Happy to receive any suggestions, and I will continue to look into locking.
-
Guess I'll have to ask the obvious question... is either (or both) the SELECT and/or the INSERT wrapped in a BEGIN TRANSACTION/COMMIT pair?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Actually, the insert is within a transaction that I see now is far too large. ( I didn't write it). I have reduced the number of queries within it, and time will tell if that pair of locks will disappear.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply