Getting SQL deadlock when inserting and deleting at the same time
3
Currently I get occasional deadlocks in the MSSQL database of my .NET application. I am using stored procedures to add data to a table "DATA_CONT". Additionally there is a periodic task to delete old data from this table. Sometimes when inserting and deleting procedure perform at the same time, I get a deadlock.
The table DATA_CONT owns one clustered index containing the Foreign Key CID (uniqueidentifier) and the ResultDate (datetime2(3)).
This is the deadlock xml graph of MS SQL:
<deadlock>
<victim-list>
<victimProcess id="process6137528c8" />
</victim-list>
<process-list>
<process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
[DATA_CONT]
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1541580530] </inputbuf>
</process>
<process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1506104406] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process658113468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process6137528c8" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process658113468" mode="RangeI-N" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
For me it seems that both locks are being escalated. How can I avoid this behavior?
sql-server deadlock clustered-index asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
add a comment |
3
Currently I get occasional deadlocks in the MSSQL database of my .NET application. I am using stored procedures to add data to a table "DATA_CONT". Additionally there is a periodic task to delete old data from this table. Sometimes when inserting and deleting procedure perform at the same time, I get a deadlock.
The table DATA_CONT owns one clustered index containing the Foreign Key CID (uniqueidentifier) and the ResultDate (datetime2(3)).
This is the deadlock xml graph of MS SQL:
<deadlock>
<victim-list>
<victimProcess id="process6137528c8" />
</victim-list>
<process-list>
<process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
[DATA_CONT]
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1541580530] </inputbuf>
</process>
<process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1506104406] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process658113468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process6137528c8" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process658113468" mode="RangeI-N" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
For me it seems that both locks are being escalated. How can I avoid this behavior?
sql-server deadlock clustered-index asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
1
@p_DeleteOlderThan IS NULLdon't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks
– Panagiotis Kanavos
Nov 12 '18 at 12:44
2
Assuming the "delete old data" procedure isn't critical, consider settingDEADLOCK_PRIORITYtoLOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)
– Jeroen Mostert
Nov 12 '18 at 13:19
As Jeroen said, I setDEADLOCK_PRIORITYof delete procedure toLOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.
– Andi F.
Nov 12 '18 at 16:47
add a comment |
3
3
3
Currently I get occasional deadlocks in the MSSQL database of my .NET application. I am using stored procedures to add data to a table "DATA_CONT". Additionally there is a periodic task to delete old data from this table. Sometimes when inserting and deleting procedure perform at the same time, I get a deadlock.
The table DATA_CONT owns one clustered index containing the Foreign Key CID (uniqueidentifier) and the ResultDate (datetime2(3)).
This is the deadlock xml graph of MS SQL:
<deadlock>
<victim-list>
<victimProcess id="process6137528c8" />
</victim-list>
<process-list>
<process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
[DATA_CONT]
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1541580530] </inputbuf>
</process>
<process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1506104406] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process658113468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process6137528c8" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process658113468" mode="RangeI-N" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
For me it seems that both locks are being escalated. How can I avoid this behavior?
sql-server deadlock clustered-index asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
Currently I get occasional deadlocks in the MSSQL database of my .NET application. I am using stored procedures to add data to a table "DATA_CONT". Additionally there is a periodic task to delete old data from this table. Sometimes when inserting and deleting procedure perform at the same time, I get a deadlock.
The table DATA_CONT owns one clustered index containing the Foreign Key CID (uniqueidentifier) and the ResultDate (datetime2(3)).
This is the deadlock xml graph of MS SQL:
<deadlock>
<victim-list>
<victimProcess id="process6137528c8" />
</victim-list>
<process-list>
<process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
[DATA_CONT]
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1541580530] </inputbuf>
</process>
<process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DEafr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1506104406] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process658113468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process6137528c8" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process658113468" mode="RangeI-N" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
For me it seems that both locks are being escalated. How can I avoid this behavior?
sql-server deadlock clustered-index sql-server deadlock clustered-index asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
asked Nov 12 '18 at 12:41
Andi F.Andi F.
162
162
1
@p_DeleteOlderThan IS NULLdon't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks
– Panagiotis Kanavos
Nov 12 '18 at 12:44
2
Assuming the "delete old data" procedure isn't critical, consider settingDEADLOCK_PRIORITYtoLOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)
– Jeroen Mostert
Nov 12 '18 at 13:19
As Jeroen said, I setDEADLOCK_PRIORITYof delete procedure toLOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.
– Andi F.
Nov 12 '18 at 16:47
add a comment |
1
@p_DeleteOlderThan IS NULLdon't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks
– Panagiotis Kanavos
Nov 12 '18 at 12:44
2
Assuming the "delete old data" procedure isn't critical, consider settingDEADLOCK_PRIORITYtoLOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)
– Jeroen Mostert
Nov 12 '18 at 13:19
As Jeroen said, I setDEADLOCK_PRIORITYof delete procedure toLOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.
– Andi F.
Nov 12 '18 at 16:47
1
1
@p_DeleteOlderThan IS NULL don't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks– Panagiotis Kanavos
Nov 12 '18 at 12:44
@p_DeleteOlderThan IS NULL don't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks– Panagiotis Kanavos
Nov 12 '18 at 12:44
2
2
Assuming the "delete old data" procedure isn't critical, consider setting
DEADLOCK_PRIORITY to LOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)– Jeroen Mostert
Nov 12 '18 at 13:19
Assuming the "delete old data" procedure isn't critical, consider setting
DEADLOCK_PRIORITY to LOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)– Jeroen Mostert
Nov 12 '18 at 13:19
As Jeroen said, I set
DEADLOCK_PRIORITY of delete procedure to LOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.– Andi F.
Nov 12 '18 at 16:47
As Jeroen said, I set
DEADLOCK_PRIORITY of delete procedure to LOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.– Andi F.
Nov 12 '18 at 16:47
add a comment |
1 Answer
1
active
oldest
votes
2
I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
add a comment |
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%2f53262419%2fgetting-sql-deadlock-when-inserting-and-deleting-at-the-same-time%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
add a comment |
2
I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
add a comment |
2
2
2
I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
answered Nov 12 '18 at 13:34
ObelixxObelixx
1015
1015
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
add a comment |
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." This seems not to work in the used isolation level, but thanks for that solution!
– Andi F.
Nov 13 '18 at 9:36
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
Sorry, i didn't saw isolation level. Can you prevent "inserting" to fire together with "deleting". I suggest waiting for some result after "deleting", and then fire the "insert".
– Obelixx
Nov 22 '18 at 13:07
add a comment |
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%2f53262419%2fgetting-sql-deadlock-when-inserting-and-deleting-at-the-same-time%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
1
@p_DeleteOlderThan IS NULLdon't use a parameter if you don't want it. The server will cache an execution plan the first time a query gets executed. This can result in inefficient execution plans. An inefficient execution plan ends up locking more rows for far longer. That can easily lead to deadlocks– Panagiotis Kanavos
Nov 12 '18 at 12:44
2
Assuming the "delete old data" procedure isn't critical, consider setting
DEADLOCK_PRIORITYtoLOW, deleting in small batches, assigning a liberal retry policy and ignoring deadlocks from this process altogether, checking only if the deletes happen fast enough to keep the table in line. Aside from all other things you might do to reduce/eliminate deadlocks, it often isn't possible or profitable to completely get rid of them when ignoring them will do. (Obviously, this isn't true of all deadlocks because not everything can be retried -- but cleanup can.)– Jeroen Mostert
Nov 12 '18 at 13:19
As Jeroen said, I set
DEADLOCK_PRIORITYof delete procedure toLOW. So if there is a deadlock again, the less important task will be canceled. The hint of Panagiotis is good, I have to try to avoid the optional filter parameter. For testing I removed this parameter, but I still get deadlocks.– Andi F.
Nov 12 '18 at 16:47