Stack Exchange Network

Stack Exchange network consists of 174 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.

1
vote
1answer
56 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
86 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
17 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
48 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
35 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
29 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
225 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
51 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
58 views

Deadlock with insert and delete

Deadlock graph: <deadlock> <victim-list> <victimProcess id="process21881f1bc28" /> </victim-list> <process-list> <process id="process21881f1bc28" ...
4
votes
1answer
72 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
55 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
85 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
35 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
56 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
153 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
1answer
28 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
37 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
32 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-...
0
votes
0answers
29 views

Type of locks and conflicts between them in MySQL and PostgreSQL

I'm doing some research of the existing type of locks both on MySQL and PostgreSQL databases. Consider reasonabily recent versions. PostgreSQL current and MySQL 5.7, for example. I came accross these ...
1
vote
1answer
47 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
65 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
61 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
38 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
192 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
30 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
45 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
49 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
211 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 ...
1
vote
1answer
62 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
280 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....
0
votes
1answer
162 views

Deadlocks in Mariadb

We have hit deadlocks in 10.1.25-MariaDB-1~trusty. Looks like it has something to do with select for update queries. But the deadlock increases when number of tps increases. Is there any alternative ...
4
votes
1answer
203 views

Is this surprising deadlock reasonable or a MariaDB bug?

I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a ...
0
votes
1answer
74 views

What blocks a query in Postgres

I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question). I check the "Server status" in pgAdmin which shows the query "Active". I ...
4
votes
1answer
188 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 ...
1
vote
1answer
94 views

Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing ...
4
votes
3answers
237 views

Does MERGE prevent deadlocks and server blocking?

I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while ...
2
votes
2answers
69 views

Deadlocks With Trigger Based Replication

A table ORIGINAL exists with the following structure: ID VARCHAR Length 10 (key) VALUE VARCHAR Length 10 (non-key) A table REPLICATION exists with the following structure: ID ...
0
votes
0answers
59 views

Locking with foreign key references

I have a table table A that is being created as part of a transaction. One of the columns in this table A has a foreign key reference to a column(columns) in table B. Now another transaction is ...
-2
votes
1answer
72 views

RID and Key lock on same object by a Select and Delete query

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row? Also, how did Delete query (SPID 121, on the right) is able to get X ...
0
votes
0answers
34 views

Deadlocks in MYSQL: How to find out why a lock cannot be acquired?

We have the following situation which yields to a deadlock as shown by the SHOW INNODB STATUS result: Transaction1 is trying to update mytable where id = 5 and waits for a row lock record of type X ...
2
votes
3answers
100 views

Encounter deadlock even when there is no relation between tables

I've been studying about deadlock and it's different causes . I found an example regarding this issue but I could not understand the cause of the deadlock exactly because two DIFFERENT tables are ...
1
vote
0answers
33 views

Mysql Deadlock in Stored Procedure,composite unique key

I have a table with composite unique key(uniqueness on combination of 3 columns.) Table structure: CREATE TABLE `userreview` ( `cid` bigint(12) unsigned NOT NULL, `conid` bigint(12) unsigned NOT ...
1
vote
1answer
106 views

Sporadic Gap-lock related Deadlocks under high concurrency in MySQL 5.7

Under high concurrency when running a daily process overnight we see deadlocks occur on a particular table, which seems to reference that it was holding a gap lock at the time. However I'm unable to ...
3
votes
1answer
173 views

How to handle SQL Server Intrasession Parallelism Deadlock?

I am struggling to deal with a intrasession deadlock coming from parallelism on SQL Server 2017. The query is not handwritten but generated by an ORM. Please have a look at the query plan over here....
3
votes
2answers
207 views

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2008?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2008 (compatibility 2005) and the only copy of deadlocks have ...
2
votes
3answers
508 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]: ...
2
votes
1answer
231 views

MySQL dead-locking with UPDATE JOIN and INSERT SELECT statements

We've run into a considerable number of dead-locks recently and they seem to be happening at this query: INSERT INTO link_click (link_id, created_at, weight) SELECT :link_id, :created_at, lw....
0
votes
0answers
20 views

Finding rows affected by deadlock on page locks [duplicate]

We've got a db that has been pretty much deadlock free for over 6 months, but a day or two ago we had over 60, all on the same two or three pages of the same table (~12,000,000 rows, ~250 bytes per ...
6
votes
1answer
122 views

SQL Server 2016 SP2 - ONLINE REBUILD DEADLOCKed Itself - is this a bug?

ALTER INDEX [PK_Order_Line_Inventory] ON [OrderManager_C].[Order_Line_Inventory] REBUILD WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=SELF) ) This was satisfied by 3 ...
1
vote
1answer
147 views

Why is MDB 10.2.14 not handling deadlocks properly when it did prior to upgrade from 10.1.22

Since upgrading MariaDB 10.1.22 to 10.2.14 our MariaDB slaves are encountering deadlocks that are not handled in less than 600 seconds thus the classic semaphore decision to crash the server. The ...