4

We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored procedure.

I have searched the forum and web in particular tried different combination of locking hints/isolation levels but no luck, either I am doing something very basic in very wrong way or this is a db configuration issue.

The culprit query in question is:

UPDATE DB_NAME.DBO.DAS_TIMESLOT
SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
WHERE DAS_TIMESLOT_ID = 628 AND TOKEN_COUNT < MAX_TOKEN_LIMIT

I think it is classic example of Read/Write Deadlock, what is happening in this query is that initially there is a clustered index seek then after matching rows are found a clustered index update is performed, now the former operation no matter what i do always places some kind of lock (S, IS, IX, U) and the latter operation then converts this lock (U, X) which causes deadlock for 2 or more requests. Any help or suggestion will be much appreciated.

Following are 2 of many deadlock graph taken using SQL Server profiler:

Isolation Level Serialization with Locking Hints

<deadlock-list>
 <deadlock victim="process47f025c38">
  <process-list>
   <process id="process47f025c38" taskpriority="0" logused="0" waitresource="KEY: 11:72057594041008128 (7a6324b54c29)" waittime="50" ownerId="72854132" transactionname="implicit_transaction" lasttranstarted="2016-03-18T23:45:40.903" XDES="0x47bb52d28" lockMode="X" schedulerid="3" kpid="3176" status="suspended" spid="188" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-18T23:45:40.907" lastbatchcompleted="2016-03-18T23:45:40.903" lastattention="1900-01-01T00:00:00.903" clientapp="WebSphere Application Server" hostname="khilt-1328" hostpid="0" loginname="sa" isolationlevel="serializable (4)" xactid="72854132" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="DB_NAME.dbo.uspCreateDasAppointment" line="15" stmtstart="774" stmtend="1194" sqlhandle="0x03000b00de675d2493378701cda5000001000000000000000000000000000000000000000000000000000000">
UPDATE DB_NAME.DBO.DAS_TIMESLOT
    WITH(XLOCK, ROWLOCK)
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT &lt; MAX_TOKEN_LIMIT     </frame>
     <frame procname="adhoc" line="1" stmtstart="162" sqlhandle="0x01000b00caac4500800908210400000000000000000000000000000000000000000000000000000000000000">
Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6     </frame>
    </executionStack>
    <inputbuf>
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 datetime2,@P4 bigint,@P5 bigint,@P6 bigint)Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6                                                            </inputbuf>
   </process>
   <process id="process476709868" taskpriority="0" logused="0" waitresource="KEY: 11:72057594041008128 (7a6324b54c29)" waittime="51" ownerId="72854131" transactionname="implicit_transaction" lasttranstarted="2016-03-18T23:45:40.900" XDES="0x47bb4b6a8" lockMode="X" schedulerid="1" kpid="7316" status="suspended" spid="166" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-18T23:45:40.907" lastbatchcompleted="2016-03-18T23:45:40.903" lastattention="1900-01-01T00:00:00.903" clientapp="WebSphere Application Server" hostname="khilt-1328" hostpid="0" loginname="sa" isolationlevel="serializable (4)" xactid="72854131" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="DB_NAME.dbo.uspCreateDasAppointment" line="15" stmtstart="774" stmtend="1194" sqlhandle="0x03000b00de675d2493378701cda5000001000000000000000000000000000000000000000000000000000000">
UPDATE DB_NAME.DBO.DAS_TIMESLOT
    WITH(XLOCK, ROWLOCK)
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT &lt; MAX_TOKEN_LIMIT     </frame>
     <frame procname="adhoc" line="1" stmtstart="162" sqlhandle="0x01000b00caac4500800908210400000000000000000000000000000000000000000000000000000000000000">
Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6     </frame>
    </executionStack>
    <inputbuf>
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 datetime2,@P4 bigint,@P5 bigint,@P6 bigint)Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6                                                            </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041008128" dbid="11" objectname="DB_NAME.dbo.DAS_TIMESLOT" indexname="1" id="lock447cee380" mode="S" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process476709868" mode="S"/>
     <owner id="process476709868" mode="X" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process47f025c38" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041008128" dbid="11" objectname="DB_NAME.dbo.DAS_TIMESLOT" indexname="1" id="lock447cee380" mode="S" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process47f025c38" mode="S"/>
     <owner id="process47f025c38" mode="X" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process476709868" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Isolation Level Read Uncommitted Allow Snapshot with No Locking Hints

