Preventing deadlocks in SQL Server

Question

I have an application connected to a SQL Server 2014 database that combines several rows into one. There are no other connections to this database while the application is running.

First, select a chunk of rows within a specific time span. This query uses a non-clustered seek (TIME column) merged with a clustered lookup.

select ...
from FOO
where TIME >= @from and TIME < @to and ...

Then, we process these rows in c# and write changes as a single update and multiple deletes, this happens many times per chunk. These also use non-clustered index seeks.

begin tran

update FOO set ...
where NON_CLUSTERED_ID = @id

delete FOO where NON_CLUSTERED_ID in (@id1, @id2, @id3, ...)

commit

I am getting deadlocks when running this with multiple parallel chunks. I tried using ROWLOCK for the update and delete but that caused even more deadlocks than before for some reason, even though there are no overlaps between chunks.

Then I tried TABLOCKX, HOLDLOCK on the update, but that means I can't perform my select in parallel so I'm losing the advantages of parallelism.

Any idea how I can avoid deadlocks but still process multiple parallel chunks?

Would it be safe to use NOLOCK on my select in this case, given there is no row overlap between chunks? Then TABLOCKX, HOLDLOCK would only block the update and delete, correct?

Or should I just accept that deadlocks will happen and retry the query in my application?

UPDATE (additional information): All deadlocks so far have happened in the update and delete phase, none in the select. I'll try to get some deadlock logs up if I can't get this solved today (the correct trace flags weren't enabled before).

UPDATE: These are the two arrangements of deadlocks that occur with ROWLOCK, they both refer only to the delete statement and the non-clustered index it uses. I'm not sure if these are the same as the deadlocks that occur without any table hints as I wasn't able to reproduce any of those.

Ask if there's anything else needed from the .xdl, I'm a bit weary of attaching the whole thing.

Thrasher

Have you tried claiming an UPDLOCK during your select? That way the lock is already there when you're updating/deleting which should keep you out of deadlocking. If possible share some of the deadlock logging details with us. – Mambo

Can you move all of the processing into a stored procedure? You might also solve this by simply turning on Snapshot Isolation but it really depends on what you're doing. – Werewolf

@Mambo Unfortunately I can't get deadlock logs right now. It seems all threads that failed due to deadlocks were in the update and delete phase, so changing the select lock is unlikely to affect this case. I don't have any deadlock logs unfortunately, I'll see if I can get the deadlock trace flags enabled for the next attempt. @Nick.McDermaid Can't use snapshot isolation unfortunately. – Thrasher

is the SELECT the deadlock victim? Here is an article I found useful sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/… - you do have a UPDATE/SELECT, DELETE/SELECT scenario - how often does it deadlock? If the error is on the SELECT, have you considered trapping it, waiting 10ms and then retrying? – Pugilism

"multiple parallel chunks" >> Is this a single process that produces several tasks to do this from different threads, hoping to speed up the process? You will not gain any benefits from this way of working. Best to do this from one "script" or "procedure" and let SQL Server do parallelization if it deems it appropriate. – Result

@Pugilism no the update/delete is always the vicim (so far). – Thrasher

@Result Single process with multiple threads, each with its own connection. I'm doing things which aren't feasible in sql, and running CLR functions in Sql Server isn't possible in this case. I am getting a multiple fold performance increase from running multiple threads with the current implementation. – Thrasher

@JussiKosunen In my question I gave a link on how to analyze deadlocks from SQL Server Profiler. See what you can learn about the deadlock issue you are experiencing from there. – Result

You should make the "algorithm" if we can call it that, crystal clear in your question. What happens on what thread, what is the flow of control and sequence of events... It's a bit unclear from what you wrote. What is the result of the SELECT? One ID; or many IDs? If you answer many IDs, what do you do: a transaction per ID or a transaction for all IDs. Is the result of the SELECT one chunk as you call it, or do you have many selects each selecting a chunk. Is the SELECT part of the transaction(s)? Why update only one ID and delete several other IDs in that one transaction? Etc. – Result

Answer 1

The general advice regarding deadlocks: make sure you do everything in the same order, i.e. acquire locks in the same order, for different processes.

You can find the same advice in this technical article on microsoft.com regarding Minimizing Deadlocks. There's a good reason it is listed first.

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
• Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
• Use snapshot isolation.
• Use bound connections.

---

Update after question from Cato:

How would acquiring locks in the same order apply here? Have you got any advice on how he would change his SQL to do that?

Deadlocks are always the same, no matter what environment: two processes (say A & B) acquire multiple locks (say X & Y) in a different order so that A is waiting for Y and B is waiting for X while A is holding X and B is holding Y.

It applies here because DELETE and UPDATE statements implicitely acquire locks on the rows or index range or table (depending on what the engine deems appropriate).

You should analyze your process and see if there are scenarios where locks could be acquired in a different order. If that doesn't reveal anything, you can analyze deadlocks using the SQL Server Profiler:

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files.

Result

how would acquiring locks in the same order apply here? Have you got any advice on how he would change his SQL to do that? – Pugilism

Added deadlock graphs to the question, they both seem to only refer to the delete statement – Thrasher

Answer 2

I'd use sp_getapplock in the updating transaction to prevent multiple instances of this code running in parallel. This will not block the selecting statement as table locking hints do.

You still should program the retrying logic, because it may take a while to acquire the lock, longer than the timeout parameter.

This is how the updating transaction can be wrapped into sp_getapplock.

BEGIN TRANSACTION;
BEGIN TRY

    DECLARE @VarLockResult int;
    EXEC @VarLockResult = sp_getapplock
        @Resource = 'some_unique_name_app_lock',
        @LockMode = 'Exclusive',
        @LockOwner = 'Transaction',
        @LockTimeout = 60000,
        @DbPrincipal = 'public';

    IF @VarLockResult >= 0
    BEGIN
        -- Acquired the lock
        update FOO set ...
        where NON_CLUSTERED_ID = @id

        delete FOO where NON_CLUSTERED_ID in (@id1, @id2, @id3, ...)

    END ELSE BEGIN
        -- return some error code, so that the caller could retry
    END;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- handle the error
END CATCH;

The selecting statement doesn't need any changes.

I would recommend against NOLOCK, even though you say that IDs in chunks do not overlap. With this hint the SELECT query can skip some pages that are being changed, it can read some pages twice. It is unlikely that such behavior can be tolerated.

Devisal

Looks like sp_getapplock would be slower than my current implementation and I'd be better off just retrying all deadlocks. Thanks for the info on NOLOCK. – Thrasher

While this is certainly the big hammer approach, I can imagine certain circumstances or architectures where this is the only feasible approach rather than redesigning or refactoring a program. Though in general I would recommend trying to find the root cause to fix any deadlock situations. – Result

Answer 3

Kindly use get applock in such format in code.  The stored procedure sp_getapplock puts the lock on the application resource .

EXEC Sp_getapplock
     @Resource = 'storedprocedurename',
     @LockMode = 'Exclusive',
     @LockOwner = 'Transaction',
     @LockTimeout = 25000

It is very helpful. Kindly increase LockTimeout to reduce deadlock

Pudgy