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
1 answer
107 views

Understanding Lock Behavior in SQL Server Deadlock Scenario with INSERT

Hello SQL Server Experts, I'm currently experimenting with SQL Server deadlocks and have intentionally created one to better understand locking behavior. After setting up a test table, I ran two ...
Just a learner's user avatar
0 votes
0 answers
47 views

batch insert dead lock

Can anyone help me? Why is this deadlocked? mysql: 8.0.22 design tables Note that this is the desensitized table structure CREATE TABLE `room_db`.`Untitled` ( `id` bigint NOT NULL AUTO_INCREMENT, `...
atlan's user avatar
  • 1
2 votes
2 answers
150 views

SQL Server: Trigger with UPDATE statement causes deadlocks

I have added a trigger to perform a change tracking mechanism on a table that has a relatively high load. During testing, we encountered no issues but now as it is in production there are deadlocks ...
JannikB's user avatar
  • 21
0 votes
1 answer
107 views

PostgreSQL: does the order of rows in a bulk insert control the order of `FOR EACH ROW` triggers?

I have a trigger like BEFORE INSERT ON table_a FOR EACH ROW EXECUTE FUNCTION upsert_into_table_b(). If I execute a bulk insert like INSERT INTO table_a ([columns]) VALUES ([row 1 values]), ([row 2 ...
Nathan Long's user avatar
0 votes
1 answer
123 views

Mysql deadlock on non unique index

I'm getting the deadlock on MySQL v8.0.32 which is very strange for me because of the following: I'm trying to insert the data: parent-child record (users, user_roles) - each transactions inserts new ...
Andrey Khataev's user avatar
0 votes
1 answer
73 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), ...
Paras Sahu's user avatar
1 vote
0 answers
98 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 ...
theta-fish's user avatar
1 vote
1 answer
278 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, ...
KaliTheGreat's user avatar
1 vote
1 answer
189 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 | ...
iForests's user avatar
  • 113
0 votes
1 answer
48 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 --> ...
Alex F's user avatar
  • 101
0 votes
1 answer
37 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 ...
Jacqueline's user avatar
0 votes
0 answers
128 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 ...
user1589188's user avatar
0 votes
1 answer
160 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). ...
Drees's user avatar
  • 11
0 votes
0 answers
81 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. ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
1 answer
212 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 ...
ishan verma's user avatar
0 votes
2 answers
311 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 --------------...
owl's user avatar
  • 3
1 vote
1 answer
472 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 ...
Alvin's user avatar
  • 35
0 votes
1 answer
1k 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; ...
Dmitry's user avatar
  • 3
-1 votes
1 answer
207 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 ...
Areeb's user avatar
  • 107
0 votes
0 answers
68 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 ...
Eduardo Wada's user avatar
1 vote
1 answer
393 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 ...
bluenote10's user avatar
1 vote
1 answer
513 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 ...
Jishnu Chandran's user avatar
-1 votes
1 answer
1k 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 ...
chell's user avatar
  • 195
0 votes
1 answer
94 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 ...
Hayate's user avatar
  • 101
1 vote
0 answers
336 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 ...
Matthew's user avatar
  • 11
1 vote
1 answer
80 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 ...
Eduardo Wada's user avatar
4 votes
1 answer
227 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 ...
DamiToma's user avatar
  • 141
1 vote
0 answers
219 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 ...
Paradox's user avatar
  • 155
3 votes
1 answer
1k 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 ...
ChrisJ's user avatar
  • 591
0 votes
1 answer
301 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 ...
ChrisJ's user avatar
  • 591
4 votes
1 answer
288 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 ...
Gleb Lashuk's user avatar
0 votes
1 answer
88 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 @...
mmierzwa's user avatar
0 votes
1 answer
321 views

Deadlock on Non Clustered Index

<deadlock> <victim-list> <victimProcess id="process1e4222068c8" /> </victim-list> <process-list> <process id=&...
JieLong's user avatar
  • 167
3 votes
0 answers
527 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 ...
Mads K's user avatar
  • 31
1 vote
1 answer
141 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 ...
feilong gao's user avatar
2 votes
1 answer
797 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', ...
jvdberg08's user avatar
0 votes
1 answer
77 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 ...
Dominique's user avatar
  • 521
2 votes
0 answers
161 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: ...
user355834's user avatar
1 vote
1 answer
236 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 ...
AlanK's user avatar
  • 111
2 votes
2 answers
828 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: ...
Chris Landry's user avatar
2 votes
1 answer
143 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 ...
user2368632's user avatar
  • 1,087
1 vote
1 answer
297 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 ...
xhr489's user avatar
  • 805
0 votes
1 answer
115 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'...
Martin Riddar's user avatar
4 votes
1 answer
355 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 ...
Valentin Nasraty's user avatar
0 votes
0 answers
72 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 ...
Malek Alhourani's user avatar
0 votes
0 answers
111 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 ...
bedla.czech's user avatar
1 vote
1 answer
5k 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 ...
igelr's user avatar
  • 2,120
0 votes
1 answer
207 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 ...
Mark Gibson's user avatar
-1 votes
1 answer
1k 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 ...
IT Researcher143's user avatar
0 votes
0 answers
105 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 ...
karnish's user avatar
  • 47

1
2 3 4 5
12