Questions tagged [deadlock]

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.

Filter by
Sorted by
Tagged with
12
votes
2answers
6k 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....
16
votes
2answers
7k 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 ...
8
votes
1answer
7k 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 ...
32
votes
5answers
29k 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. ...
4
votes
2answers
3k views

Can I get notification when event occur?

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide ...
7
votes
4answers
7k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
13
votes
2answers
8k 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 ...
55
votes
6answers
67k 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 ...
3
votes
2answers
4k 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 ...
9
votes
3answers
720 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 ...
6
votes
2answers
3k 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 ...
26
votes
3answers
31k 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
15
votes
4answers
9k 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" ...
10
votes
1answer
9k views

MySQL InnoDB Deadlock For 2 simple insert queries

I have a deadlock for this two insert queries: insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, ...
6
votes
1answer
8k 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 ...
22
votes
4answers
19k 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 ...
15
votes
3answers
22k 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 ...
6
votes
1answer
10k 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 ...
5
votes
2answers
5k 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 ...
16
votes
1answer
3k 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 ...
7
votes
1answer
5k 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 happen?...
4
votes
3answers
2k views

Deadlock Graph and Interpretation, solution to avoid

I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs. ...
4
votes
1answer
2k views

Read system_health event_file instead of ring_buffer for deadlocks extended events

I'm currently using this disaster to locate recent deadlocks by reading the system_health Extended Events ring buffer. select top 2000000000 XEvent.value('@timestamp', 'datetime2(3)') as ...
3
votes
2answers
3k 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 ...
2
votes
1answer
139 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is the ...
2
votes
2answers
6k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
10
votes
2answers
2k views

If a Parallelism Exchange Event deadlock is victim-less, is it a problem?

We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured ...
9
votes
1answer
8k 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 ...
2
votes
3answers
2k views

How long can Deadlock info hold in innodb status page?

When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp ------------------------ LATEST DETECTED DEADLOCK ------------------------ 121118 17:14:31 Questions Any idea how long Deadlock ...
1
vote
1answer
138 views

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows: CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY (id) ) ...
7
votes
3answers
6k views

Why does this query result in deadlock?

I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern: SQLSTATE[40001]: ...
10
votes
6answers
4k views

Best situation to use READ UNCOMMITTED isolation level

As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons ...
4
votes
2answers
6k 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 ...
12
votes
1answer
210 views

SQL Extended Events Session for deadlock detection

Is there a way to increase the size of the <inputbuf> element in the deadlock XML captured by the deadlock extended events session? We want to see the complete query to help pinpoint the issue ...
10
votes
2answers
8k 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 ...
8
votes
2answers
5k views

MySql Gap Lock Deadlock on Inserts

I'm getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes. START TRANSACTION UPDATE vehicle_image SET active = 0 ...
3
votes
2answers
2k views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
9
votes
1answer
5k 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 ...
7
votes
1answer
8k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
7
votes
0answers
555 views

Complete deadlock on PostgreSQL 9.3 after removing a numeric id [closed]

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 ...
7
votes
2answers
1k 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 ...
15
votes
2answers
2k 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'&...
11
votes
1answer
3k 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 ...
7
votes
2answers
2k views

Help understanding why a deadlock occurred on row level index lock

I have the following deadlock xml <deadlock> <victim-list> <victimProcess id="process3340d548c8" /> </victim-list> <process-list> <process id="...
5
votes
3answers
1k 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 ...
5
votes
1answer
4k views

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

Setup I have a bulk insert function set_interactions(arg_rows text) that looks like this: with inserts as ( insert into interaction ( thing_id, associate_id, created_time) ...
4
votes
1answer
614 views

Solving intra parallel query deadlocks

We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes. Have you ...
4
votes
2answers
856 views

Managing and Removing data from huge table

I have the following scenario: A huge table, that is also the most use table by far in the system. By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte ...
4
votes
3answers
982 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 ...
3
votes
1answer
4k views

TRUNCATE in transaction: dead lock

In a unittest we run this: BEGIN; TRUNCATE table1; TRUNCATE table2; ... UPDATE ...; ROLLBACK; Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and ...