How to fix deadlocks on 2 pages?
0
I have same situation like this (there is answer about PREFETCH, but answer link is garbage. I can not find good article about fixing PREFETCH).
Pseudocode of problem place:
foreach(var item in list)
{
var item2 = Select //use item info
Update // use item2 info
}
Graph
I have ideas to avoid S locks on pages and rows:
1) snapshot transaction
2) allow page lock := false && SELECT WITH(updlock).
But there is EF ORM and UoW wrapper to context and I have some difficulties with implementation of ideas.
Mybee you have better ideas or can explain situation better.
XML with changed names:
<deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>c# sql
sql-server database entity-framework asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
add a comment |
0
I have same situation like this (there is answer about PREFETCH, but answer link is garbage. I can not find good article about fixing PREFETCH).
Pseudocode of problem place:
foreach(var item in list)
{
var item2 = Select //use item info
Update // use item2 info
}
Graph
I have ideas to avoid S locks on pages and rows:
1) snapshot transaction
2) allow page lock := false && SELECT WITH(updlock).
But there is EF ORM and UoW wrapper to context and I have some difficulties with implementation of ideas.
Mybee you have better ideas or can explain situation better.
XML with changed names:
<deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>c# sql
sql-server database entity-framework asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
1
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for theSELECTlook like? Can you tune that?
– Martin Smith
Dec 31 '18 at 14:45
add a comment |
0
0
0
I have same situation like this (there is answer about PREFETCH, but answer link is garbage. I can not find good article about fixing PREFETCH).
Pseudocode of problem place:
foreach(var item in list)
{
var item2 = Select //use item info
Update // use item2 info
}
Graph
I have ideas to avoid S locks on pages and rows:
1) snapshot transaction
2) allow page lock := false && SELECT WITH(updlock).
But there is EF ORM and UoW wrapper to context and I have some difficulties with implementation of ideas.
Mybee you have better ideas or can explain situation better.
XML with changed names:
<deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>c# sql
sql-server database entity-framework asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
I have same situation like this (there is answer about PREFETCH, but answer link is garbage. I can not find good article about fixing PREFETCH).
Pseudocode of problem place:
foreach(var item in list)
{
var item2 = Select //use item info
Update // use item2 info
}
Graph
I have ideas to avoid S locks on pages and rows:
1) snapshot transaction
2) allow page lock := false && SELECT WITH(updlock).
But there is EF ORM and UoW wrapper to context and I have some difficulties with implementation of ideas.
Mybee you have better ideas or can explain situation better.
XML with changed names:
<deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list><deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list><deadlock-list>
<deadlock victim="process804e6bc8">
<process-list>
<process id="process804e6bc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:1488426" waittime="2142" ownerId="564828035" transactionname="SELECT" lasttranstarted="2018-12-19T17:40:15.957" XDES="0xa1c62330" lockMode="S" schedulerid="7" kpid="86760" status="suspended" spid="208" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-12-19T17:40:15.957" lastbatchcompleted="2018-12-19T17:40:15.957" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828035" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000621f4424c6c29ec12837bc8a85df7a3c6a7ab140">
SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Extent1]
WHERE ([Extent1].[Cl </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[Id] AS [Id],
[Project1].[xxx] AS [xxx],
[Project1].[yyyid] AS [yyyid],
[Project1].[vvv] AS [vvv],
[Project1].[fff] AS [fff],
[Project1].[lll] AS [lll],
[Project1].[ccc] AS [ccc],
[Project1].[mmm] AS [mmm]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[xxx] AS [xxx],
[Extent1].[yyyid] AS [yyyid],
[Extent1].[vvv] AS [vvv],
[Extent1].[fff] AS [fff],
[Extent1].[lll] AS [lll],
[Extent1].[ccc] AS [ccc],
[Extent1].[mmm] AS [mmm]
FROM [dbo].[sss] AS [Exte </inputbuf>
</process>
<process id="process601ddc8" taskpriority="0" logused="1976" waitresource="PAGE: 10:1:1488427" waittime="2129" ownerId="564828018" transactionname="user_transaction" lasttranstarted="2018-12-19T17:40:15.913" XDES="0xdfb74e80" lockMode="IX" schedulerid="3" kpid="93564" status="suspended" spid="191" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-19T17:40:15.977" lastbatchcompleted="2018-12-19T17:40:15.977" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="10652" loginname="name" isolationlevel="read committed (2)" xactid="564828018" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="140" stmtend="528" sqlhandle="0x02000000181bbd27d32bd4895ac7599f3d0749383576066e">
UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(32),@3 datetime2(7),@4 datetime2(7),@5 int)UPDATE [dbo].[sss]
SET [xxx] = @0, [yyyid] = @1, [vvv] = @2, [fff] = @3, [lll] = @4
WHERE ([Id] = @5)
SELECT [ccc], [mmm]
FROM [dbo].[sss]
WHERE @@ROWCOUNT > 0 AND [Id] = @5 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1488426" dbid="10" objectname="dbname.dbo.sss" id="lock12c525200" mode="IX" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process601ddc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process804e6bc8" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1488427" dbid="10" objectname="dbname.dbo.sss" id="lockc3cc5c80" mode="S" associatedObjectId="72057594551795712">
<owner-list>
<owner id="process804e6bc8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process601ddc8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>c# sql
sql-server database entity-framework c# sql
sql-server database entity-framework asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
edited Jan 2 at 9:03
AndreyMagnificent
asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
asked Dec 31 '18 at 14:02
AndreyMagnificentAndreyMagnificent
61
61
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
1
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for theSELECTlook like? Can you tune that?
– Martin Smith
Dec 31 '18 at 14:45
add a comment |
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
1
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for theSELECTlook like? Can you tune that?
– Martin Smith
Dec 31 '18 at 14:45
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
1
1
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for the
SELECT look like? Can you tune that?– Martin Smith
Dec 31 '18 at 14:45
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for the
SELECT look like? Can you tune that?– Martin Smith
Dec 31 '18 at 14:45
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988327%2fhow-to-fix-deadlocks-on-2-pages%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988327%2fhow-to-fix-deadlocks-on-2-pages%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you can find the original page in the wayback machine. web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/… - but anyway you should provide the deadlock graph, queries, and table definitions for your case
– Martin Smith
Dec 31 '18 at 14:10
1
So Process 601xxxxxx running the update - holds IX lock on page 1488426, waiting for IX Lock on page 1488427 and Process 804xxxxxx running the select - waiting for S lock on page 1488426, holds S lock on page 1488427 at read committed level. What does the execution plan for the
SELECTlook like? Can you tune that?– Martin Smith
Dec 31 '18 at 14:45