22
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 and gap locks here:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
The exclusive lock request is coming from the "for update" ...
22
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 for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct.
Sadly, I see ...
19
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->A (process A waits on B, B waits on C, C waits on A)
...
N nodes: P1->P2->...Pn->P1
To construct the Px->Py primitive (Process X waits on Process Y) use ...
18
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 there is a victim. So now you have a process that didn't complete the work that it intended to complete. And, in good theory applications should have some sort ...
18
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 take a shared S lock, but that would still be blocked by the exclusive X lock held by the first transaction.
17
This has been reported no less than four times (but all traces have been removed from the WayBack Machine since Connect was murdered). This one was closed as fixed:
connect.microsoft.com/SQLServer/feedback/details/365876/
But that wasn't true. (Also look at the workarounds section - the workaround I suggested is not always going to be acceptable.)
This one ...
17
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 mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not ...
17
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 DATABASE dba210949;
USE dba210949;
CREATE TABLE user_chats
(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP DEFAULT ...
16
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 requests an update (U) page lock on the same page, and is blocked
spid 54 ecid 2 requests an update (U) page lock on the same page...
Pages are being prefetched ...
16
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 serializable transaction isolation. But that's more expensive and you need to prepare to repeat commands on serialization failure.
Your best course of action is probably ...
answered Jun 17 '14 at 12:12
Erwin Brandstetter
144k19 gold badges343 silver badges487 bronze badges
16
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, 181, 561)
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', ...
16
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 lookup in this case):
As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in ...
15
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
From that you can then run the following statement to identify the row in the table (if it still exists):-
SELECT %%LOCKRES%%, *
FROM [TABLE NAME] WITH(INDEX(...
15
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?
Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses ...
15
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 confirm this theory by capturing exchange spills and matching them up (or not) to the deadlock.
Writing exchange buffers to tempdb to resolve a deadlock is not ideal. ...
15
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 isolation, or set your database to READ COMMITTED SNAPSHOT, because it's fundamentally simpler to write correct, scalable, deadlock-free code.
It's a common ...
answered Dec 14 '20 at 22:10
David Browne - Microsoft
37.8k3 gold badges39 silver badges83 bronze badges
13
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 the index that is returned from sys.partitions for the given HOBT_ID when looking up the %%lockres%% (via an index query hint). This index is not always the PK ...
13
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. There's nothing to say that such locks must actually be taken. After all, there might not be anything to lock at the lower level; the engine cannot know that ahead ...
13
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 deadlocks.
Most importantly, add a consistent order to input rows with ORDER BY. To make sure the order is enforced I use a CTE, which materializes the result. (...
13
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 rather kill that one to solve the problem. (read more about it here: 8.11.4 Metadata Locking)
To find this transaction, this thread is probably helpful: How do ...
13
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 adding a MAXDOP 1 hint), or trying to get a query that uses a simpler plan.
In this case in EF, if you can, set UseDatabaseNullSemantics to true. Otherwise the ...
answered Aug 28 '18 at 20:53
David Browne - Microsoft
37.8k3 gold badges39 silver badges83 bronze badges
12
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 you see they are all trying to access the same resource (presumably the entire table):
Also a slightly less scary way to see this is to optimize layout and ...
12
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 determined by the effective isolation level. Beyond that, all is implementation detail (including what type(s) of locks are taken, when, and for how long they ...
11
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 about face ???
YOUR ACTUAL QUESTION
Running an UPDATE is surprisingly micromanaged in PostgreSQL.
There are two DDL System Identifiers in PostgreSQL that must ...
11
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 to work. See Why is TSQL MERGE failing with a Primary Key Violation? Isn’t it atomic? by David Browne for MERGE locking details.
Or just fix it like this:
...
answered Aug 14 '17 at 14:52
David Browne - Microsoft
37.8k3 gold badges39 silver badges83 bronze badges
10
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 :
declare @databaseName varchar(100) = 'yourdatabaseName' --CHANGE HERE !
declare @keyValue varchar(100) = 'KEY: 9:72057632651542528 (543066506c7c)' --Output from ...
10
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 lock generated by the insert is obtained before the exclusive lock on the table:
I believe the issue with this is that a SQL function will do parsing (and
...
10
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 to the beginning of the transaction, meaning that two concurrent updates will want to update a given row to a different timestamp, which has to be resolved ...
10
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 new row version for every update because no index values are being changed. This case is known as a HOT (Heap-Only Tupple) update. See e.g. here, here or here (...
answered Oct 12 '16 at 6:38
a_horse_with_no_name
68.2k12 gold badges133 silver badges170 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible