Delete is creating deadlock in SQL
0
I have two tables say T1 and T2. T1 has a reference in T2 as seen below:
Table: T1
|Col1|
|v1 |
|v2 |
|v3 |
Table: T2
|Id|Col1|Col2|
|1 |v1 |v2 |
|2 |v2 |v1 |
|3 |v1 |v3 |
|4 |v3 |v1 |
So we have a primary key of T1 as foreign keys of T2 Col1 and Col2 both.
Now whenever a request for delete comes for T1 (say I want to delete v1), its referenced row needs to be deleted first from T2(in this case Id 1,2,3,4 in T2 ) and then the record in T1 will delete.
Now when we are trying to delete this using Entity framework it got stuck in deadlock and fails.
Any suggestion on how to resolve this?
Updated EF code:
using (_dbContext as IDisposable)
{
var t1= _dbContext.T1
.Include(t1 => t1.Table3)
.FirstOrDefault(x => x.Id == Col1);
var lstT2 = _dbContext.T2
.Where(x => x.Col1 == value ||
x.Col2== value).ToList();
using (var transaction = _dbContext.BeginTransaction())
{
try
{
foreach (var t2 in lstT2)
{
_dbContext.T2.Remove(t2);
_dbContext.SaveChanges();
}
_dbContext.T1.Remove(t1);
_dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
NLogger.Log(LogLevel.Error, ex.Message);
throw ex;
}
}
sql
sql-server entity-framework entity-framework-6 database-deadlocks asked Nov 14 '18 at 10:23
tangotango
395
|
show 1 more comment
0
I have two tables say T1 and T2. T1 has a reference in T2 as seen below:
Table: T1
|Col1|
|v1 |
|v2 |
|v3 |
Table: T2
|Id|Col1|Col2|
|1 |v1 |v2 |
|2 |v2 |v1 |
|3 |v1 |v3 |
|4 |v3 |v1 |
So we have a primary key of T1 as foreign keys of T2 Col1 and Col2 both.
Now whenever a request for delete comes for T1 (say I want to delete v1), its referenced row needs to be deleted first from T2(in this case Id 1,2,3,4 in T2 ) and then the record in T1 will delete.
Now when we are trying to delete this using Entity framework it got stuck in deadlock and fails.
Any suggestion on how to resolve this?
Updated EF code:
using (_dbContext as IDisposable)
{
var t1= _dbContext.T1
.Include(t1 => t1.Table3)
.FirstOrDefault(x => x.Id == Col1);
var lstT2 = _dbContext.T2
.Where(x => x.Col1 == value ||
x.Col2== value).ToList();
using (var transaction = _dbContext.BeginTransaction())
{
try
{
foreach (var t2 in lstT2)
{
_dbContext.T2.Remove(t2);
_dbContext.SaveChanges();
}
_dbContext.T1.Remove(t1);
_dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
NLogger.Log(LogLevel.Error, ex.Message);
throw ex;
}
}
sql
sql-server entity-framework entity-framework-6 database-deadlocks asked Nov 14 '18 at 10:23
tangotango
395
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
1
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-oldSaveChangesshouldn't lock as it only performs aDELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern
– Panagiotis Kanavos
Nov 14 '18 at 10:30
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01
|
show 1 more comment
0
0
0
I have two tables say T1 and T2. T1 has a reference in T2 as seen below:
Table: T1
|Col1|
|v1 |
|v2 |
|v3 |
Table: T2
|Id|Col1|Col2|
|1 |v1 |v2 |
|2 |v2 |v1 |
|3 |v1 |v3 |
|4 |v3 |v1 |
So we have a primary key of T1 as foreign keys of T2 Col1 and Col2 both.
Now whenever a request for delete comes for T1 (say I want to delete v1), its referenced row needs to be deleted first from T2(in this case Id 1,2,3,4 in T2 ) and then the record in T1 will delete.
Now when we are trying to delete this using Entity framework it got stuck in deadlock and fails.
Any suggestion on how to resolve this?
Updated EF code:
using (_dbContext as IDisposable)
{
var t1= _dbContext.T1
.Include(t1 => t1.Table3)
.FirstOrDefault(x => x.Id == Col1);
var lstT2 = _dbContext.T2
.Where(x => x.Col1 == value ||
x.Col2== value).ToList();
using (var transaction = _dbContext.BeginTransaction())
{
try
{
foreach (var t2 in lstT2)
{
_dbContext.T2.Remove(t2);
_dbContext.SaveChanges();
}
_dbContext.T1.Remove(t1);
_dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
NLogger.Log(LogLevel.Error, ex.Message);
throw ex;
}
}
sql
sql-server entity-framework entity-framework-6 database-deadlocks asked Nov 14 '18 at 10:23
tangotango
395
I have two tables say T1 and T2. T1 has a reference in T2 as seen below:
Table: T1
|Col1|
|v1 |
|v2 |
|v3 |
Table: T2
|Id|Col1|Col2|
|1 |v1 |v2 |
|2 |v2 |v1 |
|3 |v1 |v3 |
|4 |v3 |v1 |
So we have a primary key of T1 as foreign keys of T2 Col1 and Col2 both.
Now whenever a request for delete comes for T1 (say I want to delete v1), its referenced row needs to be deleted first from T2(in this case Id 1,2,3,4 in T2 ) and then the record in T1 will delete.
Now when we are trying to delete this using Entity framework it got stuck in deadlock and fails.
Any suggestion on how to resolve this?
Updated EF code:
using (_dbContext as IDisposable)
{
var t1= _dbContext.T1
.Include(t1 => t1.Table3)
.FirstOrDefault(x => x.Id == Col1);
var lstT2 = _dbContext.T2
.Where(x => x.Col1 == value ||
x.Col2== value).ToList();
using (var transaction = _dbContext.BeginTransaction())
{
try
{
foreach (var t2 in lstT2)
{
_dbContext.T2.Remove(t2);
_dbContext.SaveChanges();
}
_dbContext.T1.Remove(t1);
_dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
NLogger.Log(LogLevel.Error, ex.Message);
throw ex;
}
}
sql
sql-server entity-framework entity-framework-6 database-deadlocks sql
sql-server entity-framework entity-framework-6 database-deadlocks asked Nov 14 '18 at 10:23
tangotango
395
asked Nov 14 '18 at 10:23
tangotango
395
edited Nov 14 '18 at 11:12
tango
asked Nov 14 '18 at 10:23
tangotango
395
asked Nov 14 '18 at 10:23
tangotango
395
asked Nov 14 '18 at 10:23
tangotango
395
395
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
1
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-oldSaveChangesshouldn't lock as it only performs aDELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern
– Panagiotis Kanavos
Nov 14 '18 at 10:30
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01
|
show 1 more comment
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
1
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-oldSaveChangesshouldn't lock as it only performs aDELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern
– Panagiotis Kanavos
Nov 14 '18 at 10:30
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
1
1
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-old
SaveChanges shouldn't lock as it only performs a DELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern– Panagiotis Kanavos
Nov 14 '18 at 10:30
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-old
SaveChanges shouldn't lock as it only performs a DELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern– Panagiotis Kanavos
Nov 14 '18 at 10:30
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01
|
show 1 more 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%2f53297915%2fdelete-is-creating-deadlock-in-sql%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%2f53297915%2fdelete-is-creating-deadlock-in-sql%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
Why would that result in a deadlock? How did you determine that? What does your code look like, and what SQL does it generate?
– CodeCaster
Nov 14 '18 at 10:27
You haven't provided the code or the table schemas so one can only guess. Delete doesn't cause deadlocks. Missing indexes though, do. Badly written,long running transactions. Using excessive locking while reading. For example, if the keys aren't indexed the server will have to scan the entire table to find matching rows, thus locking far more rows than required
– Panagiotis Kanavos
Nov 14 '18 at 10:27
1
Deadlocks are caused when queries lock too much and try to perform operations in conficting sequence. A plain-old
SaveChangesshouldn't lock as it only performs aDELETE. If you used a big transaction to cover both reading entities and deleting them, you acquired shared locks on them for the duration of the transaction for no reason. If you use a transaction per request in an ASP.NET application you also take excessive transactions for no reason. That's a well known antipattern– Panagiotis Kanavos
Nov 14 '18 at 10:30
The proper, scalable way to handle conflicts is to NOT use transactions but optimistic locking. This isn't new or specific to EF or SQL Server, it applies to every system with more than a couple of users, whether it's a client/server, desktop, web or service application. It's also explained in all EF docs and tutorials. Instead of using a transaction to ensure the data you read hasn't changed until you delete it, ADO.NET/EF/NH whatever uses a DB-provided rowversion column to detect whether someone else modified that row since it was last read. This can lead to 10x better perf - for starters
– Panagiotis Kanavos
Nov 14 '18 at 10:38
@CodeCaster: I found it using SQL Profiler. It generates SQL for deleting the T2 Columns followed by T1 column.
– tango
Nov 14 '18 at 11:01