<deadlock-list>
 <deadlock victim="process476700558">
  <process-list>
   <process id="process476700558" taskpriority="0" logused="0" waitresource="KEY: 11:72057594041008128 (7a6324b54c29)" waittime="1553" ownerId="78077493" transactionname="implicit_transaction" lasttranstarted="2016-03-21T16:15:52.757" XDES="0x47bb4b6a8" lockMode="U" schedulerid="4" kpid="9484" status="suspended" spid="109" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-21T16:15:52.760" lastbatchcompleted="2016-03-21T16:15:52.757" lastattention="1900-01-01T00:00:00.757" clientapp="WebSphere Application Server" hostname="khilt-1328" hostpid="0" loginname="sa" isolationlevel="read uncommitted (1)" xactid="78077493" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="DB_NAME.dbo.uspCreateDasAppointment" line="15" stmtstart="782" stmtend="1206" sqlhandle="0x03000b00de675d2484ea0b01d0a5000001000000000000000000000000000000000000000000000000000000">
UPDATE DB_NAME.DBO.DAS_TIMESLOT
    --WITH(XLOCK, PAGLOCK)
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT &lt; MAX_TOKEN_LIMIT     </frame>
     <frame procname="adhoc" line="1" stmtstart="162" sqlhandle="0x01000b00caac4500e05ae8290400000000000000000000000000000000000000000000000000000000000000">
Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 datetime2,@P4 bigint,@P5 bigint,@P6 bigint)Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6                                                            </inputbuf>
   </process>
   <process id="process473825c38" taskpriority="0" logused="0" waitresource="KEY: 11:72057594041008128 (7a6324b54c29)" waittime="1556" ownerId="78077492" transactionname="implicit_transaction" lasttranstarted="2016-03-21T16:15:52.757" XDES="0x47683f6a8" lockMode="X" schedulerid="3" kpid="22328" status="suspended" spid="172" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-21T16:15:52.760" lastbatchcompleted="2016-03-21T16:15:52.757" lastattention="1900-01-01T00:00:00.757" clientapp="WebSphere Application Server" hostname="khilt-1328" hostpid="0" loginname="sa" isolationlevel="read uncommitted (1)" xactid="78077492" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="DB_NAME.dbo.uspCreateDasAppointment" line="15" stmtstart="782" stmtend="1206" sqlhandle="0x03000b00de675d2484ea0b01d0a5000001000000000000000000000000000000000000000000000000000000">
UPDATE DB_NAME.DBO.DAS_TIMESLOT
    --WITH(XLOCK, PAGLOCK)
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT &lt; MAX_TOKEN_LIMIT     </frame>
     <frame procname="adhoc" line="1" stmtstart="162" sqlhandle="0x01000b00caac4500e05ae8290400000000000000000000000000000000000000000000000000000000000000">
Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6     </frame>
    </executionStack>
    <inputbuf>
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 datetime2,@P4 bigint,@P5 bigint,@P6 bigint)Exec uspCreateDasAppointment @P0, @P1, @P2, @P3, @P4, @P5, @P6                                                            </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041008128" dbid="11" objectname="DB_NAME.dbo.DAS_TIMESLOT" indexname="1" id="lock440d89480" mode="U" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process473825c38" mode="U"/>
     <owner id="process473825c38" mode="X" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process476700558" mode="U" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041008128" dbid="11" objectname="DB_NAME.dbo.DAS_TIMESLOT" indexname="1" id="lock440d89480" mode="U" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process476700558" mode="S"/>
     <owner id="process476700558" mode="U" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process473825c38" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Stored procedure:

