A situation caused by two or more processes being unable to proceed (and thus release their locks) because they are blocked by locks on resources held by the other process.

learn more… | top users | synonyms

48
votes
6answers
44k views

What are the main causes of deadlocks and can they be prevented?

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was ...
25
votes
5answers
12k views

Handling concurrent access to a key table without deadlocks in SQL Server

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables. Each row in the table stores the last used ID LastID for the field named in IDName. ...
20
votes
3answers
17k views

Code to simulate deadlock

I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible). Thank you. ADDED: Reproducing deadlocks involving only one table
17
votes
1answer
2k views

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in ...
15
votes
2answers
6k views

Deadlocks From Locks on Same Temporary Tables In Different Processes

I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock: 1. process8cf948 SPID 63 Performing an ALTER TABLE on temporary ...
14
votes
2answers
454 views

Simultaneous calls to the same function: how are deadlocks occurring?

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'&...
12
votes
1answer
350 views

SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert

I have been working on this deadlock issue for quite a few days now and no matter what I do, it persists in one way or another. First, the general premise: We have Visits with VisitItems in a one to ...
11
votes
3answers
8k views

Merge statement deadlocking itself

I have the following procedure (SQL Server 2008 R2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set ...
11
votes
1answer
6k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
11
votes
1answer
832 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
10
votes
3answers
17k views

Notification on long-running query or deadlock in SQL Server 2008 R2?

I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on ...
10
votes
4answers
11k views

How to monitor deadlocks

When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, ...
10
votes
2answers
501 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
10
votes
2answers
4k views

Will these two queries result in a deadlock if executed in sequence?

This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or verified....
10
votes
2answers
926 views

SQL Server Index Update Deadlock

I have 2 queries which when run at the same time are causing a deadlock. Query 1 - update a column which is included in an index (index1): update table1 set column1 = value1 where id = @Id Takes X-...
10
votes
2answers
5k views

Trouble deciphering a deadlock in an innodb status log

We are accessing MySQL from the Microsoft ADO.NET connector. Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
10
votes
2answers
739 views

How does SQL Server determine the order it takes locks in while selecting a table?

I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc ...
9
votes
2answers
6k views

Why does this query cause a deadlock?

Why does this query cause a deadlock? UPDATE TOP(1) system_Queue SET [StatusID] = 2, @ID = InternalID WHERE InternalID IN ( SELECT TOP 1 InternalID FROM system_Queue WHERE ...
9
votes
3answers
684 views

Need Help Troubleshooting Sql Server 2005 Deadlock Scenario

I am running into a deadlock scenario where the only participants in the deadlock appear to be a single table and a single stored procedure that deletes from that table. I drew that conclusion based ...
9
votes
1answer
4k views

SQL Server Deadlock Graph - Table, Page or Row Lock?

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
9
votes
1answer
2k views

Why are there Victimless Entries on the Deadlock Graph?

I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node. I don't understand what these entries represent: if there ...
8
votes
1answer
1k views

How are deadlocks detected and reported in an RDBMS?

I was given this essay type question during an interview but did not get the job. The full question was as follows: How are deadlocks detected and reported in an RDBMS? What are the transaction ...
8
votes
1answer
4k views

Deadlock on Delete Statement

I get a deadlock when a SQL Server Job runs. The deadlock occurs on a simple DELETE statement. I would have thought there would have to be a SELECT/UPDATE query running to cause the deadlock? But ...
8
votes
1answer
295 views

What does “*password------------” mean in a profile deadlock report?

In SQL Server 2008 R2, I got several deadlock reports that have "*password------------" in the input buffer. It looks like an attack but in that case I don't know the reason or the kind of attack. (...
8
votes
1answer
172 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
8
votes
2answers
600 views

SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table. I have already changed all the required parameters and made them into row only locking. I've also changed the ...
8
votes
1answer
815 views

Trace Flag 1222 Not Working?

I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C". On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both servers:...
7
votes
4answers
2k views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
7
votes
2answers
13k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
7
votes
1answer
935 views

Meaning of 'locks rec but not gap waiting' in deadlock report

About the meaning of locks rec but not gap waitingin TRANSACTION(1), which one is correct? Already granted gap lock, waiting for clustered index X lock? Already granted clustered index X lock, ...
7
votes
1answer
6k views

SQL Server Deadlock on two updates due to index lock order

I have two UPDATEs - one locks the CI first and then the NCI (on status) because the status column is also being updated. The other already owns a U lock on the NCI because it knows it is changing ...
7
votes
1answer
907 views

Deadlock graph with a lock on an index on a seemingly unrelated table

I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE. This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a ...
7
votes
1answer
208 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
7
votes
3answers
685 views

Inserting and updating and selecting at thousands of times per second

I have a table that gets inserted, updated and selected from thousands of times within one second. I am having deadlock issues though. The database has 2-5 simultaneous 1000+ row inserts using Linq ...
6
votes
1answer
676 views

Simulate deadlock with more than 2 processes

How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 ...
6
votes
4answers
2k views

Are deadlocks a serious problem? [closed]

I have a few production SQL Server instances and I get around 100 deadlock alert emails a day (up to 100 for one server, 150 in total). Is this a problem I should investigate? How many deadlocks are ...
6
votes
2answers
4k views

SELECT/INSERT Deadlock

This instance hosts the SharePoint 2007 databases (SP). We have been experiencing numerous SELECT/INSERT deadlocks against one heavily utilized table within SP content database. I have narrowed down ...
6
votes
1answer
2k views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
6
votes
3answers
10k views

How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock?

I have the following table in my SQL Server 2008 database: CREATE TABLE [dbo].[SomeTable] ( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Column1] [varchar](30) NOT NULL, [Column2] [varchar](30) ...
6
votes
2answers
369 views

Insert only deadlocks

We have an application inserting data into a table. Unfortunately, we are getting deadlocks and the deadlocks are coming from inserts only. We are seeing the inserts take key locks in a different ...
6
votes
2answers
2k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
6
votes
1answer
1k views

Deadlock on Merge replication

I'm having a really confusing problem on the msmerge_contents table on some of my merge replication databases. It seems I'm receiving a dead lock on the exact same row on the same index on the table ...
6
votes
0answers
407 views

Is this a race condition or bug in sql server using serializable TIL with updlock? [closed]

I've been staring at this problem for almost a week now. I'm getting a deadlock in some query, and I've reduced the problem to these reproducible steps: Create a database with: create table offering ...
5
votes
2answers
552 views

How do I query only for committed data while avoid a deadlock

I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables At the same time there are ...
5
votes
2answers
3k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
5
votes
1answer
723 views

Deadlock due to concurrent updates (with SIU lock)

We have 2 different wcf services which get invoked by a windows service. The methods in those wcf services are updating the same table inside different stored procedures. One thing is for sure: ...
5
votes
2answers
406 views

Given two known statements, how can I replicate deadlock?

I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
5
votes
2answers
10k views

can I resolve a deadlock with the rowlock hint?

I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything. It looks like the part of the stored proc that hit a deadlock was ...
5
votes
2answers
5k views

Determine Reason for Deadlock in MySQL

We've been seeing some deadlocks occur but have not been able to reproduce. Is there any way to determine, after the fact, what queries were running, etc to figure out why deadlock occurred?
5
votes
1answer
682 views

Why are U locks required when using Read Committed Snapshot Isolation

I consider myself a beginner at Sql Server locking. My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From http://...