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.
596
questions
0
votes
1
answer
32
views
Getting deadlocks very frequently in Mysql insert query
=2023-09-15 15:16:35 0x1530b7bc17002023-09-15 15:16:35 0x1530b7bc1700
*** (1) TRANSACTION:
TRANSACTION 55060584750, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), ...
- 11
1
vote
0
answers
59
views
Same update on different rows results in deadlocking
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the ...
- 11
1
vote
1
answer
39
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 111
1
vote
1
answer
36
views
Postgres: deadlock when ATTACH PARTITION to a table with a lots of INSERTs
I have a partitioned table user_stay which has many INSERTs and UPDATEs.
db=> \d user_stay;
Partitioned table "public.user_stay"
Column | ...
- 113
0
votes
1
answer
25
views
Simultaneous INSERTs which lock the target table inside a before-insert trigger result in a deadlock
I'm working on a web application which uses a PostgreSQL database for storage. There are many logical entities in this application which are related to each other like hotel --> hotel_floor --> ...
- 101
0
votes
1
answer
28
views
Locking order in a nested query
I'm trying to prevent deadlocks in my code because i may use both in different parts of my project.
Do they lock rows in the same order?
Nested
select * from `carts`
where `carts`.`user_id` = ? and ...
- 57
0
votes
0
answers
78
views
Resolving deadlock between an UPDATE and a SELECT with query hint
I bumped into a page lock deadlock on a table when two queries are running at the same time in two separate sessions, one is selecting from the table (holds S lock on page x and waits for S lock on ...
- 143
0
votes
1
answer
61
views
Deadlock on DELETE statements (stored procedure & adhoc query)
I am experiencing an issue with deadlocks when running a stored procedure that is run every 15 minutes. There is also an adhoc query that is being run by an external program (I don't have access to).
...
- 111
0
votes
0
answers
77
views
Updating 2 tables in same order by 2 different process yield deadlock
For some reason, we have multiple processes that get a request to update some tables (legacy code).
Let's say we have 2 process that simultaneously updating MyTable1 and MyTable2 in a transaction.
...
0
votes
1
answer
97
views
Is it safe to configure Lock Escalation as 'AUTO' for partitioned tables
LOCK_ESCALATION= AUTO.
In one of our Data warehousing product we use partitioned tables. They are partitioned based on an integer column. We have implemented partitioning here so we can easily switch ...
- 29
0
votes
2
answers
116
views
Deadlock while concurrently updating multiple rows on the same table
We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it
--------------...
- 3
1
vote
1
answer
214
views
Deadlock in AFTER INSERT trigger
I have an AFTER INSERT trigger for table A that updates multiple rows in table B (incrementing a column in table B). Normally, the web server would insert multiple rows into table A using a single ...
- 35
0
votes
1
answer
558
views
Is deadlock possible on first SELECT FOR UPDATE in transaction in PostgreSQL
I've got a Deadlock error
deadlock detected
DETAIL: Process 14565 waits for ShareLock on transaction 1986906143; blocked by process 14567.
Process 14567 waits for ShareLock on transaction 1986906141; ...
- 3
-1
votes
1
answer
105
views
MariaDB / MySQL: Database Froze
We have had weird database freezes happen randomly. Usually, killing the troublesome process or thread helps, but in this case, nothing helped at all!
Nearly all of the queries in the process list ...
- 107
0
votes
0
answers
57
views
How to get the full query involved in a deadlock?
I have the following deadlock graph:
The problem is that when I look into the queries involved, they seem to be two select statements that shouldn't be getting exclusive locks.
After reading this ...
- 154
1
vote
1
answer
227
views
How to determine if a query can potentially deadlock in Postgres?
When implementing database queries I'm often wondering if the query I'm writing can potentially deadlock when executed in parallel by many clients. Often I only find out by running the query in ...
- 172
1
vote
1
answer
287
views
Why my clustered index causing deadlock (Keylocks and Pagelocks)
We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously.
We are actually ...
-1
votes
1
answer
239
views
How to avoid deadlock inside transaction? [closed]
I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen ...
- 175
0
votes
1
answer
78
views
Deadlock, update where id=pk_value with different pk_values, figure out reason and how to fix
How it possible to have such kind of deadlock?
id column is a unique primary key, so no gap to lock. I don't have any other conditions to lock more than one row. It is different rows, how that ...
- 101
1
vote
0
answers
191
views
Deadlocks on Batch Updates Posgres 12
Having some massive issue with deadlocks currently and I can't seem to find anything that will fix it.
We have a large sql query that contains quite a few CTEs, one of them does the locking/selecting ...
- 11
1
vote
1
answer
67
views
Why is my non clustered index causing this deadlock?
I have an application that runs the following query in multiple parallel threads:
update Table1 SET Col1 = 'Some value' WHERE Order_ID = @orderID
Order_ID is NOT the primary key on this table, but ...
- 154
4
votes
1
answer
174
views
MySQL - Understanding what is causing deadlocks
I've researched the topic on the official documentation and other questions, however weeks into it I'm still failing to understand what's causing deadlocks in my application. It only happens in ...
- 141
1
vote
0
answers
158
views
What is the object involved in an Object Lock in SQL Server Profiler's Deadlock graph? [closed]
In searching documentation online, I have found that there can be row, page, or table locks. However, I ran a SQL Server Profiler trace and noticed both a Page Lock and an Object lock. I can easily ...
- 121
2
votes
1
answer
682
views
Avoiding deadlocks when locking multiple rows without using NOWAIT
In a couple of answers I've been advised that NOWAIT is necessary to prevent deadlocks, which has surprised me because it's not mentioned in the Postgres documentation on locking and deadlocks
Given ...
- 581
0
votes
1
answer
209
views
Does UPDATE FROM VALUES lock rows in the order they are listed?
I'm bulk updating some rows using UPDATE ... FROM (values ... as described in this answer.
Will the rows will be locked in the order that they appear in the values? Or do I need to do an explicit ...
- 581
4
votes
1
answer
219
views
Why does SQL Server use INDEX SPOOL when there is already a relevant index during FK check?
I've got two tables in SQL SERVER 2019: Condition table with 2 FK on Reference table. Condition table has appropriate indexes on FK columns. The schema screenshot and SQL script to generate it are ...
- 49
0
votes
1
answer
84
views
Cannot understand how deadlock happens
In fairly simple T-SQL code deadlock happens:
INSERT INTO dbo.logs (
source,
status,
description,
aId
) VALUES ( @source, @status, @description, @aid )
set @i = SCOPE_IDENTITY()
if @...
- 3
0
votes
1
answer
272
views
Deadlock on Non Clustered Index
<deadlock>
<victim-list>
<victimProcess id="process1e4222068c8" />
</victim-list>
<process-list>
<process id=&...
- 167
3
votes
0
answers
391
views
What is the cause of persistent SQL Server deadlocks with a chosen victim, where the victim is never stopped, and no transactions are rolled back?
We are experiencing an issue with Microsoft SQL Server where every few days of running our application against the database, it goes into a state where it keeps deadlocking. It logs a deadlock about ...
- 31
1
vote
1
answer
107
views
MySQL does not open a transaction, is there a deadlock between different SQLs?
2 concurrent do not start transactions.
Concurrent A locks 1-3-5 sequentially, of course, the actual locking process must be one by one.Concurrent B locks 5-8-1 sequentially, of course, the actual ...
- 21
2
votes
1
answer
609
views
Deadlock on 2 inserts in MySQL
I'm trying to get this query to work:
insert into `risks` (`created_at`, `description`, `metadata`, `notes`, `project_id`, `risk_number`, `updated_at`)
values (
'2022-12-02 13:56:08.742',
...
- 23
0
votes
1
answer
67
views
Which two threads are handling which table and how to solve this?
I'm working on a multithreaded application (VB.Net/C#), who is working on an SQL-server database.
Regularly, my customer is complaining that the application show "deadlock"-like exceptions ...
- 523
2
votes
0
answers
143
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121
0
votes
0
answers
49
views
MySql Trigger Deadlock
I'm having issues with a trigger sometimes causing a deadlock. Only happens when the db is getting hit pretty hard but happens enough to be annoying. May it's just bad practice to use this trigger the ...
- 161
1
vote
1
answer
164
views
SQL Server: DBCC UPDATEUSAGE does not return
I am an experienced programmer (not a DBA) and would appreciate troubleshooting guidance if no immediate answer is possible.
The database itself is relatively small - a couple of million rows spread ...
- 111
2
votes
2
answers
656
views
Why is MySQL Delete on Foreign Key Index locking a record not part of the index (leads to DEADLOCK)
I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index.
I'm not sure why this could happen.
Here's my scenario:
...
- 31
2
votes
1
answer
124
views
How to resolve linenumber in deadlock graph XML to line number displayed in SSMS sproc definition?
Captured the XML graph for a given deadlock involving some large / multi query sp's. However, not really sure how to interpret the line, stmtstart, stmtend of the frame element to determine which ...
- 1,077
1
vote
1
answer
224
views
Deadlock with Reorganize Index
I was running alter index reorganize on some columnstore indexes in SQL Server 2016 and got a deadlock message.
Will reorganize always be the victim? Because I do not want to cause other jobs to fail ...
- 785
0
votes
1
answer
88
views
"Unrelated" stored procedures causes deadlock
The title says "unrelated" because that's what they are supposed to be but apparently not...
I've got two stored procedures (log_sequence_event and log_se_event) that causes a deadlock and I'...
4
votes
1
answer
246
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
0
votes
0
answers
66
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
98
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 ...
- 101
1
vote
1
answer
3k
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,120
0
votes
1
answer
162
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
670
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
83
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
214
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
495
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
3
votes
1
answer
404
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', '...
- 197
1
vote
0
answers
328
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 ...
- 25