26
votes
Accepted
Is "deadlock detected" really an error? Should I be suppressing them after handling them?
A deadlock is a kind of serialization error: you didn't do anything forbidden, it just so happened that there was an interaction with other active transactions that prevented your transaction from ...
- 56.4k
24
votes
Accepted
Best situation to use READ UNCOMMITTED isolation level
I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries ...
- 28.5k
21
votes
Accepted
Why does this query result in deadlock?
The FOREIGN KEY user_chat_messages_user_chat_id_foreign is the cause of your deadlock, in this situation.
Fortunately, this is easy to reproduce given the information you've provided.
Setup
CREATE ...
- 1,969
18
votes
Accepted
UPDATE heap table -> Deadlocks on RID
Without an index on FirstName, SQL Server has to check every row to see if it qualifies for the UPDATE.
It takes an update U lock when reading each row to prevent a common deadlock scenario. It could ...
- 90.3k
17
votes
Accepted
Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. It's no insurance against ...
- 182k
17
votes
Why am I getting a deadlock for a single UPDATE query?
Laurenz explained the mechanism that can lead to deadlocks, and you already included a link yourself to a more detailed explanation by Kevin:
Deadlocks in PostgreSQL when running UPDATE
Here are ...
- 182k
17
votes
Accepted
Safe to use WITH (NOLOCK) in this case?
I'm not a DBA, but a software dev with a few years of DB experience. I'm only "textbook-level" familiar with the inner workings of locking, pages, hints,
Then you should use SNAPSHOT ...
- 47.6k
16
votes
Accepted
Why am I getting a deadlock for a single UPDATE query?
Your statement modifies several rows. Each of these rows is locked when it is updated.
It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking ...
- 56.4k
15
votes
Accepted
MERGE deadlocking prevention
Is placing IX followed by X on the object eligible? Is it bug or not?
It looks a bit odd, but it is valid. At the time the IX is taken, the intention may well be to take X locks at a lower level. ...
- 90.3k
15
votes
Accepted
If a Parallelism Exchange Event deadlock is victim-less, is it a problem?
I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could ...
- 90.3k
14
votes
How to Resolve deadlock on concurrent MERGE on two tables
Concurrent MERGE statements will deadlock or produce PK violations by default, as the "scan" phase of the MERGE is performed without a restrictive lock. You need to add a lock hint for this ...
- 47.6k
14
votes
Accepted
MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock"
Yes, it's safe. Your alter table query hasn't even started.
However, the waiting for table metadata lock means, that there's another transaction operating on that table for a long time and you should ...
- 3,188
13
votes
Why can MySQL handle multiple updates concurrently and PostgreSQL can't?
HISTORY LESSON
On Mar 13, 2013, Uber had switched from MySQL to PostgrsSQL.
Surprisingly, that love affair did not last very long.
On Jun 26, 2016, Uber had switched from PostgreSQL to MySQL.
Why the ...
- 184k
13
votes
Accepted
Solving intra parallel query deadlocks
First off, intra-query parallelism deadlocks are always a SQL Server bug. There's not much you can do to fix them, other than updating SQL Server, opening a support case, suppressing parallelism (eg ...
- 47.6k
13
votes
Accepted
In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended?
My first question is, will these update statements work as intended?
Very likely, but not certain.
SQL Server guarantees it will honour the semantics of the query, and the level of ACID compliance ...
- 90.3k
13
votes
%%LOCKRES%% - SQL Server - Row identifier causing Deadlocks - possible bug
This looks like a bit of an edge case with variable length columns - in that the lockres just uses the binary representation of the index key and apparently takes no account of the column offsets - so ...
- 86.5k
12
votes
Accepted
Why can MySQL handle multiple updates concurrently and PostgreSQL can't?
Rolando already more or less described the reason why the deadlocks happen. Let me add that PostgreSQL has to take locks on the rows to be updated, in order to maintain consistency. (now() is fixed ...
- 31.6k
12
votes
Deadlock Graph and Interpretation, solution to avoid
This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.
Using Plan Explorer to open the deadlock ...
- 182k
12
votes
Accepted
How to avoid IX deadlock in check constraint
If you don't provide an index on column N, SQL Server cannot check if the target value exists efficiently, and must scan the table. The scan continues until a match is found, or scans the entire table ...
- 90.3k
11
votes
Accepted
Would a nonclustered index on the primary key speed up deletes and prevent deadlocks?
Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks ...
- 70.5k
11
votes
Accepted
How to Prevent Partitioned Columnstore Deadlocks on SELECT
Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:
FIX: A deadlock occurs when you run a parallel ...
- 29.8k
10
votes
Accepted
Help understanding why a deadlock occurred on row level index lock
I don't understand why the deadlock is happening.
For this execution plan, the sequence of locking operations involved in deleting each row is:
U lock nonclustered index (taken at the index seek)
U ...
- 90.3k
10
votes
Accepted
Serializable range deadlocks
We frequently experience deadlocks around an INSERT statement on the following table
...
the stored procedure where the deadlocks occur [uses] SERIALIZABLE
Yes. That is the expected behavior of ...
- 47.6k
10
votes
Accepted
Deadlock Graph and Interpretation, solution to avoid
Your default isolation level might be Read Committed Snapshot,
but the isolation level set by your application = repeatable read (3)
For all the update statements in the deadlock, these are the ...
- 16.5k
10
votes
Deadlock Graph and Interpretation, solution to avoid
Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:
transactionname="implicit_transaction"
This can also lead to blocking if the application doesn't ...
- 29.8k
10
votes
Accepted
How can SQL Server 2016 deadlock with a pagelock on two different tables?
This is a classic deadlock, but it can be hard to tell in SSMS or by just looking at the XML. Both tables are being accessed in both sessions.
Viewing it in SentryOne Plan Explorer is a bit easier ...
- 29.8k
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
10
votes
Cannot run query to get deadlock graph in a timely fashion
Why Does It Take A Long Time?
You have to be quite careful when querying XML. Your query is a bit of a disaster in that regard, specifically this part: where XEventData.XEvent.value('@name', 'varchar(...
9
votes
Accepted
MySql Gap Lock Deadlock on Inserts
I'm not a MySQL expert, but by the look of your Deadlock logs,
even though you are INSERTing different vehicle IDs per statement,
those require the whole datapage (238326) of the VehicleID non-...
- 1,554
Only top scored, non community-wiki answers of a minimum length are eligible