3

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
  • 2
    It would be helpful to see the T-SQL script of the table definition along with any indexes on the table. – Max Vernon Mar 21 '16 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. – Max Vernon Mar 21 '16 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 '16 at 17:43
  • Do you have any triggers on the updated table? – Steve Mangiameli Mar 21 '16 at 17:50
  • @SteveMangiameli No triggers – Aslam Kumhar Mar 21 '16 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.