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 ...
user avatar
  • 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 ...
user avatar
  • 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-...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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? ...
user avatar
  • 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 ...
user avatar
  • 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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 : ...
user avatar
  • 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 ...
user avatar
  • 37.5k

Only top scored, non community-wiki answers of a minimum length are eligible