Forum Discussion

sebd's avatar
sebd
Copper Contributor
Jan 22, 2025
Solved

Deadlocks on High Frequency Updates

Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exceed max_workers_count, 576 in my case, I get multiple errors of the form:


('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 448) was deadlocked on lock | thread resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)')

I wasn't able to reproduce the error with less requests than max_workers_count.

 

The UPDATE request is the following:

UPDATE dbo.UsersAnswer SET UsersSelectionType = ? WHERE For_Question = ? AND For_Quiz = ? AND FK_Answer = ?;

Note that, I've tried with and without (UPDLOCK, ROWLOCK) and (UPDLOCK), but it doesn't change the outcome. Also, the updates are done for the same primary key. 

Finally, the UsersAnswer table is created as follows:

CREATE TABLE [dbo].[UsersAnswer](
    [For_Question] [smallint] NOT NULL,
    [For_Quiz] [uniqueidentifier] NOT NULL,
    [FK_Answer] [int] NOT NULL,
    [UsersSelectionType] [tinyint] NOT NULL,
 CONSTRAINT [PK_UsersAnswer] PRIMARY KEY CLUSTERED 
(
    [For_Question] ASC,
    [For_Quiz] ASC,
    [FK_Answer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UsersAnswer]  WITH CHECK ADD  CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] FOREIGN KEY([FK_Answer])
REFERENCES [dbo].[Answer] ([PK_Answer])
GO

ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer]
GO

ALTER TABLE [dbo].[UsersAnswer]  WITH CHECK ADD  CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] FOREIGN KEY([For_Question], [For_Quiz])
REFERENCES [dbo].[QQ] ([FK_Question], [FK_Quiz])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz]
GO

 

Do you have any idea on what could cause the deadlock? The deadlock graph is huge, you can find it https://drive.google.com/file/d/1cs_-QULtF0yBsqOIzab56l9oYxKypbUV/view?usp=sharing.

 

Thanks for your insights on this.

  • Right off the bat: do you have a nonclustered index on the columns in the UPDATE statement's WHERE clause?

    CREATE INDEX UsersAnswer_ncix ON UsersAnswer(For_Question,For_Quiz,FK_Answer);

    If not, create it and monitor for changes in deadlock occurrence.

    P.S. I'd recommend against turning the table into a heap by turning the PK into a NC index.

8 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    Rerun the transaction. 

    I don't understand what here the problem is? Deadlocks can occur in several different situation.

    Application should be aware of and should do, what the error message already says: Rerun the transaction. 

    • sebd's avatar
      sebd
      Copper Contributor

      I was mainly worried that I misconfigured the database in terms of concurrency, or that the index was missing some parameter. This error slows down my platform, but I guess if the only solution is to rerun the transaction, than I need to upgrade the CPU of the server and maybe rerun the transactions with exponential backoff.

  • VladDBA's avatar
    VladDBA
    Brass Contributor

    Right off the bat: do you have a nonclustered index on the columns in the UPDATE statement's WHERE clause?

    CREATE INDEX UsersAnswer_ncix ON UsersAnswer(For_Question,For_Quiz,FK_Answer);

    If not, create it and monitor for changes in deadlock occurrence.

    P.S. I'd recommend against turning the table into a heap by turning the PK into a NC index.

    • sebd's avatar
      sebd
      Copper Contributor

      Thanks for your answer. I have a primary key on the columns of the WHERE clause, so it's a clustered index. I've also tried having a primary key with a non clustered index but it didn't solve the bug.

      I didn't measure (yet) if there is a significant difference in the number of deadlocks created between the clustered and non clustered primary key though. But as it varies a lot between each test, so it might be difficult to evaluate this precisely.

      • VladDBA's avatar
        VladDBA
        Brass Contributor

        Sorry. I missed the PK definition containing all the columns in the WHERE clause.

        Are all the concurrent UPDATE threads touching the same record or handful of records?

  • HolgerRachut's avatar
    HolgerRachut
    Copper Contributor

    I'm curious. What happens if you set the primary key to nonclustered?
     CONSTRAINT [PK_UsersAnswer] PRIMARY KEY NONCLUSTERED

    • sebd's avatar
      sebd
      Copper Contributor

      Thanks for your reply! Unfortunately, it doesn't solve the problem.

      I also tried on a different table that has a clustered primary key not composite (single column) and it gave the same error. Also note that I don't reach 100% of my CPU while stress testing (so the database is not even under extreme stress). I really don't get the problem here... I'll try on another machine just to check.

      • HolgerRachut's avatar
        HolgerRachut
        Copper Contributor

        How do your database properties look like? 
        Have you tried to set "read commited snapshot on" to true? (but read the documentation about that)

        Just for another try: If your fill-factor is set to 100 or 0, maybe reduce to 80. (think about regular rebuilding then)

        "cost treshold for parallism" changed in the sql server setting? I recommend a value 50 and not the default of 5.

        If the single update then still using parallel execution (for the single update), you can try to use the option maxdop with a value of 1. 


        How does your physical/virtual disk look like? TEMPDB+database+log on same physical drive ? Have you measured Disk-IO/ Page faults ? Disk Queue Length`?

Resources