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

30
votes
5answers
22k 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 ...
14
votes
3answers
5k 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
14
votes
1answer
1k views

Can foreign keys cause deadlocks and hinder READ COMITTED 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/timout situations in ...
13
votes
5answers
2k 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. ...
11
votes
2answers
2k 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 ...
9
votes
3answers
11k 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 ...
9
votes
3answers
625 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 ...
8
votes
1answer
193 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
599 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
2answers
424 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 ...
7
votes
3answers
3k views

SQL Server 2008 merge statement deadlocking itself

I have the following procedure (SQL Server 2008r2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set ...
7
votes
2answers
3k 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 ...
7
votes
1answer
2k 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 ...
7
votes
1answer
836 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 ...
7
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
4answers
8k 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, ...
6
votes
1answer
3k 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 ...
6
votes
1answer
1k 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 ...
6
votes
3answers
4k 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
1answer
372 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 ...
6
votes
2answers
3k 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 ...
6
votes
3answers
512 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
445 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
2answers
268 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 ...
6
votes
0answers
178 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
151 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
6k 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
149 views

SQL Server 2000 - 'Performance: Deadlock'

We had to restart our SQL Server today, we had made no changes to it. When it came back up we immediately started getting this error from the server DATE/TIME: 2/27/2014 3:09:31 PM ...
5
votes
1answer
2k views

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction". I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
5
votes
1answer
484 views

Deadlocks in SQL Server

We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX ...
4
votes
3answers
572 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
4
votes
2answers
1k 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 ...
4
votes
3answers
347 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
4
votes
2answers
896 views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
4
votes
2answers
2k 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?
4
votes
2answers
312 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 ...
4
votes
2answers
2k 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 ...
4
votes
2answers
483 views

Lock escalation problem on a trigger

I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day. I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger. The trigger ...
4
votes
2answers
173 views

Deadlock during Slony uninstall and concurrent access of DB

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process. I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
4
votes
2answers
1k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
4
votes
2answers
2k views

How to handle deadlock issue sql server 2008?

I have a simple application.This application update data of a table (B) from another table(A) which is changed every 60 sec. User may read data at any time so creating deadlock. How is this sort of ...
4
votes
1answer
53 views

How can a connection for one DB block a connection to another DB in SQL Server?

We have an interesting scenario. For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's ...
4
votes
1answer
127 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
4
votes
1answer
286 views

After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

I have a table: OrderDetails. For 5 years, only the odd Deadlock has occured. If it happened, it happened on indexes which was on OrderDetails table Now all of a sudden, deadlocks are everywhere - ...
3
votes
3answers
494 views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
3
votes
1answer
918 views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
3
votes
1answer
582 views

Understanding SIX lock in Microsoft SQL-Server

Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of ...
3
votes
1answer
568 views

Deadlocks on same wait resource

I am experiencing a deadlock in SQL Server, but I do not understand why this is a deadlock. It looks like the two resources are actually the same. Meaning that they each have the lock and are asking ...
3
votes
2answers
6k views

How to debug deadlocks in SQL Server 2008 R2?

I'm using SQL Server 2008 R2 that automatically lock database items based on my queries. Sometimes this deadlocks - two threads with different queries never ends. Is there any way to debug how SQL ...
3
votes
1answer
110 views

Deadlock between two databases

I have my code running online shared hosting provider. Recently I am seeing too many deadlock exception is being thrown. As you know that shared hosting uses the same database server to host all the ...