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

When does InnoDB acquire gap locks on the post gap of the selected/updated records?

I am currently learning about Gap Lock in the InnoDB engine, In MySQL doc they say, InnoDB will acquire a gap locks only on the preceding gap, but on this Percona article as I understood from their ...
0 votes
0 answers
27 views

When avoiding MSSQL Deadlock on FK insert - is it good ideat to just remove FK?

we have following situation. There is parent child between two tables with FK constraint. When there is higher concurrent access when inserting data with FK id, we are receiving deadlock exception ...
0 votes
1 answer
25 views

How to detect and log deadlocks in PostgreSQL

I want to detect and log all the deadlocks that occurred in my PostgreSQL server, preferrably including the details of the queries. Is this possible in PostgreSQL. I want a solution like Extended ...
  • 2,068
0 votes
1 answer
43 views

SQL Server 2017 - Read-Committed Snapshot isolation update deadlocks

I have a stored procedure that is running concurrently on schedule and is causing 100's of deadlocks a day. The transaction is managed by the application and the database is using Read-Committed ...
-1 votes
1 answer
51 views

Deadlocks caused due to Keylocks and Pagelocks

I have a database on which select, update and insert queries are run. At times it gave me deadlocks (key locks) while updating and selecting data from the table, it considered the select query to be ...
0 votes
0 answers
35 views

Deadlock because of fulltext index - FTS_DOC_ID_INDEX in MySQL

We are having an issue with one of our innodb tables. This table has primary key on integer column (and this one is not auto increment column ) and fulltext key on one of the text columns, Application ...
  • 47
1 vote
1 answer
44 views

Understanding MySQL Locks and Deadlocks

I was just checking the articles regarding the MySQL table locking system. I wanted to learn about how we can avoid the deadlocks in MySQL while UPDATE/DELETE operations. What I have done so far: ...
2 votes
1 answer
64 views

Why would a SQL Server query request the same lock twice and deadlock?

I have SQL Server database that keeps getting deadlocked when two queries attempt to acquire locks they already own to the same table. The above deadlock graph get's me very confused. Both PageLock ...
  • 31
0 votes
0 answers
30 views

Find out why deadlock happens

Consider the following table: CREATE TABLE limits ( foo_id INTEGER NOT NULL, bar_id INTEGER NOT NULL, limit INTEGER NOT NULL, changed_at TIMESTAMP NOT NULL, PRIMARY KEY (foo_id, bar_id) ); ...
  • 119
3 votes
1 answer
105 views

Deadlock on DELETE - DELETE for parallel execution of same stored procedure

I have a stored procedure which ran parallely by two processes. Code for sp : DECLARE @inputXML XML DECLARE @tempTable TABLE (afid INT, ccid INT) INSERT INTO @tempTable SELECT ref.value('@afid', '...
1 vote
0 answers
94 views

Why would executing individual update statements on two unrelated tables each within their own transaction cause deadlock?

I've searched the stackoverflow network, but couldn't find anything similar to the scenario detailed below. It doesn't make sense how deadlock could occur, but we're seeing logs from the database ...
2 votes
0 answers
123 views

Is this the correct way to select with updlock and then conditionally insert?

I have a table which tracks 'reservations', defined as follows: CREATE TABLE [dbo].[Reservation] ( [OrderId] nvarchar(40) CONSTRAINT [PK_Reservation_OrderId] PRIMARY KEY CLUSTERED, [GroupId] ...
0 votes
0 answers
68 views

InnoDB Deadlock when transaction waiting for lock already acquired

Transaction (2) holds the following lock: RECORD LOCKS space id 11404 page no 1144152 n bits 72 index PRIMARY of table [tableName] /* Partition [tableName]_p59 */ trx id 28648068046 lock_mode X Record ...
10 votes
0 answers
247 views

Has SQL Server not Killed a Deadlock? [closed]

I have encountered a situation where a user has run something in one of our applications which runs a stored procedure. The stored procedure does a few bits of logging (INSERTing to a log table) and ...
  • 1,600
1 vote
2 answers
44 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 ...
  • 11
0 votes
2 answers
275 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 ...
0 votes
1 answer
49 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 ...
1 vote
1 answer
277 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 &...
  • 139
1 vote
1 answer
124 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 ...
4 votes
1 answer
143 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: <...
0 votes
3 answers
96 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?
  • 5
0 votes
1 answer
59 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 ...
1 vote
1 answer
160 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 ...
0 votes
1 answer
191 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 ...
  • 177
3 votes
2 answers
792 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-...
0 votes
0 answers
117 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 ...
  • 135
1 vote
1 answer
488 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 ...
1 vote
1 answer
88 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,...
0 votes
2 answers
227 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" ...
3 votes
1 answer
565 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 ...
7 votes
1 answer
456 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
0 answers
144 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 ...
  • 1
0 votes
1 answer
1k 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; ...
  • 111
1 vote
2 answers
519 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 ...
  • 113
10 votes
1 answer
210 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 ...
  • 30.9k
-1 votes
2 answers
87 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, ...
  • 2,870
0 votes
1 answer
219 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 ...
  • 2,870
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 ...
  • 2,870
0 votes
1 answer
130 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,870
2 votes
1 answer
238 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( ...
  • 469
0 votes
0 answers
101 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 ...
  • 43
3 votes
1 answer
264 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> <...
  • 43
1 vote
2 answers
917 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
1 answer
486 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
3 answers
162 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
0 answers
401 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 ...
  • 101
0 votes
1 answer
107 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 ...
  • 101
0 votes
1 answer
171 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
1 answer
413 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 ...
  • 185
0 votes
0 answers
100 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, ...
  • 125

1
2 3 4 5
12