Deadlock issue in SQL Server
Just recently , I have come across a deadlock issue of a stored procedure getting deadlocked with itself.
This SP is being called through a COM+ transaction.
When I looked into the report it seems that there is a range of keys of "IX_ItemInstance_InstrumentInstanceID_ItemTemplateID" index of "ItemInstance" table are locked and result in deadlock.
The indexes on ItemInstance table are as follows
1) IX_ItemInstance_InstrumentInstanceID_InstrumentPageID clustered located on PRIMARY InstrumentInstanceID, InstrumentPageID
2) IX_ItemInstance_InstrumentInstanceID_ItemTemplateID nonclustered located on PRIMARY InstrumentInstanceID,
3) PK__ItemInstance__2CF2ADDF nonclustered, unique, primary key located on PRIMARY ID
I tried for following things to resolve this issue.
1) Provided read uncommitted locking hint for all the tables data are selected.
2) Provided transaction isolation level in the SP at the beginning of the SP to set this level for the connection calling this SP.
3) Dropped the index reported for a key lock as it was kind of useless index.(But then the Key lock propagated to Clustered Index)
Could you suggest me what else can be tried out, Currently I am thinking of two options
1) changing clustered index to some other column.
2) Splitting insert to insert row by row by implementing a cursor or a loop.
However it would really be a great help if you can suggest me some work around to avoid this deadlock issue.
Because many customers are unable to save their responses because of these deadlocks.
Please find attached herewith are the traceflag 1204 report , Stored procedure DDL and DDL of the table in question.
Thanks in advance
You might need to do the transaction inside the stored procedure, and add the records to all relevent linked tables (linked via the foreign key constraints) within the transaction.
Thanks for you reply brouse !!
This does not really seems to be feasible as there are aprox 300 + sps are being called through com+ transaction, So it is really painfull for me to think in this way.
As depending on the business logic all the relevant linked tables can't get populated at this point of time.
A deadlock is when two or more connections perform activities that would block each other. A single SP will never deadlock itself in one call, but I' have come across cases where COM+ components accidentally work with several connections at once. If these call the same SP, it might appear to block itself, since SQL Server is quite unaware of that the different connections are involved in a single COM+ transaction.
The answer is often to make sure it's a single COM+ component that calls all the SP-s with a single connection object. For best performance, it's good practise to not let COM+ manage the transaction at all since COM+ transactions are distributed, with the associated overhead.
It's better execute BEGIN TRANSACTION before all the SP calls, and the COMMIT/ROLLBACK TRANSACTION after the calls, depending on outcome.
If you hit a brick wall, you didn't jump high enough!
Thanks for your reply Rune,
But if you see the 1204 trace flag report attached with my original e-mail, it states that both the participants of the deadlock are the same.
The reason it shows is that because of the serializability of the com+ component it locks a range of keys, to protect phantom etc. and results in a deadlock (Not always). (It usually happens when concurrent users for this SP exceeds 120 - 130.
Your idea is good but is a bit impractical in my case.
I am also thinking of degrading transaction isolation level of the com + component from serializable to read committed.
Any views ?
I have the same problem you mentioned here, "but I' have come across cases where COM+ components accidentally work with several connections at once".
How can i avoid deadlock in that scenario. I have only one com+ object which calls only one storedproc. Deadlock occurs when there is heany load like more than 500 or 1000 calls to the com+ object from another process which in turn calls the sp.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL