April 20, 2005 at 3:03 pm
We have an application in .NET that reads from SQL Server SPROC to build an XML. The SPROC XML calls executes another SPROC, which updates and inserts CHECKSUM values to a table to keep track of values sent so that in subsequent execution the same data is not sent again.
The SPROC that updates data in the tracking table seems to be getting into deadlock problem. The same application will acquire a lock on the same table from three different SPROCs, though updates/inserts might be happening from different tables. The tracking table has checksum values for three different types of object and a given object's checksum values are distinguished by its object ID on which (each OBJECT ID) there is a separate index (neither unique nor clustered, though).
I have never had to face deadlock issues so posting here to see if I can get some help into improving my SPROC so that deadlocks don't occur.
Error: Transaction (Process ID 75) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In my SPROC that does update/inserts I have at the beginning:
SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
At the end:
COMMIT TRAN
GO
Any suggestions on what to do to avoid this deadlock issue? Any help is appreciated as I am new to understanding locking mechanism.
Thanks much.
JN
April 20, 2005 at 3:28 pm
First understand what a deadlock is ---somebody post a link here or just go back to books online
Try not to do a lot of different stuff in the same transaction. Try to stage as much information as possible before inserting rows.
(some like to insert a row then go update it to add values to it instead of --for example-- build the row in a temp table and then when it's all set to go, insert it in one statement near the very end of your transaction --the final commit)
Only after you've addressed all the fundamental design aspects, then build in a "retry 3 times" to your db connection handler on the client side that resends the SQL if the deadlock error is detected.
April 20, 2005 at 3:48 pm
I did read about deadlocks so have some general knowledge on that. Just not sure how to resolve related issues.
In my sproc there is only one INSERT statement at the beginning of the SPROC to insert the ObjectID if not already found in the tracking table. Then several UPDATE statements to update CHECKSUM values for the ObjectID that was either just inserted in using the first INSERT statement in the same SPROC or it already existed in the tracking table from previous SPROC execution. There are three SPROC for three different Objects that does this INSERT/UPDATE to the same table and they do this in the same order (columns).
The XML application is not controlled by me so how it's written or should be written is not within my power. I can, however, request the developer to make changes to avoid deadlocks, if necessary.
Hope that clarifies the scenario I have.
Any other suggestions?
Thanks.
JN
April 20, 2005 at 4:16 pm
Yes.
Don't do that.
Don't insert then update. Insert only.
April 20, 2005 at 4:23 pm
I am not sure how I can do Insert only. I need to insert IDs only if they are not already found in my table, but if they are found in the table then just update that ID's checksum values. Is it better if I have one SPROC that does the ID Insert only and another that does checksum value update only for the IDs found in my table?
Thanks.
JN
April 20, 2005 at 6:38 pm
if not exists (...) begin
begin tran
get your totals
insert the row
commit tran
end else begin
begin tran
update the row that existed...
commit tran
end
Or be fancy:
Update where id = id
if rowcount = 0 then it must not be there so begin
insert new row
end
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply