We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

95 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
1answer
793 views

Oracle Ref Partitioning: Deadlock due to child table row migration

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc. ORDERS table is LIST partitioned on STATUS column and all ...
5
votes
0answers
109 views

Deadlock in REDO thread , 1 replica went OK other didn't?

I have a 3 node cluster and the 2 secondaries initially went into SUSPECT mode due to a deadlock. I tried to resume both and only one caught up while the other is continuously deadlocking. I have to ...
4
votes
1answer
472 views

Is this surprising deadlock reasonable or a MariaDB bug?

I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a ...
3
votes
0answers
135 views

Why do two transactions making a read lock result in a DEADLOCK exception?

Let's assume a scenario that flows as follows: Transaction #1 makes this query: SELECT v.id, v.name FROM versions_history as vh INNER JOIN versions as v ON vh.version_id = v.id WHERE vh.project_id = ...
3
votes
1answer
798 views

Deadlock during Delete on Clustered Index

We're seeing many deadlocks, where different sessions are running the exact same delete query (generated by Eclipse LINQ) from the same host: DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) ...
3
votes
0answers
144 views

Why `update` and `delete` acquire EXCLUSIVE lock rather than ROW EXCLUSIVE lock in Greenplum?

As we known, insert,update and delete commands automatically acquire ROW EXCLUSIVE lock in PostgreSQL. As a fork of PostgreSQL 8.2, why in Greenplum update and delete commands automatically acquire ...
3
votes
0answers
267 views

Read/Write Deadlock in SQL Server

We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored ...
3
votes
2answers
865 views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
3
votes
0answers
1k views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
3
votes
2answers
2k views

Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

Can two or more instances of the following deadlock with one another? START TRANSACTION SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE : COMMIT Points to keep in mind: mycolumn ...
2
votes
2answers
207 views

Deadlocks With Trigger Based Replication

A table ORIGINAL exists with the following structure: ID VARCHAR Length 10 (key) VALUE VARCHAR Length 10 (non-key) A table REPLICATION exists with the following structure: ID ...
2
votes
0answers
164 views

Help in understanding how to avoid a page-level deadlock

I have the following deadlock xml <deadlock> <victim-list> <victimProcess id="process9b7ade24e8" /> </victim-list> <process-list> <process id="...
2
votes
0answers
312 views

MySQL delete gradually locks more and more rows

I've encountered a strange problem that I can't explain. MySQL 5.6.34, RDS. Basically, some delete from table where id in (...) queries are locking up indefinitely, but never truly deadlocking. I ...
2
votes
0answers
604 views

Intra-Query Parallelism Deadlock Resolution

I want to know how SQL Server handles intra-query parallelism deadlocks, and whether it kills one of the threads involved in the deadlock? Will there be a change in the result set when this deadlock ...
2
votes
0answers
949 views

PostgreSQL: Deadlock on insert trigger that also updates same table.

I am trying to implement a type 2 slowly changing dimension in a PG data warehouse using a trigger function. The basic idea is that when a new row is inserted, we should check whether NEW is a ...
2
votes
0answers
95 views

