Prevent deadlock in SQL using query wait option
We are using Amazon RDS for DB hosting. Recently we have seen occasional deadlocks. We tried to resolve it using @@LOCK_TIMEOUT but later found out that its only for the session not at database level.
I found this link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175463(v=sql.110)
which says you can set the query wait at the database level and set a expiry time. But there is a section which says it's not recommended
Can someone please guide me what to use to set lock timeout at database level to avoid deadlocks. If something from the code can be achieved that can be feasible too.
We use Entity Framework 4
FYI: We have checked profiler, there are no query issues causing deadlock maybe concurrency.
sql-server database database-deadlocks asked Nov 16 '18 at 5:56
JayJay
388
add a comment |
We are using Amazon RDS for DB hosting. Recently we have seen occasional deadlocks. We tried to resolve it using @@LOCK_TIMEOUT but later found out that its only for the session not at database level.
I found this link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175463(v=sql.110)
which says you can set the query wait at the database level and set a expiry time. But there is a section which says it's not recommended
Can someone please guide me what to use to set lock timeout at database level to avoid deadlocks. If something from the code can be achieved that can be feasible too.
We use Entity Framework 4
FYI: We have checked profiler, there are no query issues causing deadlock maybe concurrency.
sql-server database database-deadlocks asked Nov 16 '18 at 5:56
JayJay
388
1
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17
add a comment |
We are using Amazon RDS for DB hosting. Recently we have seen occasional deadlocks. We tried to resolve it using @@LOCK_TIMEOUT but later found out that its only for the session not at database level.
I found this link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175463(v=sql.110)
which says you can set the query wait at the database level and set a expiry time. But there is a section which says it's not recommended
Can someone please guide me what to use to set lock timeout at database level to avoid deadlocks. If something from the code can be achieved that can be feasible too.
We use Entity Framework 4
FYI: We have checked profiler, there are no query issues causing deadlock maybe concurrency.
sql-server database database-deadlocks asked Nov 16 '18 at 5:56
JayJay
388
We are using Amazon RDS for DB hosting. Recently we have seen occasional deadlocks. We tried to resolve it using @@LOCK_TIMEOUT but later found out that its only for the session not at database level.
I found this link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175463(v=sql.110)
which says you can set the query wait at the database level and set a expiry time. But there is a section which says it's not recommended
Can someone please guide me what to use to set lock timeout at database level to avoid deadlocks. If something from the code can be achieved that can be feasible too.
We use Entity Framework 4
FYI: We have checked profiler, there are no query issues causing deadlock maybe concurrency.
sql-server database database-deadlocks sql-server database database-deadlocks asked Nov 16 '18 at 5:56
JayJay
388
asked Nov 16 '18 at 5:56
JayJay
388
asked Nov 16 '18 at 5:56
JayJay
388
asked Nov 16 '18 at 5:56
JayJay
388
asked Nov 16 '18 at 5:56
JayJay
388
388
1
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17
add a comment |
1
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17
1
1
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17
add a comment |
1 Answer
1
active
oldest
votes
The @@LOCK_TIMEOUT is nothing to deal with deadlocks, it defines only the maximal time which will pass before Microsoft SQL Server attempts to lock some resource and then returns a locking error.
The deadlock situation means that two or more process already locked some resources but there is a cyclic dependency between two or more threads, or processes, for some set of resources.
Hence, regardless the value of @@LOCK_TIMEOUT the deadlock cannot be prevented in such manner. Please take a look to Analyze Deadlocks with SQL Server Profiler article.
answered Nov 16 '18 at 9:21
sergeserge
70148
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
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
});
}
});
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%2f53332213%2fprevent-deadlock-in-sql-using-query-wait-option%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
The @@LOCK_TIMEOUT is nothing to deal with deadlocks, it defines only the maximal time which will pass before Microsoft SQL Server attempts to lock some resource and then returns a locking error.
The deadlock situation means that two or more process already locked some resources but there is a cyclic dependency between two or more threads, or processes, for some set of resources.
Hence, regardless the value of @@LOCK_TIMEOUT the deadlock cannot be prevented in such manner. Please take a look to Analyze Deadlocks with SQL Server Profiler article.
answered Nov 16 '18 at 9:21
sergeserge
70148
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
add a comment |
The @@LOCK_TIMEOUT is nothing to deal with deadlocks, it defines only the maximal time which will pass before Microsoft SQL Server attempts to lock some resource and then returns a locking error.
The deadlock situation means that two or more process already locked some resources but there is a cyclic dependency between two or more threads, or processes, for some set of resources.
Hence, regardless the value of @@LOCK_TIMEOUT the deadlock cannot be prevented in such manner. Please take a look to Analyze Deadlocks with SQL Server Profiler article.
answered Nov 16 '18 at 9:21
sergeserge
70148
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
add a comment |
The @@LOCK_TIMEOUT is nothing to deal with deadlocks, it defines only the maximal time which will pass before Microsoft SQL Server attempts to lock some resource and then returns a locking error.
The deadlock situation means that two or more process already locked some resources but there is a cyclic dependency between two or more threads, or processes, for some set of resources.
Hence, regardless the value of @@LOCK_TIMEOUT the deadlock cannot be prevented in such manner. Please take a look to Analyze Deadlocks with SQL Server Profiler article.
answered Nov 16 '18 at 9:21
sergeserge
70148
The @@LOCK_TIMEOUT is nothing to deal with deadlocks, it defines only the maximal time which will pass before Microsoft SQL Server attempts to lock some resource and then returns a locking error.
The deadlock situation means that two or more process already locked some resources but there is a cyclic dependency between two or more threads, or processes, for some set of resources.
Hence, regardless the value of @@LOCK_TIMEOUT the deadlock cannot be prevented in such manner. Please take a look to Analyze Deadlocks with SQL Server Profiler article.
answered Nov 16 '18 at 9:21
sergeserge
70148
answered Nov 16 '18 at 9:21
sergeserge
70148
answered Nov 16 '18 at 9:21
sergeserge
70148
answered Nov 16 '18 at 9:21
sergeserge
70148
70148
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
add a comment |
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
thanks for posting an answer. As I was explaining Mitch in the comment above, deadlock in my Application occurs on a page with only select queries. I will go through the link that you have posted. But can two select statements in different processes create a deadlock?
– Jay
Nov 18 '18 at 22:30
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
and one more question. if a table has table lock is there a more chance of getting a deadlock over a row lock?
– Jay
Nov 18 '18 at 22:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
@Jay the problem is not in the table lock but 2+ tables/pages/rows locked by2+ transactions in a different order. Check that all transactions lock resources in same order. You need catch deadlock in Profiler and see the concerned resources. Take a look at my simple example "How to simulate and catch deadlock"
– serge
Nov 19 '18 at 8:32
add a comment |
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.
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%2f53332213%2fprevent-deadlock-in-sql-using-query-wait-option%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
"We have checked profiler, there are no query issues causing deadlock" - well, clearly that's incorrect!
– Mitch Wheat
Nov 16 '18 at 6:02
@MitchWheat I have limited knowledge about database so apologies if what I am doing is incorrect. I am following few articles on internet. We are getting most deadlocks on one page. I was trying to figure out if there are any update/ insert on that page because from UI its just a search page (so my assumption was to see only select statements). In the profiler I saw few update queries - so I removed them believing all the threads now on this page will only execute select statements which will be fine because of shared locks, However, we are still seeing deadlocks and I have no clue why.
– Jay
Nov 18 '18 at 22:17