April 19, 2017 at 2:19 am
The below select is causing deadlock on our production box. How can i avoid this?
The same query is running from different servers at same time.
(@PayloadId bigint)SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId
April 19, 2017 at 2:27 am
Rechana Rajan - Wednesday, April 19, 2017 2:19 AMThe below select is causing deadlock on our production box. How can i avoid this?The same query is running from different servers at same time.
(@PayloadId bigint)SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId
We need more information in order to be of any assistance, can you please post the DDL (create table) script for [SignalR].[Messages_0] including all indices and constraints, the deadlock graph and information on the cardinalities of both the table and the result set ([PayloadId] > @PayloadId)
😎
April 19, 2017 at 4:37 am
Thanks Erikkur,
CREATE TABLE [SignalR].[Messages_0](
[PayloadId] [bigint] NOT NULL,
[Payload] [varbinary](max) NOT NULL,
[InsertedOn] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PayloadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
No other index other than clustered index.
<deadlock>
<victim-list>
<victimProcess id="process1e45468" />
</victim-list>
<process-list>
<process id="process1e45468" taskpriority="0" logused="0" waitresource="KEY: 7:72057604889575424 (be611099975b)" waittime="3094" ownerId="6154843548" transactionname="CQueryScan::BeginNotifXact" lasttranstarted="2017-04-19T11:10:21.593" XDES="0x8e45623b0" lockMode="RangeS-U" schedulerid="6" kpid="89184" status="suspended" spid="190" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2017-04-19T11:10:21.590" lastbatchcompleted="2017-04-19T11:10:21.593" lastattention="1900-01-01T00:00:00.593" clientapp=".Net SqlClient Data Provider" hostname="NXBSRV1-DC1" hostpid="43532" loginname="nxbsignalr" isolationlevel="read committed (2)" xactid="6154843547" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="38" stmtend="240" sqlhandle="0x020000001c044424b440f84184b17c85b38f51ef750390570000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@PayloadId bigint)SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId </inputbuf>
</process>
<process id="process217fc28" taskpriority="0" logused="0" waitresource="KEY: 7:72057604889575424 (cf0e8615d690)" waittime="3094" ownerId="6154843528" transactionname="CQueryScan::BeginNotifXact" lasttranstarted="2017-04-19T11:10:21.593" XDES="0x5965ec3b0" lockMode="RangeS-U" schedulerid="5" kpid="74524" status="suspended" spid="133" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2017-04-19T11:10:21.590" lastbatchcompleted="2017-04-19T11:10:21.593" lastattention="1900-01-01T00:00:00.593" clientapp=".Net SqlClient Data Provider" hostname="NXBSRV5-DC1" hostpid="54944" loginname="nxbsignalr" isolationlevel="read committed (2)" xactid="6154843526" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="38" stmtend="240" sqlhandle="0x020000001c044424b440f84184b17c85b38f51ef750390570000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@PayloadId bigint)SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057604889575424" dbid="7" objectname="SIGNALR.sys.query_notification_1955510416" indexname="cidx" id="lockdbf129280" mode="RangeS-U" associatedObjectId="72057604889575424">
<owner-list>
<owner id="process217fc28" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process1e45468" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057604889575424" dbid="7" objectname="SIGNALR.sys.query_notification_1955510416" indexname="cidx" id="lockb12698a80" mode="RangeS-U" associatedObjectId="72057604889575424">
<owner-list>
<owner id="process1e45468" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process217fc28" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
April 24, 2017 at 3:09 am
Can anyone help me on this?
April 26, 2017 at 8:15 am
How many tables is your SignalR set to use? We have the same issue, we are running SignalR in backplane with 3 tables, but are looking to expand to 6 to try to reduce the number of deadlocks that we get per day. This article was helpful for us to configure it, our next release we plan on scaling out.
https://blogs.msdn.microsoft.com/mert/2016/04/01/signalr-performance-and-scaleout-configuration/
May 1, 2017 at 6:54 am
chris.munsell - Wednesday, April 26, 2017 8:15 AMHow many tables is your SignalR set to use? We have the same issue, we are running SignalR in backplane with 3 tables, but are looking to expand to 6 to try to reduce the number of deadlocks that we get per day. This article was helpful for us to configure it, our next release we plan on scaling out.https://blogs.msdn.microsoft.com/mert/2016/04/01/signalr-performance-and-scaleout-configuration/
Thanks Chris,
We are using 4 tables now. Thankfully i am not getting any deadlock for the last 2 weeks.
May 1, 2017 at 8:07 am
That is great!
Did you do something similar to what the link I provided mentioned?
May 7, 2017 at 11:40 pm
chris.munsell - Monday, May 1, 2017 8:07 AMThat is great!
Did you do something similar to what the link I provided mentioned?
Not made any changes at the moment.
May 8, 2017 at 8:01 pm
Where does
Where lockMode="RangeS-U"
come from?
A SELECT query does not initiate any "U" kind of locks by itself.
What causes "update resource lock" to be placed within this transaction?
_____________
Code for TallyGenerator
May 9, 2017 at 6:22 am
Sergiy - Monday, May 8, 2017 8:01 PMWhere doesWhere lockMode="RangeS-U"
come from?
A SELECT query does not initiate any "U" kind of locks by itself.
What causes "update resource lock" to be placed within this transaction?
Thanks Sergiy,
Unfortunately I dont have answer for that.
May 9, 2017 at 6:51 am
Can you trace the DB calls?
But honestly - if you cannot change the code there is no hope to fix it.
The issue is within the code.
There must be an explicit transaction which includes an update on this table.
It forces U lock to be applied
The SELECT statement you see must be a part of that explicit transaction.
It selects from range (PayloadID > @PayloadID), so the lock is changed to RangeS-U.
The query as it's written has to scan the whole range.
With 2 or more such queries invoked at the same time a deadlock is guaranteed.
If only it could be changed to "SELECT TOP 1"...
_____________
Code for TallyGenerator
May 10, 2017 at 3:55 am
Sergiy - Tuesday, May 9, 2017 6:51 AMCan you trace the DB calls?But honestly - if you cannot change the code there is no hope to fix it.The issue is within the code.There must be an explicit transaction which includes an update on this table.It forces U lock to be appliedThe SELECT statement you see must be a part of that explicit transaction.It selects from range (PayloadID > @PayloadID), so the lock is changed to RangeS-U.The query as it's written has to scan the whole range.With 2 or more such queries invoked at the same time a deadlock is guaranteed.If only it could be changed to "SELECT TOP 1"...
Thanks Sergiy,
As I already told this is system generated from SignalR .
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply