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
-1
votes
0answers
18 views

Prevent Deadlock Errors with Trigger on high concurrent write table [closed]

I have a table that is getting around 1000+ inserts per minute. There is a trigger on it to update a column on another table. CREATE or replace FUNCTION clothing_price_update() RETURNS trigger AS $...
1
vote
1answer
28 views

How are deadlocks detected by Synapse

I'm trying to produce a deadlock in Azure Synapse. I would have expected it to work much as on-prem SQL Server does given the documentation says it "applies to" Synapse. I've waited an ...
6
votes
1answer
175 views

Self-deadlocked process on a table variable

We are running SQL Server 2019 CU12 for one of our customers. Some time ago we started getting a wired deadlocks whereby a single process deadlocks itself on access to a table variable. Example ...
0
votes
0answers
56 views

Why deadlocks occurs on two queries that access same table, but different data?

I have a multiple deadlocks when running multiple backups in parallel. I tried to read the graph but I have not so much experience with this part. What I found in the deadlock graph was that deadlock ...
0
votes
1answer
59 views

postgresql deadlocks: SELECT blocked and blocking query

Based on the 2nd answer on https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql I performed the following snippet: Transaction 1 Transaction 2 BEGIN; ...
1
vote
2answers
129 views

SQL Server - Deadlock during two concurrent DELETE

I've got a Java app exposing a REST API to delete records in a table (SQL Server), sometimes the API must handle concurrent deletion requests (each request uses a different DB session) Here is an ...
10
votes
1answer
181 views

Could I have an undetected deadlock?

Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected: Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking ...
-1
votes
2answers
53 views

What does the process shown in the deadlock graph represent?

I have a windows application that is used by multiple users, often there are deadlocks in the database related to lock resources. Looking at the deadlock graph I see 2 processes and 2 resources, ...
0
votes
1answer
51 views

How to capture the exact SQL text involved in deadlock (locks) using extended events?

I have setup extended event session with the following event: xml_deadlock_report When I look at the deadlock graph, it shows me the processes asking or requesting for the resource, and the type of ...
3
votes
2answers
591 views

Can indexing be a solution to SQL deadlocks that are caused due to lock resources?

This question pertains purely to deadlock caused due to lock resources. I am reading this article: Using a Clustered Index to Solve a SQL Server Deadlock Issue They have explained how adding ...
0
votes
1answer
38 views

Do trace flags 1204 and 1222 give any more information than available via the xml_deadlock_report event in Extended Events?

I am exploring deadlock detection approaches. Trace flags 1204 and 1222 Extended events (xml_deadlock_report) Do the trace flags contain any more data than what is available via the Extended Events ...
2
votes
1answer
28 views

Are foreign key share locks taken out atomically?

