CODEWITHSUNDEEP
sqlsql-servertransactionssql-updatedeadlock
Gerald
Gerald

Reputation: 21

Deadlock at update query only when more than two transaction at same time

When writing some SQL Server update queries for interfacing a product database, I ran into a situation where everything is fine if two transactions with these updates are running at the same time - but when more than two transactions are trying to update the same table simultaneously, some of them were deadlocked.

I broke the problem down to a "UNIQUE NONCLUSTERED" constraint to the table definition. When I remove this constraint, all transactions will wait for their needed resources and finish without errors.

Here is my sample-code to reproduce the problem:

CREATE TABLE [dbo].[profiles]
(
    [ProfileID] [int] IDENTITY(1,1) NOT NULL, 
    [ProfileName] [nvarchar](255) NOT NULL, 
    [GroupFK] [int] NULL

    CONSTRAINT [PK_Profile] 
        PRIMARY KEY CLUSTERED ([ProfileID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    -- !!! [UI_UniqueNameInGroup] seems problematic for me because i get deadlocks when executing more than two transactions at once !!!
    CONSTRAINT [UI_UniqueNameInGroup] 
        UNIQUE NONCLUSTERED ([GroupFK] ASC, [ProfileName] ASC)
                      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                            ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO profiles (ProfileName) VALUES ('PROFILE_99')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_88')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_77')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_66')

These are samples of my transactions:

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_99' 
     WHERE ProfileID = 4

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_66' 
     WHERE ProfileID = 1

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_88' 
     WHERE ProfileID = 3

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_77' 
     WHERE ProfileID = 2

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

Here are my deadlock-graphs:

deadlockgraph1.xdl

deadlockgraph2.xdl

Please help me by explaining the problem to me - I don't get it why deadlocks only happen when executing more than two transactions simultaneously.

I'm also cool with just getting a working solution for this.

Is there a solution at my side (without changing the database scheme)?

Upvotes: 2

Views: 1447

Answers (1)

Máté Farkas
Máté Farkas

Reputation: 146

Change constraint [UI_UniqueNameInGroup] to allow only row locks:

CONSTRAINT [UI_UniqueNameInGroup] UNIQUE NONCLUSTERED ([GroupFK] ASC, [ProfileName] ASC)
WITH (PAD_INDEX = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = OFF
) ON [PRIMARY]

This causes a problem because if you deny page and row lock then you force SQL Server to use table lock but other session already put a table lock.

Upvotes: 1

Related Questions