ALTER PROCEDURE uspCreateDasAppointment
    @commonProfileId numeric(18,0),
    @dasApplicationId numeric(18,0),
    @dasTimeSlotId numeric(18,0),
    @appointmentDate date,
    @regionalCenterId numeric(18,0),
    @statusCodeId numeric(18,0),
    @appointmentTypeCodeId numeric(18,0)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @updatedTokenCount int
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    UPDATE DB_NAME.DBO.DAS_TIMESLOT
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT < MAX_TOKEN_LIMIT

    IF @updatedTokenCount IS NULL
        BEGIN
            RAISERROR(  N'Token limit reached for selected timeslot',
                        11,
                        1)
            RETURN
        END

    SELECT '123' AS appointmentNumber
END

Edited to Include T-SQL of table and index:

CREATE TABLE [dbo].[DAS_TIMESLOT]
(
    [DAS_TIMESLOT_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [TITLE] [varchar](255) NULL,
    [DURATION_IN_MINUTES] [int] NOT NULL,
    [START_HOURS_OF_TIMESLOT] [int] NOT NULL,
    [END_HOURS_OF_TIMESLOT] [int] NOT NULL,
    [TOKEN_START_SEQUENCE] [int] NOT NULL,
    [TOKEN_COUNT] [int] NOT NULL,
    [LOCK_VERSION] [int] NOT NULL,
    [ACTIVE_IND] [tinyint] NOT NULL,
    [CREATE_DATETIME] [datetime] NOT NULL,
    [CREATED_BY] [varchar](50) NOT NULL,
    [MODIFIED_DATETIME] [datetime] NOT NULL,
    [MODIFIED_BY] [varchar](50) NOT NULL,
    [MAX_TOKEN_LIMIT] [int] NOT NULL,
    [REGIONAL_CENTER_ID] [numeric](18, 0) NOT NULL,
    [TIMESLOT_DATE] [date] NOT NULL,
 CONSTRAINT [PK_DAS_TIMESLOT] PRIMARY KEY CLUSTERED 
(
    [DAS_TIMESLOT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[DAS_TIMESLOT] ADD  CONSTRAINT [PK_DAS_TIMESLOT] PRIMARY KEY CLUSTERED 
(
    [DAS_TIMESLOT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Improve this question
6
  • 2
    It would be helpful to see the T-SQL script of the table definition along with any indexes on the table.
    – Hannah Vernon ♦
    Mar 21, 2016 at 16:48
  • Also, the stored proc either has a bunch of unused parameters, or you're not showing us the entire code. It's important to show the actual code you're using, not some "dumbed-down" version.
    – Hannah Vernon ♦
    Mar 21, 2016 at 16:52
  • @MaxVernon Post Edited to include T-SQL of Table plus there is only 1 index (Primary Key Clustered). Also you are right about store procedure I have deliberately shorten it just to focus on mentioned particular query, I assure you that this version is still producing the same deadlock. Let me know if I have clarified, otherwise I will upload the whole store procedure.
    – Aslam Kumhar
    Mar 21, 2016 at 17:43
  • Do you have any triggers on the updated table?
    – Steve Mangiameli
    Mar 21, 2016 at 17:50
  • @SteveMangiameli No triggers
    – Aslam Kumhar
    Mar 21, 2016 at 17:53

1 Answer 1

Reset to default
-2 
ALTER PROCEDURE uspCreateDasAppointment
    @commonProfileId numeric(18,0),
    @dasApplicationId numeric(18,0),
    @dasTimeSlotId numeric(18,0),
    @appointmentDate date,
    @regionalCenterId numeric(18,0),
    @statusCodeId numeric(18,0),
    @appointmentTypeCodeId numeric(18,0)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @updatedTokenCount int

    UPDATE DB_NAME.DBO.DAS_TIMESLOT WITH (UPDLOCK, ROWLOCK)
    SET TOKEN_COUNT = (TOKEN_COUNT + 1), @updatedTokenCount = TOKEN_COUNT
    WHERE DAS_TIMESLOT_ID = @dasTimeSlotId AND TOKEN_COUNT < MAX_TOKEN_LIMIT

    IF @updatedTokenCount IS NULL
    BEGIN
        RAISERROR(N'Token limit reached for the selected timeslot', 11, 1)
        RETURN
    END

    SELECT '123' AS appointmentNumber
END

consider using the ROWLOCK hint in conjunction with UPDLOCK to acquire locks on individual rows instead of a whole page or table level, which can further reduce the risk of deadlocks.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.