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 < 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 < 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 < 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 < 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