Select Query with S lock wins on Insert with IX lock in deadlock

Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2. Attaching the deadlock xml file.Please help! http://pastebin.com/nY9EXbS8 This is from the error log by ...
2
votes
0answers
872 views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
2
votes
0answers
447 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ (filetable_parents_are_directories((...
2
votes
0answers
98 views

Why is this update creating a deadlock?

I have these queries: (This one was deadlocked) And this one: update tbldealerativacao set flagusado = 0 from tbldealerativacao WITH (NOLOCK) inner join tbldealerusuario WITH (NOLOCK) on ...
2
votes
2answers
1k views

What happens when SELECT … FOR UPDATE returns multiple rows?

I'm working on some dead-lock troubleshooting, particularly involving a row-swap. Currently the statements look like this, all running under the same transaction: SELECT IDENTIFIER FROM tables WHERE ...
2
votes
0answers
882 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
0answers
35 views

Locks while stream reading and deleting/inserting involving the same tables?

I have a question regarding a potential block/lock situation: I am reading data using a select command, which is then modified externally and uploaded to a temporary table. A stored procedure will ...
2
votes
0answers
104 views

Deadlock when CODES table has 41900 rows

I'm having a deadlock accessing the same table from two different jobs. I'm testing it on a SQL SERVER 2012 Enterprise edition. Running SQL Profiler I get this graph: You can find the SQL Server ...
2
votes
0answers
2k views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH command)....
2
votes
0answers
482 views

SQL Server 2008 R2 - Deadlock Trace, Need Help Understanding It

I really need help on understanding this deadlock, i've already replaced the values, mind if anyone helps me interpreting where my deadlocks are coming from? What does e_waitpipegetrow mean? btw, i'm ...
2
votes
1answer
893 views

Does the order I declare foreign key constraints in matter?

Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements ...
2
votes
1answer
1k views

How do Locks Work in MySQL or MariaDB

There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of ...
1
vote
0answers
43 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 ...
1
vote
0answers
78 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
1
vote
1answer
302 views

mysql deadlock when update some items whose id are selected from another table

I'm sorry, I just can speak a little English. I have two machines(10.10.102.101, 10.10.102.104) which connect to the same mysql datebase. This is a table and deadlock happened on it. mysql> ...
1
vote
0answers
118 views

What is meant by Orphan Lock and Orphan Sessions?

I have came across this term Orphan locks and Orphan sessions and I really have no idea what that is. I have tried to search it on Google and I still haven't got any useful info/results regarding what ...
1
vote
0answers
191 views

Transactional Replication Deadlocks

I set up transactional replication between two servers on various tables. I noticed that there were a few deadlocks around the time of the snapshot generation(which it appears is expected). What I ...
1
vote
0answers
136 views

Mysql Deadlock in Stored Procedure,composite unique key

I have a table with composite unique key(uniqueness on combination of 3 columns.) Table structure: CREATE TABLE `userreview` ( `cid` bigint(12) unsigned NOT NULL, `conid` bigint(12) unsigned NOT ...
1
vote
1answer
272 views

Why has deadlock behaviour changed between MariaDB 10.1.22 and 10.2.14?

Since upgrading MariaDB 10.1.22 to 10.2.14 our MariaDB slaves are encountering deadlocks that are not handled in less than 600 seconds thus the classic semaphore decision to crash the server. The ...
1
vote
0answers
132 views

MySQL deadlocking because update using full table scan

I have these jobs that run in parallel that call a series of queries on my DB. They're batched so each job is associated with a group of "bar" objects. So in the example below, temp1 is just a ...
1
vote
0answers
48 views

Deadlock During Insert into One Table

I have a multi-threaded application that does an insert into a table using a stored procedure, and it is causing deadlocks in the database. I ran a trace on my database, and the deadlock processes are ...
1
vote
0answers
346 views

How can I simulate deadlock in SQL Server nested transactions?

Actually I would like to find answers for the following situation: Let's say I have a stored procedure (Usp1) which at some moment calls an other stored procedure (usp2). Each of the stored procedures ...
1
vote
0answers
457 views

Modifying Ola Hallengrens index maintenance script for deadlocks

In a SQL Server 2012 Enterprise database without a true maintenance window, I would like to run Ola Hallengrens script to optimize indices. Testing has shown that while indices are reorganized and ...
1
vote
0answers
128 views

Weird deadlock error after switching from MyISAM to InnoDB

I have been told many times that I should switch my MySql tables engine from MyIsam to InnoDB. Finally I did that, only to have to urgently switch it back to MyIsam this morning. Some PHP scripts ...
1
vote
1answer
694 views

deadlock in mariadb: with non-index & fk table

when i try to do an operation concurrently, there exists an deadlock from mariadb show engine innodb status, the deadlock log is as follows ------------------------ LATEST DETECTED DEADLOCK ----------...
1
vote
0answers
48 views

MSSQL Deadlocking preventing DDL statement in different DB from running

I had a user doing a massive SQL data compare from server A to server B involving database X. On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely ...
1
vote
1answer
55 views

Process possessing lock crashes

I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm). So, here is the scenario that I am confused about: M1 places a table level ...
1
vote
0answers
313 views

NOLOCK vs Read Uncommitted

in our environment, reports are running on the subscriber and resulting in deadlocks with MS Replication SPs, which are doing inserts/updates. What's the best way to eliminate the deadlocks? We have ...
1
vote
0answers
402 views

How to resolve Innodb lock

I am trying to run a sitemap generator job for Magento and here is what I get as Innodb status after the query has failed to complete even after several hours. I do not know much about DBA stuff and ...
1
vote
0answers
88 views

Is this query actually working?

This is from show engine innodb status ---TRANSACTION 4FF4, ACTIVE 43350 sec, OS thread id 6232 fetching rows mysql tables in use 2, locked 2 480318 lock struct(s), heap size 46578104, 48647170 row ...
1
vote
1answer
635 views

Help understanding InnoDB Deadlock Detection

I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but ...
1
vote
0answers
98 views

MySQL deadlock in simple table

I'm having a deadlock issue that I don't get. Possibly it's because of the composite index (idSite,timestamp). Maybe it's range locking on it? I have this table: CREATE TABLE `logs` ( `idLog` ...
1
vote
0answers
718 views

MySQL - Preventing lock wait timeout when deleting rows from self

I have a table of around 500k rows (not big data), that I need to query against itself and then delete some of the rows which don't match the filters. At the moment my query looks like this: DELETE ...
1
vote
0answers
108 views

Why does this deadlock occur?

We're using MySQL with the INNODB storage engine. The account table is accessed by two different transactions. We didn't expect a deadlock because only one transaction is holding a lock (2): ---------...
1
vote
0answers
456 views

SQL Server deadlock graph explained

I am testing a scenario or recurrent request that ends up in executing the following stored procedure simultaneously ALTER PROCEDURE [dbo].[clear_class]( @classID [int]) AS BEGIN DELETE FROM [dbo]....