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

5
votes
2answers
276 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 ...
5
votes
1answer
4k views

How I prevent deadlock occurrence in my application?

I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database. All the tables in the database have innodb engine. I also created indexes on each tables. Now I am ...
5
votes
3answers
835 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on the database, that's gives me error. Transaction (Process ...
5
votes
1answer
451 views

Should an INSERT cause an exclusive lock on a foreign key?

I'm working on a deadlock issue. Process A is doing a simple INSERT into TableA which has a FK to TableB Process B is doing a complicated SELECT on a join that includes TableA and TableB I'll ...
5
votes
2answers
870 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
1k 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 ...
5
votes
0answers
88 views

Complete deadlock on PostgreSQL 9.3 after removing a numeric id

The problem - complete DB lock after a schema change Architecture I use a PostgreSQL 9.3 with PostGIS to store location updates. The updates are written to two tables - location_updates and ...
5
votes
1answer
374 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
4
votes
3answers
727 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
1answer
6k 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 ...
4
votes
3answers
638 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
2k 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
1answer
2k views

Side effects of using updlock lock hint?

I was just exploring UPDLOCK hint in SQL-Server 2008 and I found it is very useful in avoiding certain deadlock scenarios. There are always some side effects when using these types of lock hints but I ...
4
votes
2answers
2k 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
1answer
153 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
4
votes
2answers
198 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
1answer
46 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
4
votes
2answers
4k 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
1answer
2k views

InnoDB : Deadlock from one reader and one writer

This sort of has me miffed. One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that ...
4
votes
2answers
4k 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
4k views

SSIS package blocks itself if uses TRUNCATE

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data: Here the Delete ...
4
votes
1answer
311 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
4
votes
1answer
273 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
463 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
547 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 - ...
4
votes
1answer
3k views

How do you read MySQL InnoDB Monitor output?

I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs. The log says that "transaction 1 is rolled back". Why is this the ...
4
votes
0answers
786 views

SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource? The exclusive locks were on the primary key index for ...
3
votes
3answers
2k 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
1k 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
2answers
1k views

SQL Server Deadlock Log Entries

We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222. In the resulting logs we're seeing the following line repeatedly: "Log Viewer could not ...
3
votes
2answers
657 views

Deadlock with simple queries in sql server 2008

I have an application that must delete and insert registries from a table, the table is storing the historic registries for an access control and is stored with the datetime for the check in the ...
3
votes
1answer
2k 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
1answer
113 views

enabling snapshot isolation level on a database - what to monitor?

I am having problems with frequent deadlocks on a subscription database. I am considering enabling the snapshot isolation level for that database. I am aware that there is an overhead on the tempDb ...
3
votes
2answers
684 views

Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related. E.g. Query 1 runs for a few seconds Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
3
votes
1answer
134 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 ...
3
votes
1answer
111 views

Understanding & Resolving Deadlock in SQL Server 2008 R2

I am trying hard to figure out what may be going wrong for below shown deadlock graph: I am receiving alerts for deadlocks similar to above for almost every 10 minutes, it's just the query changes, ...
3
votes
1answer
9k views

Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
3
votes
2answers
2k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
3
votes
2answers
65 views

How to handle lock information?

Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks. There is a high accordance between the tables (and views) affected by the deadlocks and the tables being ...
3
votes
1answer
58 views

sql server deadlock error - Shared (S) and Intent-Exclusive (IX) locks under Serializable isolation

Deadlock errors such as below happen frequently on our 3rd party vendor database: <?xml version="1.0" encoding="utf-8" ?> - <deadlock-list> - <deadlock victim="process743288"> ...
3
votes
1answer
2k views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
3
votes
1answer
3k views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
3
votes
1answer
2k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
3
votes
1answer
739 views

How this simple Stored Procedure ended up in Deadlock

Thanks for all and this forum is really helpful. After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock. We use MySQL 5.5.28 and the Java code ...
3
votes
2answers
5k views

Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table). Now, we insert values into these 2 tables using the below 2 queries: INSERT ...
3
votes
1answer
481 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
3
votes
1answer
82 views

Merge causing deadlocks

We are running MS SQL Server 2008 R2. We've got a VendorMaster table (with about 8700 rows) that we are trying to keep in sync across two databases using a Merge statement. The merge code runs every ...
3
votes
1answer
134 views

Can a Table Lock eliminate a deadlock or make it worse Oracle

We have a situation in which an Oracle Database is having deadlocks issues. I said that perhaps we should lock into some table locks, and a guys response to me was "Unless I am misunderstanding ...
3
votes
1answer
840 views

View last several innodb deadlocks

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock ...
3
votes
2answers
1k views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...