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 ...
21
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" ...
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 ...
answered Feb 4 '15 at 14:35
Thomas Stringer
40.4k99 gold badges107107 silver badges151151 bronze badges
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.
answered Oct 14 '16 at 14:38
17
To answer that I have to take a little detour, so bear with me.
If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource ...
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 ...
answered Nov 6 '13 at 18:55
Aaron Bertrand
170k2424 gold badges358358 silver badges566566 bronze badges
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 ...
answered Dec 31 '14 at 18:05
RolandoMySQLDBA
163k2929 gold badges269269 silver badges460460 bronze badges
15
First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones.
It is possible to avoid deadlocks altogether. I have no ...
15
This exact issue was just announced on Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2. It is linked from Cumulative update package 4 for SQL Server 2008 R2 SP2.
It finally pays off to read SQL Server fix descriptions.
15
Well from an application point of view there are:
connection timeout (how long the app is willing to wait to establish a connection to SQL Server)
command timeout (how long the app is willing to wait for a command to complete, including pulling the results down from SQL Server)
Back in my classic ASP days, the defaults for these were 15 and 30 seconds ...
answered Jan 11 '13 at 16:16
Aaron Bertrand
170k2424 gold badges358358 silver badges566566 bronze badges
15
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 ...
answered May 28 '14 at 5:30
15
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
128k1616 gold badges297297 silver badges442442 bronze badges
15
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 ...
answered Jan 8 '15 at 18:19
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 ...
answered Jan 21 '16 at 16:36
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. ...
answered Feb 13 '18 at 14:19
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
30.3k22 gold badges3232 silver badges6767 bronze badges
14
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', ...
answered Dec 25 '14 at 15:24
RolandoMySQLDBA
163k2929 gold badges269269 silver badges460460 bronze badges
14
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 ...
13
The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below):
CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
TRANSITION_UUID,
STATUS,
ACTION_SEQ
);
The first query is:
UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED',
CLOSED = ...
answered Feb 6 '13 at 4:57
12
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 ...
12
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 ...
answered Oct 6 '17 at 9:56
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 ...
answered Apr 9 '19 at 13:28
Aaron Bertrand
170k2424 gold badges358358 silver badges566566 bronze badges
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 ...
answered Sep 4 '19 at 13:48
11
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. (...
answered Jan 12 '18 at 0:18
Erwin Brandstetter
128k1616 gold badges297297 silver badges442442 bronze badges
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
...
answered Jul 28 '15 at 17:56
Jack Douglas
35.3k1313 gold badges8989 silver badges169169 bronze badges
10
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 lock clustered index (taken at the delete operator)
X lock clustered index (at the delete operator)
X lock nonclustered index (at the delete operator)
I'm ...
answered May 26 '18 at 4:56
10
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 isolationlevels:
isolationlevel="repeatable read (3)"
This is done at the connection level with this command:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
...
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
deadlock × 520sql-server × 255
mysql × 133
locking × 82
innodb × 63
postgresql × 39
sql-server-2008 × 38
transaction × 31
sql-server-2012 × 28
sql-server-2008-r2 × 25
sql-server-2014 × 23
oracle × 20
concurrency × 19
performance × 17
sql-server-2005 × 17
sql-server-2016 × 16
update × 16
insert × 14
mariadb × 13
blocking × 13
isolation-level × 12
replication × 11
trigger × 11
mysql-5.6 × 11
mysql-5.7 × 11