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
2answers
53 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
353 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
81 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
78 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
36 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
22 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
59 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
194 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
58 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
88 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
68 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
69 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
28 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
53 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
41 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
46 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
145 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
137 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
15 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
47 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
81 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
35 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
86 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
84 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
73 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
60 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> <...
1
vote
0answers
48 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 ...
1
vote
1answer
113 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
68 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
32 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
324 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
85 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
122 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
vote
1answer
54 views

Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled?

Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot ...
2
votes
1answer
106 views

Deadlock and schema modification lock

I have a deadlock that I need some input for <deadlock> <victim-list> <victimProcess id="process4c86a61868" /> </victim-list> <process-list> <process id=&...
0
votes
1answer
87 views

Can PostgreSQL UPDATEs in autocommit mode deadlock?

Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)
0
votes
0answers
118 views

Rolling back transaction with trx_mysql_thread_id of 0 create deadlocks on update

I'm using MySQL 5.6.41 on AWS RDS. I have seen, recently, lot of transactions ending as deadlocks. Using SELECT * FROM information_schema.innodb_trx; I found that a transaction was always there. +-----...
1
vote
1answer
625 views

Should simultaneous bulk inserts cause deadlocks, with a PK being an identity column, in SQL Server?

When analyzing a deadlock graph, it looks like the victim is a bulk insert, and the blockers are the same bulk insert. Using a message bus, many subscribers can end up doing a bulk insert at roughly ...
0
votes
1answer
65 views

Is my theory on INSERT deadlocks correct?

I originally had this (as three separate PG queries, from my applications): INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING; INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING; ...
3
votes
3answers
446 views

(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction

Database: MySQL I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock. The error says SQLSTATE[40001]: Serialization failure: 1213 ...
0
votes
1answer
22 views

SQL Sever Transactional Replication on DB with deadlocks and blocking issues

Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?
1
vote
0answers
68 views

InnoDB trigger deadlocks itself?

I have a MySQL 5.7.22 database using the InnoDB engine. There's a table respondents that has 6 associated triggers - three of them make CRUD operations on a completed_respondents table, and the other ...
0
votes
1answer
35 views

Why do these queries deadlock and how to work this around in MariaDB?

The following 2 queries enounter DB deadlock in my program: DELETE FROM proxies WHERE NOT EXISTS ( SELECT 1 FROM proxy_xref_proxy_list AS pxpl WHERE pxpl.ProxyID=proxies.ID AND (pxpl....
11
votes
1answer
528 views

MySQL deadlock: upsert query acquiring gap lock twice?

I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen. Here is the deadlock output: ------...
0
votes
1answer
170 views

how see parameter value on xml_deadlock_report

i have some xml_deadlock_report i got from this step management > sessions > system_health > package().even_file but when i see xml_deadlock_report i got sql query but i can't see what parameter use ...
0
votes
1answer
44 views

Updating column having index causing Deadlock

My update statement: Update XyzTable set state ='second' where pgmNumber = 'prog123' and state = 'first' Both state and pgmNumber columns has individual index. Deadlock is happening even if above ...
0
votes
1answer
57 views

SQL Server Deadlock OPENXML insert

I am running into a deadlock issue when I call a stored procedure in Sql Server 2014 Enterprise Edition. An XML string is passed into the stored procedure and can be called many times in quick ...

1
2 3 4 5
11