September 27, 2016 at 9:23 am
All, could someone help me how to avoid the deadlock on a "queued" DELETE operation? It seems SQL server has some issue. We avoided by doing a RETRY with a considerable amount of time, about 0.4 seconds after realizing .35 seconds for a delete operation.
Any help is very much appreciated.
Thanks,
Pingala
September 27, 2016 at 9:38 am
Are you deleting one row or a range or rows?
Also, is the selectivity of the delete operation covered by an index?
For example, if you DELETE FROM TABLEA WHERE NAME = 'SMITH', and the NAME column is not indexed, then SQL Server will potentially hold an update lock on every page in the table, rather than just the page containing the individual row to be deleted.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 6:19 am
I am not sure how many rows are in a particular "queued" delete operation. Does indexing help?
September 28, 2016 at 6:29 am
Pingala (9/28/2016)
I am not sure how many rows are in a particular "queued" delete operation. Does indexing help?
Indexing might help. Post deadlock info here.
Might be the reason that you are trying to delete a range and there can be some other DML holding lock on resources, just my guess.
September 28, 2016 at 7:11 am
Pingala (9/28/2016)
I am not sure how many rows are in a particular "queued" delete operation. Does indexing help?
Like I mentioned above, an index on the DELETE's WHERE clause predicate is not only helpful but essential. You want the DELETE operation to quickly navigate to the specific page containing row for deletion, perform the delete, and then release it's locks. Without an index, the DELETE operation must scan every page in the table looking for that single rows to delete, so it will not only take londer to run, but it also hold it's locks for a longer period of time. When locks held by multiple processes collide, it causes blocking and potentially deadlocking, so you want each DELETE to be as quck as possible.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 7:30 am
As the DELETEs are queued to SQL server, I thought it will take care of on executing synchronously
September 28, 2016 at 7:49 am
Pingala (9/28/2016)
As the DELETEs are queued to SQL server, I thought it will take care of on executing synchronously
With the default read committed isolation level, readers can block writers and writers can block reader. So, if another process like a report or application is querying this table, then it can block or delete operations. The best thing to do at this point is determine which specific SPIDs are involved in the deadlock and see what's blocking your deletes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 9:15 am
This is a code snippet I have used where I currently work to handle deadlocks:
-- The following snippet is for building deadlock handling.
declare @ErrorNumber int,
@ErrorLine int,
@ErrorMessage nvarchar(4000),
@ErrorProcedure nvarchar(128),
@ErrorSeverity int,
@ErrorState int;
while 1 = 1
begin
begin transaction;
begin try;
-- <-- code goes here -->
commit;
break;
end try
begin catch
rollback;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
continue;
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
end
end catch
end
September 28, 2016 at 9:42 am
Thanks. We implemented the RETRY to avoid deadlock. However, I am surprised how a queued DELETE got a deadlock in the first place if SQL Server does queued operations sequentially.
September 28, 2016 at 10:02 am
Pingala (9/28/2016)
Thanks. We implemented the RETRY to avoid deadlock. However, I am surprised how a queued DELETE got a deadlock in the first place if SQL Server does queued operations sequentially.
I am not even sure what you mean by a queued delete.
September 29, 2016 at 7:32 am
Pingala (9/28/2016)
Thanks. We implemented the RETRY to avoid deadlock. However, I am surprised how a queued DELETE got a deadlock in the first place if SQL Server does queued operations sequentially.
Also, without looking at the deadlock graph it is difficult to know what caused the deadlock.
September 29, 2016 at 7:42 am
Lynn Pettis (9/28/2016)
Pingala (9/28/2016)
Thanks. We implemented the RETRY to avoid deadlock. However, I am surprised how a queued DELETE got a deadlock in the first place if SQL Server does queued operations sequentially.I am not even sure what you mean by a queued delete.
I'm assuming there is a mechanism by which requests for deletes are queued asynchonously, and then a process executes them sequentially to prevent parallel delete operations. If there are deletes, then I assume there must also be inserts so maybe that's when the blocking occurs.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 29, 2016 at 7:42 am
Everything you need to know is here:
Deadlocking
https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx
Detecting and Ending Deadlocks
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
Handling Deadlocks
https://technet.microsoft.com/en-us/library/ms177453(v=sql.105).aspx
Minimizing Deadlocks
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 29, 2016 at 11:33 am
Eric M Russell (9/29/2016)
Lynn Pettis (9/28/2016)
Pingala (9/28/2016)
Thanks. We implemented the RETRY to avoid deadlock. However, I am surprised how a queued DELETE got a deadlock in the first place if SQL Server does queued operations sequentially.I am not even sure what you mean by a queued delete.
I'm assuming there is a mechanism by which requests for deletes are queued asynchonously, and then a process executes them sequentially to prevent parallel delete operations. If there are deletes, then I assume there must also be inserts so maybe that's when the blocking occurs.
The only mechanism I know of is Service Broker and depending on that is configured you could still run deletes in parallel.
Wait, you could use a table and a scheduled SQL Server Agent job to handle it as well.
September 30, 2016 at 5:22 am
Thank you very much! It seems the indexing of the table resolved the issue. However, we also added RETRY as an extra measure.
Thanks,
Pingala
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply