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 ...
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
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 ...
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 ...
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 ...
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 ...
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
128k16 gold badges297 silver badges442 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 ...
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
30.3k2 gold badges32 silver badges67 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', ...
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 = ...
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 ...
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
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. (...
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
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 ...
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
