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
11 votes
2 answers
7k 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....
user avatar
16 votes
2 answers
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 ...
user avatar
8 votes
1 answer
8k 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 ...
user avatar
33 votes
5 answers
33k 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. ...
user avatar
  • 67.2k
4 votes
2 answers
4k 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 ...
user avatar
7 votes
4 answers
8k 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 ...
user avatar
  • 1,251
13 votes
2 answers
10k 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 ...
user avatar
  • 575
58 votes
6 answers
75k 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 ...
user avatar
  • 4,382
3 votes
2 answers
5k 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 ...
user avatar
14 votes
1 answer
14k 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, ...
user avatar
  • 345
9 votes
3 answers
758 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 ...
user avatar
6 votes
2 answers
4k 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 ...
user avatar
  • 976
28 votes
3 answers
37k 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
user avatar
  • 6,562
17 votes
4 answers
14k 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="...
user avatar
  • 2,048
6 votes
1 answer
9k 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 ...
user avatar
4 votes
2 answers
4k 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 ...
user avatar
  • 41
24 votes
4 answers
23k 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 ...
user avatar
  • 425
15 votes
3 answers
23k 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 ...
user avatar
  • 253
9 votes
3 answers
14k 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 ...
user avatar
5 votes
1 answer
8k 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 ...
user avatar
  • 429
12 votes
1 answer
3k 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 ...
user avatar
  • 255
6 votes
1 answer
12k 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 ...
user avatar
  • 467
5 votes
2 answers
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 ...
user avatar
17 votes
1 answer
4k 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 ...
user avatar
4 votes
1 answer
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 ...
user avatar
  • 1,966
11 votes
1 answer
12k 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 ...
user avatar
7 votes
1 answer
6k 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?...
user avatar
  • 127
6 votes
2 answers
7k views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
user avatar
4 votes
3 answers
2k views

Deadlock Graph and Interpretation, solution to avoid

I am supporting vendor based application, which is filled with blocks and deadlocks. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was having 9 SPIDs involved....
user avatar
2 votes
1 answer
160 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 ...
user avatar
2 votes
2 answers
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 ...
user avatar
15 votes
2 answers
5k 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 ...
user avatar
2 votes
3 answers
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 ...
user avatar
  • 1,479
2 votes
1 answer
2k views

Deadlock on MySQL insert statments

Using MySQL v5.7.18 on Windows Server 2008 R2, we are having intermittent deadlock issues during insert queries and are looking for some guidance on how to mitigate them. Below are the inserts that ...
user avatar
1 vote
1 answer
532 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) ) ...
user avatar
7 votes
3 answers
8k views

Why can MySQL handle multiple updates concurrently and PostgreSQL can't?

Let's imagine you have a table with this definition: CREATE TABLE public.positions ( id serial, latitude numeric(18,12), longitude numeric(18,12), updated_at timestamp without time zone ) ...
user avatar
  • 346
10 votes
3 answers
15k 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]: ...
user avatar
15 votes
6 answers
6k 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 ...
user avatar
12 votes
2 answers
11k 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 ...
user avatar
11 votes
1 answer
12k 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) ...
user avatar
  • 394
4 votes
2 answers
3k 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 ...
user avatar
  • 173
12 votes
1 answer
370 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 ...
user avatar
  • 121
10 votes
1 answer
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 ...
user avatar
  • 1,825
7 votes
2 answers
2k 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 ...
user avatar
7 votes
1 answer
9k 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. ...
user avatar
  • 173
7 votes
0 answers
566 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 ...
user avatar
  • 10.4k
2 votes
2 answers
4k views

How to prevent deadlocks in many-to-many insert/update trigger function?

I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point. I have a tweet table that receives thousands of records per second. One of the columns ...
user avatar
  • 281
15 votes
2 answers
3k 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'&...
user avatar
  • 37.5k
14 votes
1 answer
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 ...
user avatar
  • 243
7 votes
2 answers
3k 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="...
user avatar
  • 193