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
0answers
15 views

Transaction was deadlocked on lock resources with another process and was chosen as the

I have table in SQL Server 2014. Please find the structure below: CREATE TABLE [dbo].[ProductIMEISerialNoes]( [ID] [int] IDENTITY(1,1) NOT NULL, [IMEI1] [nvarchar](max) NULL, [IMEI2] [...
0
votes
1answer
34 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
67 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
30 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
51 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
79 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 ...
0
votes
1answer
25 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
53 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> <...
0
votes
0answers
30 views

Is it possible than two simple updates on same table interlocks (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
41 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
932 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
35 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
18 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
299 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
48 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
115 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
40 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
47 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
31 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
52 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
258 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
43 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
191 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
19 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
42 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
34 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
246 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
0answers
24 views

Blocking task report is showing false positive

Here is the code I am using to send email alerts. It seems to send more false +ves, can anyone guide me how to resolve this ? DECLARE @Waiting INT; DECLARE @Message NVARCHAR(MAX); SELECT @Waiting =...
0
votes
1answer
75 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
0answers
33 views

SQL Deadlock History [duplicate]

What query can I run to display historical deadlocks? I can see the current days deadlocks by going into "Management - Extended Events - system_health - package0.event_file" and looking at the ...
0
votes
1answer
38 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
38 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 ...
0
votes
1answer
366 views

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205) when update

I have some issue when i test 100 user fill in attendance with same time. i got error message like this Transaction (Process ID) was deadlocked on lock resources with another process and has ...
0
votes
2answers
81 views

What is the difference between lock_timeout and deadlock_timeout?

What is the play between lock_timeout and deadlock_timeout ? What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database ...
2
votes
2answers
34 views

Force update statements to take page locks in the same order?

I have two update statements on the same table that take page locks in different orders. This leads to deadlocks. Process A owns an update lock on page 1, and requests an update lock on page 2. ...
0
votes
1answer
277 views

MySQL: how to prevent deadlock on concurrent inserts

Percona Server 5.6.39-83.1-log Table structure: CREATE TABLE `myTable` ( `column_1` VARCHAR(50) NOT NULL DEFAULT '', `column_2` VARCHAR(50) NOT NULL DEFAULT '', `column_3` VARCHAR(50) ...
0
votes
3answers
172 views

MySQL - get latest record when date and time are two fields

I have a DB in which date and time are two columns. I need to get the latest (by latest I mean the record with the highest time) row. This is used as a sub query to feed another query. DB id ...
3
votes
1answer
98 views

Why do updates to separate sets of rows deadlock?

Update: I have resolved my problem by switching to optimistic locking (SNAPSHOT isolation). The question of why that was necessary in the first place remains unanswered, but I had to move on and thus ...
0
votes
1answer
87 views

Unable to understand this mysql deadlock log

I am getting a good number of these deadlocks (low % of total requests, but still high in absolute errors). For all the deadlocks, the pattern is very similar; but I am unable to understand why there ...
0
votes
1answer
46 views

Deadlock debugging with node: log responsible queries

I have a system of many node scripts that read and/or write automatically on a Postgres database all the time. One of those scripts randomly gets a deadlock. I would like to debug this, but the ...
0
votes
0answers
60 views

SSIS ETL Deadlock (Single Job)

I have a single SSIS Package on the server running via SQL Agent Job. Every now an then we are getting deadlocks issue. I used Breant Ozar's sp_BlitzLock to find out the deadlocks history and got ...
0
votes
1answer
653 views

Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE `...
1
vote
1answer
88 views

Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?

Consider the following when the transaction isolation level is Read Committed (the default): UPDATE example SET foo = 'bar' WHERE ... and DELETE FROM example WHERE ... Neither DELETE nor UPDATE ...
2
votes
1answer
155 views

Deadlock troubleshooting in SQLServer

Edit: In case it is relevant, we are connecting to the database via JDBC in a java webapp. We use autocommit=off for all of these operations, and deadlocks mean the job thread is not committed. I am ...
0
votes
2answers
123 views

Avoiding deadlocks in PostgreSQL with multiple clients

I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run ...
1
vote
2answers
1k views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
0
votes
1answer
116 views

How to get the SQL_ID that is blocking another SQL?

Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL. Here is my script, if you try this, You never will get ...
0
votes
1answer
61 views

MySQL - How can a deadlock b caused by the same transaction?

I am experiencing a deadlock scenario, and it's possible that I'm just not reading this correctly but here are the details, my interpretation is at the bottom. The players: Transaction 1: An ETL ...
5
votes
1answer
435 views

SQL Server Deadlock between two selects and one update on one table

Our application encounters a deadlock every now and again (about once a week). The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the ...

1
2 3 4 5
11