Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
44 views

One SPID has deadlocked itself - how? [duplicate]

We had some Deadlock alerts raised today - on further investigation I have found that it seems to all be the same SPID and related to parallelism events. How has this happened? I didn't think ...
0
votes
1answer
29 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) ) ...
2
votes
1answer
76 views

Mysql/innodb deadlock on simple delete query

Mysql/innodb 8.0.16, read committed transactions, several statements in transaction raise deadlock on deletion of non intercepting rows. Trying to understand what is happening: -----------------------...
12
votes
1answer
142 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 ...
0
votes
0answers
43 views

Writer vs writer deadlock and solution

Earlier we used to have reader vs writer and writer vs reader deadlock, which is resolved by setting isolation level at application configuration level from "Repeatable Read" to "Read Committed" which ...
9
votes
1answer
195 views

Apparently, my CLR assembly function is causing deadlocks?

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
0
votes
0answers
25 views

Getting Database is locked error [duplicate]

I have a query in which I am updating a table and have used IN keyword in the query. The query is something like this: UPDATE q SET b_id = 123 WHERE id IN (SELECT id FROM q WHERE b_id IS NULL ...
1
vote
0answers
58 views

Deadlock on two update statements on the same page

I'm receiving multiple deadlocks in one SQL Server. The deadlocks occur when the same query with different parameter values is being issued on the same database: Example: Query 1: UPDATE Tabel1 ...
4
votes
1answer
66 views

How to avoid deadlock for this query?

I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads. I am trying to figure out how to avoid this deadlock, but so far I haven't been ...
0
votes
0answers
30 views

MySQL InnoDB: how to interpret this InnoDB monitor output of Next-Key locks?

Next-Key Locks from MySQL documentation A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. InnoDB performs row-level ...
4
votes
3answers
1k 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. ...
0
votes
1answer
64 views

Are deadlocks always a symptom of design flaws or are they an unavoidable occurrence even in well designed systems with high data writes/reads? [closed]

A complete redesign proposal is experiencing deadlocks in its prototype. I am of the opinion that deadlocks are always the symptom of design flaws. I do not want to go into the specifics of our system;...
1
vote
2answers
152 views

Is this deadlock related to our database?

our application is running in a SQL enterprise cluster hosted at a client. We don't have administrative access. They are saying that our database is generating deadlocks, and sent us a huge trace file....
0
votes
0answers
40 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
1
vote
2answers
138 views

Postgresql Deadlock - what does “while rechecking updated tuple” mean?

Question: Can someone please decipher while rechecking updated tuple for me in the following: 2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:ERROR: deadlock detected 2019-02-20 10:34:...
1
vote
1answer
51 views

Deadlock Explanation

I've inherited a SQL Server 2012 database that uses triggers to copy all changes made to dbo tables into an audit schema, providing functionality similar to system-versioned temporal tables. One of ...
1
vote
2answers
69 views

Overly eager explicit row locking causes deadlock

I'm running into deadlock problems on MariaDB using the INNODB engine. In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
1
vote
1answer
88 views

Sql server deadlock when insert and update in one transaction

I'm using Azure SQL + Java + Spring Boot 2. Currently I'm trying to understand the reason why deadlocks occur. In transaction I'm doing insert and later update on the same table by different rows. ...
2
votes
3answers
243 views

Transaction deadlock during restore

I am working on PS script to restore database backups and I have following SQL script that I am running from PS Invoke-SQLcmd command: USE master GO ALTER DATABASE mydatabase SET SINGLE_USER WITH ...
0
votes
1answer
37 views

Oracle 10g Database Locking Session Issue

I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user ...
1
vote
1answer
76 views

Issue with a “weird” deadlock in SQL server

I'm working with a database that has the characteristics that follow: GO /****** Object: Table [dbo].[ADS_EVENT_TYPE_STR] Script Date: 1/17/2019 8:20:36 PM ******/ SET ANSI_NULLS ON GO SET ...
3
votes
1answer
109 views

Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is ...
0
votes
0answers
20 views

Deciphering InnoDB status logs

I noticed MySQL deadlock errors in my application logs and for investigation, I queried InnoDB status and got the below mentioned Latest detected deadlock *** (1) TRANSACTION: TRANSACTION ...
0
votes
2answers
58 views

Delete queries blocks other concurrent transactions

DELETE queries in my production SQL server are causing deadlocks. I know that DELETE is causing this because i checked the Extended Events and checked the deadlock XML and found out that this DELETE ...
0
votes
1answer
94 views

MySQL timeout on INSERT lasts almost 60 seconds

I have this table: CREATE TABLE `ClusterDescription` ( `ClusterDescriptionId` int(11) NOT NULL AUTO_INCREMENT, `ClusterId` int(11) NOT NULL, `RightBarDateTime` datetime NOT NULL, `...
0
votes
0answers
48 views

Read Deadlock XML - Intra-query Parallelism

I encountered several deadlocks in my application over the last 2 months and i checked the Extended Events to find the details. I tried to understand the dealock XML and i came across this great ...
4
votes
2answers
286 views

Serializable range deadlocks

Help required on an inherited deadlock problem! The code given below appears to have been written some time back, in an attempt to address contention issues around the assigning of new ID values on ...
2
votes
2answers
109 views

Query to return all Oracle Deadlocks in 12c

Is there an object I can query in 12c to return all ORA-60 Deadlock alerts? I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on. ...
2
votes
1answer
255 views

Deadlock with insert and delete

Deadlock graph: <deadlock> <victim-list> <victimProcess id="process21881f1bc28" /> </victim-list> <process-list> <process id="process21881f1bc28" ...
4
votes
1answer
134 views

Eliminating a intra query deadlock, can I avoid restricting parallelism?

I have an update statement that is generating intra query parallelism deadlock. Based on this link I believe my two options to resolve this issue is to force SQL Server to avoid parallelism via an ...
1
vote
2answers
107 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...
2
votes
2answers
266 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 ...
1
vote
0answers
47 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
0
votes
1answer
271 views

Best lock strategy to prevent deadlock on concurrent updates

Yesterday I stumbled across a deadlock caused by two apps concurrently updating the same table. The operation would have been harmless in this instance if it weren't for the deadlock. The pattern is ...
1
vote
1answer
254 views

Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?

I have a SQL code as below #table creation CREATE TABLE TAB ([i_task_id] [int] IDENTITY(1,1) NOT NULL, [i_priority] [tinyint] NULL, [i_status] [smallint] NULL CONSTRAINT [PK_TAB] PRIMARY KEY ...
1
vote
2answers
41 views

Receiving several deadlocks with simple queries when ran concurrently from different nodes

We have a web application with three nodes that sit behind a load balancer that handles traffic in a round robin fashion (no sticky sessions). When we receive several requests coming in at the same ...
1
vote
1answer
122 views

mysql deadlock when update some items whose id are selected from another table

I'm sorry, I just can speak a little English. I have two machines(10.10.102.101, 10.10.102.104) which connect to the same mysql datebase. This is a table and deadlock happened on it. mysql> ...
2
votes
1answer
101 views

Waitresource format explanation in deadlock xml

I have a deadlock xml where two transactions are deadlocked on two resources. waitresource attributes of the process elements have values "OBJECT: 8:1269579561:4" and "OBJECT: 8:1269579561:0". It ...
1
vote
2answers
36 views

Update same field from multiple transaction

I have a service which makes some manipulations in DB, everything is working normally until I tried to make two requests in parallel. Below see the sql debug log: Executing (2c4cb493-7090-4a83-942a-...
1
vote
1answer
81 views

Deadlock - Due to update causing Index page movement within an index

I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also. Now I ...
2
votes
1answer
115 views

Troubleshooting Deadlock, Victim holding on to shared lock

I am trying to troubleshoot the below deadlock situation Looking at the deadlock xml, both session are using read committed isolation level. Question is, in read committed isolation level, in this ...
0
votes
2answers
63 views

Deadlock when delete using OR operator

I got a deadlock when having more than one sessions doing the following: start transaction insert data to table A delete data from table A commit at the same time They may delete the same records ...
3
votes
0answers
66 views

Why do two transactions making a read lock result in a DEADLOCK exception?

Let's assume a scenario that flows as follows: Transaction #1 makes this query: SELECT v.id, v.name FROM versions_history as vh INNER JOIN versions as v ON vh.version_id = v.id WHERE vh.project_id = ...
3
votes
1answer
205 views

Is it acceptable to use deadlocks as a strategy?

We recently upgraded our SQL production environment from 2008R2 to 2017. I am finally at a point where I want to try optimistic locking in the form of using snapshot isolation. I read Brent Ozar's ...
0
votes
0answers
41 views

Deadlock when dropping multiple constraints on multiple tables at the same time

Background We have a data warehouse process that calls the following code: exec sp_executesql N'EXEC maintenance.sp_fk_statements @P1,@P2,@P3', N'@P1 varchar(3), @P2 varchar(9), @P3 ...
1
vote
0answers
71 views

What is meant by Orphan Lock and Orphan Sessions?

I have came across this term Orphan locks and Orphan sessions and I really have no idea what that is. I have tried to search it on Google and I still haven't got any useful info/results regarding what ...
1
vote
0answers
122 views

Transactional Replication Deadlocks

I set up transactional replication between two servers on various tables. I noticed that there were a few deadlocks around the time of the snapshot generation(which it appears is expected). What I ...
-2
votes
2answers
400 views

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

I'm running a 15GB database on SQL Server 12.0.5207. Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs. Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would ...
2
votes
1answer
121 views

SQL Query logging in extended events deadlock xml report is too short

How can I tell the sql server to log the whole query in the xml, and not truncate it? example: <inputbuf> (@0 nvarchar(20),@1 nvarchar(10),@2 decimal(38,20),@3 int,@4 datetime,@5 nvarchar(...
2
votes
1answer
538 views

MySQL 5.6.34 RDS Warning: a long semaphore wait causes crash — log included

I had my RDS MySQL 5.6.34 server crash under normal load with nothing out of the ordinary today (That I could find). Amazon support pointed me the the error log. MySQL config: https://gist.github....