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
30 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
24 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
23 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
64 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
27 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
30 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
49 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
51 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
90 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
42 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
28 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
54 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
205 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
73 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 ...
0
votes
0answers
25 views

SQL Deadlock resolution

I am seeking help regarding a SQL deadlock I am trying to resolve. Please ignore the fact that the query -> 'Select * from ... Where In...' can be written in a much more straightforward way. I got as ...
2
votes
1answer
95 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
80 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 ...
2
votes
2answers
215 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
34 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
26 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
279 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 ...
3
votes
2answers
212 views

SQL Deadlocks - Same Stored Procedure Called Concurrently

I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to ...
0
votes
1answer
28 views

Multiple lock owners in SQL Server 2014

I've encountered a deadlock on SQL Server 2014, and created a deadlock report using extended events. Below is an excerpt from the report. What does it mean for objectlock(objid="554979041") to have ...
0
votes
1answer
63 views

Multiple SPIDs in sp_who2, Deadlocks and Parallelism

There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things: Number ...
0
votes
1answer
66 views

Unusual deadlock occuring

I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however. Below is pseudo code for the stored procedure. The stored procedure generates a ...
1
vote
3answers
74 views

Constant deadlocks on a high load table

I have a table files with about 73m rows, which totals to about 45GB. The rows being inserted in a day are about 100k. But the total rows which we run IODKU can reach tens of millions a day (since ...
0
votes
1answer
33 views

Minimizing and Handling Deadlocks Strategies

I want to discuss deadlock minimizing strategies. From isolation level to retries to different insert strategies. Lets say we have table A: CREATE TABLE `A` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
2
votes
1answer
55 views

How to get more physical details when executing an SQL query?

I want to get some physical features about a SQL query execution with PostgreSQL, like Read/write page accesses I/O operations Response time Sequential I/O throughput rate Deadlocks Pages scanned ...
0
votes
0answers
21 views

Create deadlock scenario without foreign key [duplicate]

I need to test my C# code in a SQL Server deadlock scenario. Is there a way to create a deadlock "on demand"? I have tried this: Begin Transaction Select * from TABLEA with (tablockx, holdlock) ...
2
votes
2answers
123 views

After upgrade from MySQL 5.5 to 5.7 queries are more frequently encountering deadlock

Recently we migrated our production DB to Amazon RDS with version upgrade from 5.5 to 5.7 using AWS DMS service. After that, we are frequently getting deadlock issues for our insert...on duplicate key ...
0
votes
0answers
33 views

ALTER TABLE causing transaction aborted

There are two transactions. One transaction I used BEGIN RO for read-only transaction. While the other one I used BEGIN to allow me adding a new column to my table called dummy_table. This is what I'...
1
vote
0answers
166 views

Random deadlocks SQL Server 2012 using ODBC Driver 17 and simple “Select * FROM” Stored Procedures

I'm getting random deadlock errors in my application on sometimes the simplest of stored procedures. The application environment where I'm experiencing the error uses the GUNICORN server in ...
5
votes
1answer
178 views

Deadlock Graph - Surviving Statement inputbuf does not show the object that was locked

I have captured a deadlock on my system and the (anonymised) XML output is as follows: <deadlock> <victim-list> <victimProcess id="processf4d9233468" /> </victim-...
0
votes
0answers
55 views

Deadlock in SQL Server

I have been trying to resolve this deadlock for a while now with no success, its seems like the users are opening multiple tabs when executing this task. Below is the deadlock trace from red gate. Can ...
0
votes
0answers
34 views

How do I handle deadlocking on a Stored Procedure when running an SSIS package?

I am concurrently running (for example 25) the same SSIS package that retrieves data from a 3rd party platform. This data is then inserted into our Production database. In order to increase ...
1
vote
1answer
47 views

Deadlock between two non-related tables

I am facing with a deadlock between two tables Table1 and Table2. The deadlock occurs when an sp (sp2) delete from table2 and other sp (sp1) try to update table1. In table2 the column by which it is ...
0
votes
1answer
113 views

Microsoft SQL Server MERGE deadlock issue

I am using Microsoft SQL Server version: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows ...
2
votes
2answers
322 views

SQL Server Full-text population deadlock

I've created a full-text index on a non-persisted computed column in a table (details here). However, every couple days, the population gets deadlocked and stopped. Specifically: DESCRIPTION: A ...
0
votes
1answer
73 views

deadlock detected during oracle expdp

I have an Oracle 11g and I wanted to export all my shcema objects, with this command: expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH ...
-4
votes
2answers
173 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
0
votes
1answer
228 views

Deadlock on partitioned table

we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing. Our setup is like this: ...
1
vote
1answer
141 views

Can I prevent deadlock using `WITH(NOLOCK)`?

I have a table which I'm only Insert, Delete and Select (no Updates) on Insert, Sometimes it used a session lock (WITH (TABLOCKX)) I have a program using ADO which Select from the table using the ...
0
votes
2answers
149 views

Tools for SQL Deadlock Statistics [closed]

One of my company's SQL database generates a lot of deadlocks per day recently (someday >100) I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same ...
6
votes
1answer
250 views

Deadlock Priority High Chosen as deadlock victim

I have SQL Server 2016 SP2 (13.0.5237.0). Here is a deadlock graph I noticed recently in my system. The process with high deadlock priority was chosen as a victim (probably because of high log usage ...
10
votes
2answers
513 views

How to Prevent Partitioned Columnstore Deadlocks on SELECT

I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks ...
3
votes
1answer
2k views

How to prevent Deadlock on SELECT queries?

I have a multi-session DB. Few sessions inserting many rows to the DB. and one session selects from this table. Sometimes I'm getting a deadlock on the SELECT query and I don't understand why. For ...
0
votes
1answer
638 views

AWS RDS mysql deadlock

Everything in our production environment was working well. Yesterday, suddenly out of no where we were getting "lock wait" on any transaction related to a specific row. Same transaction on another row ...
0
votes
0answers
75 views

Reproduce Deadlock in SQL Server

I have identified a deadlock by using Solarwinds, I have noticed that it is happening in a specific table, that table only has a primary key, which tells me that the deadlock makes sense since every ...
0
votes
0answers
157 views

Deadlock / Isolation Level

I was analyzing a deadlock that I received when I noticed that one of the processes involved in the deadlock had the isolation level set to Read Uncommitted. I search the web... and searched in my ...
0
votes
0answers
41 views

MySQL - Investigate deadlock on atomic update

On a high traffic queue managed by Redis there is the following code: update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ? Here's is the table schema: ...

1
2 3 4 5
10