Hot answers tagged

34

tracking deadlocks is the easier of the two: By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605. Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605) Turn it off: DBCC TRACEOFF(-1, 1204, 3605) See "Troubleshooting ...


23

The best way would be to use tables you already have. Create two tables -- table-a, table-b For a test you can even update the same column with the same information so you don't affect any real data. For instance UPDATE table_a set ID = ID where ID = 100; Open two sessions to the same database. On one, run BEGIN TRAN update table_a set ID=ID where ID = ...


19

my favorite articles to read and learn about deadlocks are: Simple Talk - Track down deadlocks and SQL Server Central - Using Profiler to resolve deadlocks. They will give you samples and advices about how to handle suck a situation. In short, to solve a current problem, I'd make the transactions involved shorter, take out the unneeded part out of them, ...


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

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)[...


15

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 ...


14

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 ...


14

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.


13

Sometimes a deadlock can be solved by adding indexing, as it allows the database to lock individual records rather than the whole table, so you reduce contention and the possibility of things getting jammed up. For example, in InnoDB : If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every ...


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

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 ...


12

It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table. The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess ...


12

In the deadlock graph XML you will see something like: <deadlock-list> <deadlock victim="..."> <process-list> <process id="..." ... waitresource="X:..." ... The X is the interesting bit, possible values you are interested in are: RID for row id (row level locking) PAG for page level lock OBJECT (which may be further ...


12

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 ...


12

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(...


11

No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database. The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to ...


11

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 * ...


11

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 avoid deadlocks altogether. I have no deadlocks ...


11

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 ...


11

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-record-level-locks.html The exclusive lock request is coming from the "for update" clause ...


10

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 ...


10

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 = ...


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 ...


9

You can configure alerts for both of these with SQL Agent. Create a new alert and select type "SQL Server performance condition alert" For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go. For deadlocks, the ...


9

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 ...


8

Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event. How To Track Down Deadlocks in SQL Server using Profiler Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.


8

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-...


8

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 ...


8

I can't answer as to why there is information missing, but I can point out how to extract it. First, move to that database: SELECT DB_NAME(25); Take that result and: USE <db_name>; Now find out what these hobt_ids represent: SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id IN (72057594099728384, ...


8

Use of the XLOCK hint on either your SELECT approach or the following UPDATE should be immune to this type of deadlock: DECLARE @Output TABLE ([NewId] INT); SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; UPDATE dbo.tblIDs WITH (XLOCK) SET LastID = LastID + 1 OUTPUT INSERTED.[LastId] INTO @Output WHERE IDName = @IDName; ...



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