10
votes
Accepted
SQL Server Deadlock between two selects and one update on one table
When looking at the deadlock graph:
Update query - SPID 75
The update query locking (spid 75) is pretty straightforward, an X (exclusive) lock is requested on the key/row value, while that row is ...
- 16.5k
6
votes
SQL Server - Exclusive lock taken by a SELECT statement - Why?
The first thing you should do is run this:
SELECT sp = OBJECT_NAME(1686297067, 32);
That'll get you the name of the stored procedure that's part of the deadlock, which SQL Server couldn't be bothered ...
6
votes
Accepted
What does exchangevent indicate in SQL deadlock graph?
Exchange Events provide details and context on the deadlock itself. Specifically the WaitType e_waitPipeGetRow is a resource that one SELECT statement owns and another SELECT statement is waiting on, ...
- 39.5k
5
votes
Accepted
Deadlock Graph - Surviving Statement inputbuf does not show the object that was locked
There are circumstances in which a lock can be taken on a table that isn't the immediate target of the UPDATE (e.g. foreign keys or update triggers) but I assume this is not the case here?
Certainly ...
- 86.5k
4
votes
Accepted
Why is my non clustered index causing this deadlock?
How could my index be causing this deadlock and how should it be fixed without complete removal of the index?
Because the plan must scan the entire index to find the rows with that @orderid. So each ...
- 47.6k
4
votes
Deadlock on Non Clustered Index
mess
It looks like whomever wrote those queries was intentionally avoiding decades of blog posts about SQL Server query performance.
For reference, here are a couple of mine:
How To Do Date Math In ...
4
votes
Accepted
How to resolve linenumber in deadlock graph XML to line number displayed in SSMS sproc definition?
The line number and statement offsets in the deadlock report are based on the actual stored procedure text. SSMS scripting will add additional lines for the sticky QUOTED_IDENTIFIER and ANSI_NULLS, ...
- 28.5k
3
votes
Accepted
What would cause this SQL Server deadlock?
Because you have no suitable index on the table, you get a full table scan.
The table scan is going parallel, which means that access is happening out-of-order. You could solve this by using OPTION(...
- 14.5k
2
votes
What does the process shown in the deadlock graph represent?
The process ID in the deadlock graph is an internal ID that represents a particular Unit Of Work. It is not related to the operating system's process IDs. As far as the OS is concerned there is only ...
- 14.5k
2
votes
What does the process shown in the deadlock graph represent?
You will need to read up on the SQL Server scheduler to get the exact idea on how this works.
The simple explanination is that she SQL Server OS (SQLOS) is a non-preemptive scheduler where all queries ...
- 4,734
2
votes
How to capture the exact SQL text involved in deadlock (locks) using extended events?
A session could hold a lock for a very long time, and run many queries before deadlocking. In order to provide what you're describing, SQL Server would need to keep many prior/completed batches in ...
- 16.3k
2
votes
Accepted
Postgres 9.5 deadlock with many updates on a table + many "select for update" on another table
There is no deterministic ORDER BY in:
SELECT FROM * machines FOR UPDATE;
So multiple transactions might try to obtain row locks in different (arbitrary) order and interlock.
Also, why would you lock ...
- 182k
2
votes
Accepted
Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled?
Read Committed Snapshot don't affect the lock compatibility matrix.
For reads it means that shared locks aren't acquired, and reads will use row versioning instead. Assuming you are using read ...
- 18k
2
votes
Accepted
Why my clustered index causing deadlock (Keylocks and Pagelocks)
most likely
You have a read query deadlocking with a write query, which means one or both query plans have one or more key lookups in them.
<owner-list>
<owner id="...
1
vote
SQL Server - Exclusive lock taken by a SELECT statement - Why?
So it turns out that SSRS does indeed wrap the datasets in a transaction, based on this article.
What's the deal with transactions in a stored procedure called by an SSRS report?
- 431
Only top scored, non community-wiki answers of a minimum length are eligible