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 ...
- 24.3k
23
votes
Accepted
Meaning of 'locks rec but not gap waiting' in deadlock report
Question 1: InnoDB is trying to get an exclusive lock on the row (it's actually a lock on the clustered index record, the PK), but not the surrounding gap (a gap lock). You can read more about record ...
- 1,231
19
votes
Accepted
Simulate deadlock with more than 2 processes
If you know how to simulate it with 2, you know how to simulate it with 3, or with N. You have to build a cyclic graph:
2 nodes: A->B->A (process A waits on B, process B waits on A)
3 nodes: A->B->C-...
- 50.9k
19
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,834
18
votes
Are deadlocks a serious problem?
It's typically not the count of deadlocks that can cause a serious problem, but the reason to investigate would be to find out what workloads are deadlocking on each other. After all, in a deadlock ...
- 41.4k
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 ...
- 71k
17
votes
Accepted
View last several innodb deadlocks
There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks
When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the ...
- 174k
16
votes
Deadlock on Delete Statement
What I am looking for is why I am getting a DELETE/DELETE deadlock.
It appears the deadlock occurs because:
spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
spid 166 ecid 3 ...
- 71k
16
votes
Accepted
Optimizing concurrent updates in Postgres
There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order:
UPDATE with ORDER BY
To avoid deadlocks with absolute certainty, you could run your statements in ...
- 152k
16
votes
MySQL InnoDB Deadlock For 2 simple insert queries
HERE ARE THE FACTS
Here are the two INSERTs
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, ...
- 174k
16
votes
Accepted
SQL Server Index Update Deadlock
Is there a way to prevent the deadlock while maintaining the same queries?
The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID ...
- 71k
16
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 ...
- 40.2k
15
votes
How can I convert a key in a SQL Server deadlock report to the value?
You have the hobt_id so the following query will identify the table:-
SELECT o.name
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE p.hobt_id = 72057632651542528
...
- 952
15
votes
Accepted
Good reasons to use SELECT... WITH XLOCK?
As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right?
...
- 71k
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 ...
- 71k
14
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. ...
- 71k
13
votes
Accepted
How can I convert a key in a SQL Server deadlock report to the value?
The answers from @Kin, @AaronBertrand, and @DBAFromTheCold are great and were very helpful. One important piece of info I found during testing that the other answers left out is that you need to use ...
- 736
13
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. So it's no insurance against ...
- 152k
13
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,028
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 ...
- 40.2k
12
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 ...
- 174k
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 ...
- 28.9k
12
votes
Why can MySQL handle multiple updates concurrently and PostgreSQL can't?
I don't think this has anything to do with how UPDATEs are handled by Postgres or MySQL (or Uber's somewhat moot reasons to switch back)
Additionally, with the given setup, Postgres will not create a ...
- 71.3k
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.
If you expand the waiter/owner list ...
- 176k
12
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 ...
- 71k
11
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 ...
- 40.2k
11
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 ...
- 33.2k
10
votes
How can I convert a key in a SQL Server deadlock report to the value?
The is a supplement to the answers already posted by DBAFromTheCold and Aaron Bertrand.
Microsoft has still left %%lockres%% as undocumented feature.
Below is the script that will help you :
...
- 61.2k
10
votes
Accepted
Simultaneous calls to the same function: how are deadlocks occurring?
I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the ...
- 37.5k
Only top scored, non community-wiki answers of a minimum length are eligible