Why two select statements are causing deadlock? S and IX locksShared and IX locks causing deadlock (Sql...
g++ and clang++ different behaviour with recursive initialization of a static member
Six real numbers so that product of any five is the sixth one
What is the difference between ashamed and shamed?
Why does Starman/Roadster have radial acceleration?
How can I handle a player who pre-plans arguments about my rulings on RAW?
If nine coins are tossed, what is the probability that the number of heads is even?
What am I? I am in theaters and computer programs
How would we write a misogynistic character without offending people?
How to tighten battery clamp?
Is divide-by-zero a security vulnerability?
Understanding Kramnik's play in game 1 of Candidates 2018
Why does the author believe that the central mass that gas cloud HCN-0.009-0.044 orbits is smaller than our solar system?
Did 5.25" floppies undergo a change in magnetic coating?
I can't die. Who am I?
What is this waxed root vegetable?
What do the pedals on grand pianos do?
Compare four integers, return word based on maximum
How to mitigate "bandwagon attacking" from players?
What can I substitute for soda pop in a sweet pork recipe?
What type of postprocessing gives the effect of people standing out
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
What is better: yes / no radio, or simple checkbox?
How to count occurrences of Friday 13th
Auto Insert date into Notepad
Why two select statements are causing deadlock? S and IX locks
Shared and IX locks causing deadlock (Sql server)Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008Given two known statements, how can I replicate deadlock?Why are U locks required when using Read Committed Snapshot IsolationRun and Save multiple SELECT statementsDeadlock from select statementsDISABLE / DROP INDEX causing deadlockFinding rows affected by deadlock on page locksDeadLock - Two Process Locked Same IndexDeadlock with insert and delete
<deadlock>
<victim-list>
<victimProcess id="process516293848" />
</victim-list>
<process-list>
<process id="process516293848" taskpriority="0" logused="17436" waitresource="PAGE: 54:9:3466 " waittime="4761" ownerId="46372202" transactionguid="0x1958dbf5df37074a8dedb2eead348e1c" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:42.490" XDES="0x1fdab8690" lockMode="S" schedulerid="1" kpid="6428" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:49.270" lastbatchcompleted="2019-03-04T22:24:49.267" lastattention="1900-01-01T00:00:00.267" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46372202" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
<process id="process98aea8c8" taskpriority="0" logused="17464" waitresource="PAGE: 54:9:3466 " waittime="6028" ownerId="46371200" transactionguid="0x8abb0d250a51184b8a2b8f7c02671db5" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:41.137" XDES="0x1ff2f83b0" lockMode="S" schedulerid="1" kpid="3536" status="suspended" spid="87" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:47.997" lastbatchcompleted="2019-03-04T22:24:48.003" lastattention="1900-01-01T00:00:00.003" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46371200" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process98aea8c8" mode="IX" />
<owner id="process98aea8c8" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process516293848" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process516293848" mode="IX" />
<owner id="process516293848" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process98aea8c8" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
Here is data from Extended Events xml_deadlock report.
Could anyone explain why select statement is requiring here and IX Lock on index?
Primarly I thought that the issue may be with transaction, maybe selects are a part of bigger transaction with some updates/inserts, but I've checked that in profiler and there is only that simple select.
Maybe those selects are not a source of IX lock, is it possible that there is already opened transaction which is locking that index with IX lock?
If it's possible, what's the best way to find out which query is causing that?
performance query-performance select locking deadlock
asked 10 mins ago
zawidzawid
63
add a comment |
<deadlock>
<victim-list>
<victimProcess id="process516293848" />
</victim-list>
<process-list>
<process id="process516293848" taskpriority="0" logused="17436" waitresource="PAGE: 54:9:3466 " waittime="4761" ownerId="46372202" transactionguid="0x1958dbf5df37074a8dedb2eead348e1c" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:42.490" XDES="0x1fdab8690" lockMode="S" schedulerid="1" kpid="6428" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:49.270" lastbatchcompleted="2019-03-04T22:24:49.267" lastattention="1900-01-01T00:00:00.267" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46372202" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
<process id="process98aea8c8" taskpriority="0" logused="17464" waitresource="PAGE: 54:9:3466 " waittime="6028" ownerId="46371200" transactionguid="0x8abb0d250a51184b8a2b8f7c02671db5" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:41.137" XDES="0x1ff2f83b0" lockMode="S" schedulerid="1" kpid="3536" status="suspended" spid="87" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:47.997" lastbatchcompleted="2019-03-04T22:24:48.003" lastattention="1900-01-01T00:00:00.003" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46371200" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process98aea8c8" mode="IX" />
<owner id="process98aea8c8" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process516293848" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process516293848" mode="IX" />
<owner id="process516293848" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process98aea8c8" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
Here is data from Extended Events xml_deadlock report.
Could anyone explain why select statement is requiring here and IX Lock on index?
Primarly I thought that the issue may be with transaction, maybe selects are a part of bigger transaction with some updates/inserts, but I've checked that in profiler and there is only that simple select.
Maybe those selects are not a source of IX lock, is it possible that there is already opened transaction which is locking that index with IX lock?
If it's possible, what's the best way to find out which query is causing that?
performance query-performance select locking deadlock
asked 10 mins ago
zawidzawid
63
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago
add a comment |
<deadlock>
<victim-list>
<victimProcess id="process516293848" />
</victim-list>
<process-list>
<process id="process516293848" taskpriority="0" logused="17436" waitresource="PAGE: 54:9:3466 " waittime="4761" ownerId="46372202" transactionguid="0x1958dbf5df37074a8dedb2eead348e1c" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:42.490" XDES="0x1fdab8690" lockMode="S" schedulerid="1" kpid="6428" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:49.270" lastbatchcompleted="2019-03-04T22:24:49.267" lastattention="1900-01-01T00:00:00.267" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46372202" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
<process id="process98aea8c8" taskpriority="0" logused="17464" waitresource="PAGE: 54:9:3466 " waittime="6028" ownerId="46371200" transactionguid="0x8abb0d250a51184b8a2b8f7c02671db5" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:41.137" XDES="0x1ff2f83b0" lockMode="S" schedulerid="1" kpid="3536" status="suspended" spid="87" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:47.997" lastbatchcompleted="2019-03-04T22:24:48.003" lastattention="1900-01-01T00:00:00.003" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46371200" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process98aea8c8" mode="IX" />
<owner id="process98aea8c8" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process516293848" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process516293848" mode="IX" />
<owner id="process516293848" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process98aea8c8" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
Here is data from Extended Events xml_deadlock report.
Could anyone explain why select statement is requiring here and IX Lock on index?
Primarly I thought that the issue may be with transaction, maybe selects are a part of bigger transaction with some updates/inserts, but I've checked that in profiler and there is only that simple select.
Maybe those selects are not a source of IX lock, is it possible that there is already opened transaction which is locking that index with IX lock?
If it's possible, what's the best way to find out which query is causing that?
performance query-performance select locking deadlock
asked 10 mins ago
zawidzawid
63
<deadlock>
<victim-list>
<victimProcess id="process516293848" />
</victim-list>
<process-list>
<process id="process516293848" taskpriority="0" logused="17436" waitresource="PAGE: 54:9:3466 " waittime="4761" ownerId="46372202" transactionguid="0x1958dbf5df37074a8dedb2eead348e1c" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:42.490" XDES="0x1fdab8690" lockMode="S" schedulerid="1" kpid="6428" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:49.270" lastbatchcompleted="2019-03-04T22:24:49.267" lastattention="1900-01-01T00:00:00.267" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46372202" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
<process id="process98aea8c8" taskpriority="0" logused="17464" waitresource="PAGE: 54:9:3466 " waittime="6028" ownerId="46371200" transactionguid="0x8abb0d250a51184b8a2b8f7c02671db5" transactionname="user_transaction" lasttranstarted="2019-03-04T22:24:41.137" XDES="0x1ff2f83b0" lockMode="S" schedulerid="1" kpid="3536" status="suspended" spid="87" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-03-04T22:24:47.997" lastbatchcompleted="2019-03-04T22:24:48.003" lastattention="1900-01-01T00:00:00.003" clientapp=".Net SqlClient Data Provider" hostname="hostname" hostpid="7448" loginname="sa" isolationlevel="read committed (2)" xactid="46371200" currentdb="54" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="206" sqlhandle="0x020000000e3a5b1838f341b8a60bd993a9c62af0afe922d70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@id int)select [col1],[col2] from dbo.tab1 where [col3] = @id </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process98aea8c8" mode="IX" />
<owner id="process98aea8c8" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process516293848" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
<pagelock fileid="9" pageid="3466" dbid="54" subresource="FULL" objectname="srvName.dbo.TableName" id="lock1e9907e80" mode="IX" associatedObjectId="72057594086162432">
<owner-list>
<owner id="process516293848" mode="IX" />
<owner id="process516293848" mode="S" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process98aea8c8" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
Here is data from Extended Events xml_deadlock report.
Could anyone explain why select statement is requiring here and IX Lock on index?
Primarly I thought that the issue may be with transaction, maybe selects are a part of bigger transaction with some updates/inserts, but I've checked that in profiler and there is only that simple select.
Maybe those selects are not a source of IX lock, is it possible that there is already opened transaction which is locking that index with IX lock?
If it's possible, what's the best way to find out which query is causing that?
performance query-performance select locking deadlock
performance query-performance select locking deadlock
asked 10 mins ago
zawidzawid
63
asked 10 mins ago
zawidzawid
63
edited 14 secs ago
zawid
asked 10 mins ago
zawidzawid
63
asked 10 mins ago
zawidzawid
63
asked 10 mins ago
zawidzawid
63
63
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago
add a comment |
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f231284%2fwhy-two-select-statements-are-causing-deadlock-s-and-ix-locks%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
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f231284%2fwhy-two-select-statements-are-causing-deadlock-s-and-ix-locks%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
they are definitely part of bigger transactions. You wouldnt get IX logs or non zero transaction log used for just that
– Martin Smith
8 secs ago