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 vote
2 answers
32 views

What queries should you run to debug what are the stored procedures that caused a deadlock and on what line I should look at on MSSQL?

What queries should you run to debug what are the stored procedures that caused a deadlock and on what line I should look at on MSSQL? I am trying to fix a recurring deadlock on MSSQL. The issue is I ...
user avatar
  • 11
0 votes
2 answers
66 views

SQL Server - Exclusive lock taken by a SELECT statement - Why?

I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below. I'm confused because the deadlock is ...
user avatar
0 votes
1 answer
34 views

How to log deadlocks for each database separately

I have MariaDB 10.6.5 server with several databases. I added a line innodb_print_all_deadlocks = 1 to config file. Now all deadlocks are logged to one file /var/log/mysql/mariadb.err Is there any way ...
user avatar
1 vote
1 answer
58 views

How to avoid deadlocking in a stored procedure with SERIALIZABLE isolation level, INSERTs, UPDATEs, and DELETEs

I'm having trouble with deadlocks in a stored procedure. This stored procedure is part of a custom full-text-like search system. It's called after a record is updated, and inserts/updates a list of &...
user avatar
  • 129
1 vote
1 answer
60 views

Deadlock on common IX lock

I am trying to make sense of a deadlock. The table in question mytb is only used in the following query: delete mytb with (uplock,holdlock) where key_value=@value called through sp_executesql. Both ...
user avatar
4 votes
1 answer
137 views

Duplicate resource on deadlock xml

As I was debugging a deadlock, I noticed that in the xml's resource-list, the first and third object locks are the same. Is there some reason for this? The <resource-list> verbatim: <...
user avatar
0 votes
3 answers
60 views

Causing an artificial infinite deadlock on MSSQL?

Is there a way of causing an artificial infinite deadlock on MSSQL? I need to cause a deadlock so I can test out some stored procedure to automatically remove the deadlock. Is there a way to do this?
user avatar
  • 5
0 votes
1 answer
40 views

Best way to run performance tests in a production PostgreSQL 13 database?

First, I'm asking this question because I'm studying to be a DBA. Recently I learned a lot about database locking, and then some questions arouse in my mind. Suppose I'm a DBA, I'm in charge of a huge ...
user avatar
1 vote
1 answer
67 views

Deadlock reading table inside an update trigger

I am using Microsoft SQL Server 2019 with an UPDATE trigger that reads the table that is being updated. If the trigger is called simultaneously, there will be a deadlock situation. The trigger roughly ...
user avatar
0 votes
1 answer
37 views

Deadlock on same table when creating partition in PostgreSQL

PostgreSQL 14.1. I have a partitioned table liked this: create table dataset_session ( dataset_id integer not null references dataset (id) session_id integer not null, external_id uuid not ...
user avatar
  • 165
3 votes
2 answers
536 views

Why does upsert result in a deadlock?

I'm doing the below upsert statement, which I need to have run multiple times in a short timeframe. I wrote this statement based on examples from Aaron Bertrand's Please stop using this UPSERT anti-...
user avatar
0 votes
0 answers
53 views

Postgresql 13: Blocked queries

I have a database server running Postgresql 13.4 in AWS RDS. I'm trying to use pglogical to replicate in our data from our 9.6 Postgres RDS instance. Pglogical status is currently 'down' and I'm ...
user avatar
  • 123
1 vote
1 answer
152 views

Can I retry every SQL query in case of a deadlock exception (MSSQL)

Is it always safe to retry an SQL query in case of a deadlock exception? Can this lead to unexpected behavior or executing an operation twice? I think a deadlock exception always means a rollback even ...
user avatar
1 vote
1 answer
82 views

SQL Server unexplained deadlocks events

We are using Microsoft SQL Server 2017 Standard Edition (64-bit) on AWS and we are suffering from unexplained deadlocks events. We are trying to update different unique rows from a different processes,...
user avatar
0 votes
2 answers
121 views

Postgres deadlock on sharedlock

I'm having a problem where a couple UPDATE sql statements are locking up on a particular table in a database. For some reason these statements seem to be requiring access to a "ShareLock" ...
user avatar
3 votes
1 answer
331 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 ...
user avatar
7 votes
1 answer
318 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 ...
user avatar
0 votes
0 answers
72 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 ...
user avatar
  • 1
0 votes
1 answer
310 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; ...
user avatar
  • 111
1 vote
2 answers
243 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 ...
user avatar
  • 113
10 votes
1 answer
195 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 ...
user avatar
  • 30.3k
-1 votes
2 answers
68 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, ...
user avatar
  • 2,130
0 votes
1 answer
107 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 ...
user avatar
  • 2,130
3 votes
2 answers
1k 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 ...
user avatar
  • 2,130
0 votes
1 answer
63 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 ...
user avatar
  • 2,130
2 votes
1 answer
53 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( ...
user avatar
  • 459
0 votes
0 answers
65 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 ...
user avatar
  • 43
3 votes
1 answer
201 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> <...
user avatar
  • 43
1 vote
2 answers
439 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 ...
user avatar
3 votes
1 answer
468 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) ...
user avatar
0 votes
3 answers
131 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 ...
user avatar
0 votes
0 answers
261 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 ...
user avatar
  • 101
0 votes
1 answer
61 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 ...
user avatar
  • 101
0 votes
0 answers
36 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 ...
user avatar
0 votes
1 answer
104 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 ...
user avatar
3 votes
1 answer
307 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 ...
user avatar
  • 185
0 votes
0 answers
85 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, ...
user avatar
  • 125
2 votes
0 answers
133 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 ...
user avatar
  • 21
0 votes
1 answer
242 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 ...
user avatar
  • 103
2 votes
2 answers
80 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 ...
user avatar
1 vote
1 answer
111 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 &...
user avatar
0 votes
1 answer
104 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 ...
user avatar
  • 1,323
1 vote
1 answer
51 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 ...
user avatar
  • 13
1 vote
1 answer
1k 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 ...
user avatar
1 vote
1 answer
473 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 ...
user avatar
0 votes
1 answer
389 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,...
user avatar
  • 123
0 votes
0 answers
129 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 ...
user avatar
1 vote
0 answers
45 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-...
user avatar
  • 123
3 votes
1 answer
232 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> <...
user avatar
  • 61.1k
0 votes
0 answers
34 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 ...
user avatar

1
2 3 4 5
12