31
There would not be a problem if the table variable only ever held one value. With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company.
Process A reads the destination, finds no row, and inserts the value '1'. It holds an exclusive ...
answered Sep 3 '12 at 13:48
25
The data you need is recorded in the default extended events trace.
DECLARE @xml XML
SELECT @xml = target_data
FROM sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
SELECT
XEventData.XEvent.query('(data/value/deadlock)[...
25
The full message that is commonly seen:
Transaction (Process ID 53) was deadlocked on lock | communication
buffer resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.
This lock type is commonly seen with deadlock queries that SQL Server has executed as parallel, sometimes referred to as "intra-query ...
20
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
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" ...
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
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 ...
16
If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation ...
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
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 has been reported no less than four times. This one was closed as fixed:
http://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 was closed as by design / won't fix:
http://connect.microsoft.com/SQLServer/...
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
108k11 gold badges230 silver badges358 bronze badges
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
14
On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:
a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index
The SELECT accesses the nonclustered index first, then the clustered index.
The ...
answered Nov 29 '12 at 14:22
14
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
14
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(...
13
In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby ...
13
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 ...
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
13
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', ...
13
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 ...
12
OK, after looking everything over a couple of times, I think that your basic assumption was correct. What's probably going on here is that:
The MATCH part of the MERGE checks the index for matches, read-locking those rows/pages as it goes.
When it has a row without a match, it will try to insert the new Index Row first so it will request a row/page write-...
12
It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password.
I was able to reproduce this as follows
CREATE TABLE T(X varchar(1000))
Connection 1
BEGIN TRAN
INSERT INTO T VALUES('password1')
WAITFOR DELAY '00:01:00'
SELECT * ...
12
The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:
CREATE TABLE dbo.LockTest
(
col1 ...
answered Nov 30 '12 at 2:16
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 ...
answered Sep 4 at 13:48
11
You are dealing with a deadlock, not a performance bottleneck issue.
If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.
Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in ...
11
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
Only top voted, non community-wiki answers of a minimum length are eligible