I'm trying to understand how a deadlock happened in our database and I'm wondering if the culprit could have been an update that did not run in a transaction. If I have two tables CREATE TABLE user( ...
0
votes
0answers
37 views

Deadlock due to serializable isolation level(from my knowledge)

Deadlock occurring on update. Both the updates holding RangeS-S lock and waiting for X lock. Is there any other possible way without changing the isolation level or using the retry logic? Database ...
3
votes
1answer
158 views

Deadlock on Report server database

Getting number of deadlocks from ReportServer.dbo.ExpireExecutionLogEntries storedproc. I spent my whole day on watching tutorials on deadlock but could not figure out this. <deadlock> <...
1
vote
2answers
122 views

How to lock table in SQL Server to prevent other transactions from INSERT and avoid deadlocking?

I'm writing a database trigger on Table A. In said trigger, I insert a new record into Table B by doing: Look up the PK ID of Table B Increment the PK ID and insert new row into Table B (Note: Table ...
3
votes
1answer
441 views

Cannot run query to get deadlock graph in a timely fashion

I am trying to get deadlock information from a sql-server by using this query select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph FROM (select CAST(target_data as xml) ...
0
votes
3answers
101 views

How to avoid deadlocks when generating consecutive ticket numbers?

I have implemented a ticketing system where when you join an event and placed an order depending on the quantity, a n of entries will be generated. Example: you place an order with 10 quantity, a 10 ...
0
votes
0answers
172 views

InnoDB deadlock with concurrent SELECT .. FOR UPDATE queries

I'm currently having deadlocks during high load scenarios. These deadlocks occur even without any update, insert or delete statements Also changing isolation level to read committed does not help ...
0
votes
1answer
40 views

A strange deadlock: it seems that a delete locks multiple secondary indexes, a bug of MySQL?

Recently I got an unexpected deadlock with the below 3 delete (MySQL 5.7.12, isolation level: Repeatable Read) Session 1 begin; delete from fruit where age = 99; Session 2 begin; delete from fruit ...
0
votes
0answers
25 views

Page level Deadlock on same delete query

In SQL Server I am issuing the following query: DELETE FROM TableA WHERE tableB_id IN (SELECT ID from TableB WHERE FileID = @P0) The query is being called in parallel by multiple processes and which ...
0
votes
1answer
73 views

What would cause this SQL Server deadlock?

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the ...
3
votes
1answer
215 views

How to avoid IX deadlock in check constraint

I have following situation, I have a table with a primary key, and a constraint that I cannot have rows with specific requirements. For demonstration purposes, here I have constraint that doesn't ...
0
votes
0answers
7 views

Locking on a strict 2pl

For this question Im really unclear on what happens on step 6. Because at step 5 both T1 and T2 share a S lock on A. And T1 has requested for an X(exclusive) lock and is in waiting because T2 has an S ...
0
votes
0answers
62 views

Help on understanding deadlock

A deadlock issue is encountered when adding column to an existing table, from my attempt to understand the graph, a linked server calling sp_columns_100_rowset is having an update lock on sysschobjs, ...
2
votes
0answers
99 views

Unhandled Deadlocks with SQL Server 2017 [closed]

We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before? Detailed explanation: We migrated our database from ...
0
votes
1answer
117 views

MySQL Dead Lock on Delete Issue

I have recently updated my production code with the new query that is supposed to clean up orphaned records. It runs within the transaction as the last step. And, as a result, I am seeing occasional ...
2
votes
2answers
72 views

Key lock triggered by concurrent batch update

I'm doing a batch update operation,but it appears key lock I think I'm in trouble. I don't understand why the deadlock is happening. Environment: SQL Server 2016 Work mode:concurrent batch update ...
0
votes
0answers
48 views

MySQL keeps ignoring MAX_EXECUTION_TIME

I have an instance running which have mysql8 and a nodejs application server. This query keeps popping up and freezing. SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM my_table; Even though this ...
1
vote
1answer
79 views

Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations

I have below two queries on Production: Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column There are ~20 tables that point to &...
0
votes
1answer
43 views

Concurrent Update On Primary Key Column Making Deadlock

I have simple table T which have Col1, Col2,Col3 and Col4. columns. Col1 is primary key. Now I am running some concurrent code, Parallel.For(0, list.Count, new ParallelOptions { MaxDegreeOfParallelism ...
1
vote
1answer
48 views

Deadlock Detection | Should the node T4 point to T1 and T2? [closed]

This example regarding deadlock detection was shown during one of my lectures and I have a doubt about it. The image is shown below. For deadlock detection mechanism, why does T4 point only to T2 and ...
1
vote
1answer
296 views

Is "deadlock detected" really an error? Should I be suppressing them after handling them?

A while ago, I spent a nightmarish number of weeks dealing with "deadlock detected" and trying to figure out how to handle it. I ended up finally handling it in such a manner that my code is ...
1
vote
1answer
207 views

Can UPDATE lead to deadlocks in SQL SERVER?

Can we experience deadlock if run the same update query concurrently? Let's assume there's a table with millions of records and we need to update several thousand records at one shot. UPDATE TABLEX ...
0
votes
0answers
16 views

does postgres deadlock if query update and insert in a table inside a function begin? plpgsql

does postgres deadlock if query update and insert in a table inside a function begin? plpgsql function Declare select same table begin update multiple rows same table insert row same table
0
votes
1answer
51 views

How to UPDATE data without locking every record the search encounters?

We have the following database specifications: version: 10.1.47-MariaDB-0ubuntu0.18.04.1 innodb_version: 5.6.49-89.0 tx_isolation: READ-COMMITTED innodb_strict_mode: ON sql_mode: NO_AUTO_CREATE_USER,...
0
votes
0answers
85 views

SQL Server : Deadlock is happening even with small batch size

We have a stored procedure which purges old data. We do the purging on daily basis during off-business hours. Also we do purging in small batches. But in the 5 hour window of purging, we see around 2 ...
1
vote
0answers
36 views

Difference between setting of a shared lock when using INSERT or UPDATE/DELETE statements

I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement. Specifications: version: 10.1.47-...
3
votes
1answer
130 views

deadlock with repeatable read isolation level

Trying to understand deadlock that is occurring with repeatable read isolation level when the stored proc is invoked. This is the deadlock xml : <TextData> <deadlock-list> <...
0
votes
0answers
32 views

Need sample DELETE Query where SQL Server by default takes PAGELOCK instead of ROWLOCK [duplicate]

I need a sample DELETE Query where SQL Server by default takes an X lock at Page Level and not at Row Level. BACKGROUND: In our purging SP, we are using DELETE Query with ROWLOCK hint and I want to ...
0
votes
1answer
87 views

Why does SQL Server use exclusive locks on the entire table to update one record?

We have problems with deadlocks, judging by the logs, two requests simultaneously want to get exclusive access to the table. However, both queries change one record in the table. The error occurs at ...
1
vote
1answer
92 views

intra-query parallel thread deadlocks with only Page Locks with snapshot isolation and change tracking

I've been trying to optimise a data loading process. This is on Sqlserver 2016 (13.0.5850.14 - latest SP&CU). I've switched to change tracking for internal database changes and set tablocks on all ...
1
vote
1answer
62 views

Purging SP getting deadlocked with App...Not able to completed understand Deadlock Graph

We have a stored procedure which purges old data. On a regular basis, it is getting deadlocked with an application stored procedure. Below is the deadlock graph: Deadlock XML: <deadlock> <...
2
votes
2answers
228 views

Possible for two simple updates on the same table to deadlock?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...
2
votes
1answer
170 views

Postgres 9.5 deadlock with many updates on a table + many "select for update" on another table

I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key. I face a deadlock problem while executing many of these ...
8
votes
2answers
1k views

Safe to use WITH (NOLOCK) in this case?

Scenario I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an ...
0
votes
2answers
83 views

Catch deadlock events in a background job?

I'm currently catching deadlock events with the profiler. (deadlock, deadlock graph, deadlock chain). So far so good, but for this, a client computer needs to be constantly running and online. ==> ...
1
vote
0answers
56 views

Deadlock in MYSQL during SELECT and TRUNCATE

We are regularly getting a deadlock in our application. this requires us to kill the deadlocked queries. We are getting the following error in our logs "Waiting for table metadata lock SELECT&...
3
votes
2answers
334 views

Deadlock chain, but no deadlock?

I've setup the profiler to catch these deadlock events: Deadlock graph Lock: Deadlock Lock: Deadlock Chain Al available columns are ticked (TextData, LoginName, SPID, etc.) So far, I was able to ...
1
vote
1answer
149 views

MS SQL Server 2019 - deadlock after row update by primary key, possibly caused by trigger

I've a table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](65) NOT NULL, [nickname] [nvarchar](65) ...
1
vote
1answer
139 views

Facing deadlock after introducing a new Non-clustered Index on Heap

I am facing a strange issue wherein I had analyzed a stored procedure which runs every 3 minutes and was putting load on CPU. I found out that there are number of select statements which is part of ...

1
2 3 4 5
11