December 5, 2012 at 9:38 am
Hi,
I have a SQL 2000 Agent job that executes a stored procedure which populates a table from a file.
The code in the SP is not within a transaction and the EXEC of the proc is also not in a transaction. It is just a SQL Agent job.
It happens every now and again that this job fails with an error message indicating that it was a deadlock victim.
My question is:
Will the queries in the proc roll back when this deadlock occurs?
OR
Will it import the file up to a the point of deadlock and then just stop - meaning an incomplete import?
December 5, 2012 at 10:35 am
Hello Casper,
According to my knowledge of deadlocks,
Deadlocking occurs when two or more user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has locked. When this happens, SQL Server resolves the deadlock by automatically aborting one process, the "victim" process, allowing the other processes to continue.
The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.
So, theoritically, in your case, the table should not be populated at all.
Please refer to the below articles on handling deadlocks:
http://sqlserverpedia.com/wiki/Handling_Deadlock_Conditions_with_TRY_/_CATCH
http://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
Let me know if it solves your query...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply