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.

learn more… | top users | synonyms

0
votes
0answers
9 views

How do Locks Work in MySQL [on hold]

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 ...
2
votes
0answers
47 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*/ ...
0
votes
0answers
14 views

Deadlock on Postgres 9.3 Sharelock executing same function at same time

Postgres 9.3 Debian 7 I'm developing a gambling website (in alfa now) where you bet credits (I call coins) in card games. There's an unique pg function to add/subtract user coins that updates a ...
9
votes
2answers
180 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
1
vote
0answers
36 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 ...
4
votes
1answer
46 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
0
votes
1answer
102 views

SQL Server 2012 - Deadlock happening daily

We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour. The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted ...
1
vote
0answers
35 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 ...
0
votes
1answer
29 views

What does this deadlock report from MySQL mean?

Here is the output: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-12-23 05:57:47 7f23d73c6700 *** (1) TRANSACTION: TRANSACTION 32990178, ACTIVE 0 sec inserting mysql ...
0
votes
0answers
33 views

mariadb error transaction deadlock

I use galera cluster with 4 nodes, mariadb, we insert nearly 12million data. Insert task was finished. However we have lost 11000 data. I don't know where they go. In error log it writes; 151220 ...
9
votes
0answers
107 views

SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert

I have been working on this deadlock issue for quite a few days now and no matter what I do, it persists in one way or another. First, the general premise: We have Visits with VisitItems in a one to ...
1
vote
1answer
32 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 ...
0
votes
1answer
50 views

Can someone decipher this deadlock for me?

We don't have a real DBA in our shop at this time, I'm filling in the best I can. It looks like the INSERT has taken place first and is holding a lock the SELECT FOR UPDATE needs. But the INSERT is ...
1
vote
2answers
107 views

How to force SQL server to use row locking for specific update/delete statements

I read this great response to a similar question How to force the use of row locks but I was looking for a per update/delete statement instead of permanently putting the rowlock restriction on an ...
2
votes
0answers
81 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 ...
1
vote
1answer
98 views

Excessive page locking

There is a query: SELECT entry_id FROM table_name WHERE (path_id = 7 AND value_string = N'00020003-0004-0005-0006-000700080009' ) The table in this query contains some millions of records. Both ...
0
votes
0answers
34 views

How system resolves when particular resource lock at uncommitted transaction

Let us assume Transaction T1#: Initiated on 2015-10-30 10:25:41.380 Transaction T2#: Initiated on 2015-10-30 10:25:41.580 The above two transactions are initiated through application. T1# & ...
1
vote
2answers
99 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 ...
3
votes
2answers
65 views

How to handle lock information?

Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks. There is a high accordance between the tables (and views) affected by the deadlocks and the tables being ...
1
vote
1answer
125 views

MYSQL - FORCE INDEX ON A DELETE

UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp <= 1445255546 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space ...
0
votes
1answer
119 views

Debugging lock time out with show engine innodb status

I am keeping getting this lock wait time out. Below is my current settings. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to ...
1
vote
0answers
56 views

How to avoid deadlocks when running queries on a remote server

I need to pull data from many different systems, all on remote, LIVE SQL Servers into one place for a one-off piece of work that will last a few weeks. This has to be run multiple times during the ...
1
vote
2answers
69 views

How many deadlocks am i really getting? system health or deadlocks/sec?

I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours. I assume these are all different ...
0
votes
1answer
93 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 ...
7
votes
1answer
306 views

Meaning of 'locks rec but not gap waiting' in deadlock report

About the meaning of locks rec but not gap waitingin TRANSACTION(1), which one is correct? Already granted gap lock, waiting for clustered index X lock? Already granted clustered index X lock, ...
3
votes
1answer
82 views

Merge causing deadlocks

We are running MS SQL Server 2008 R2. We've got a VendorMaster table (with about 8700 rows) that we are trying to keep in sync across two databases using a Merge statement. The merge code runs every ...
2
votes
1answer
138 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
2
votes
1answer
77 views

Extended Events XML querie to show relevant info about deadlocks - SQL Server 2014

While trying to get together information about deadlocks I came across this link which asks a question regarding a trace: Interpretation of a column in SQL Server profile trace The answer dealt with ...
1
vote
1answer
71 views

DISABLE / DROP INDEX causing deadlock

Rather urgent one. Have created an index on one of our tables and for some reason all data going into that table has stopped. Can't see any blocking, but when I'm trying to drop or disable the ...
2
votes
1answer
50 views

what are the costs of enabling extended events?

I have been dealing with deadlocks, specifically getting their information, as you can see on this post here. On this same post Shanky recommended "You must now rely on extended events trace to ...
1
vote
1answer
34 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is ...
3
votes
1answer
59 views

sql server deadlock error - Shared (S) and Intent-Exclusive (IX) locks under Serializable isolation

Deadlock errors such as below happen frequently on our 3rd party vendor database: <?xml version="1.0" encoding="utf-8" ?> - <deadlock-list> - <deadlock victim="process743288"> ...
3
votes
1answer
134 views

Can a Table Lock eliminate a deadlock or make it worse Oracle

We have a situation in which an Oracle Database is having deadlocks issues. I said that perhaps we should lock into some table locks, and a guys response to me was "Unless I am misunderstanding ...
3
votes
0answers
103 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 ...
1
vote
0answers
57 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
55 views

mysql deadlock issues

we are having starnge deadlock issue. We have a deadlock in mysql and it appears conitously when we run engine innodb command. the webserver it is comming from do not exist as we have manually killed ...
5
votes
0answers
88 views

Complete deadlock on PostgreSQL 9.3 after removing a numeric id

The problem - complete DB lock after a schema change Architecture I use a PostgreSQL 9.3 with PostGIS to store location updates. The updates are written to two tables - location_updates and ...
13
votes
2answers
301 views

Simultaneous calls to the same function: how are deadlocks occurring?

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not ...
1
vote
1answer
56 views

Clarification on mysql deadlock situation

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that 1) Transaction (2) holds exclusive ...
6
votes
4answers
2k views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
-1
votes
2answers
803 views

Deadlock from select statements

I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue? Deadlocked processes: 11278 Victim process: 5185 Lock type: Index/(Key) Both ...
8
votes
1answer
161 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
2
votes
1answer
153 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 ...
2
votes
0answers
108 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 ...
1
vote
0answers
183 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 ...
2
votes
0answers
27 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 ...
3
votes
1answer
111 views

Understanding & Resolving Deadlock in SQL Server 2008 R2

I am trying hard to figure out what may be going wrong for below shown deadlock graph: I am receiving alerts for deadlocks similar to above for almost every 10 minutes, it's just the query changes, ...
0
votes
1answer
39 views

Is it possible to have a deadlock in this situation

I am working with SqlServer and Java. I have the following (pseudo) code : begin transaction For each item of a list select the row in db ... update the row End for end transaction ...
0
votes
0answers
223 views

Deadlocks due to foreign keys (MySQL)

We are in the process of creating a web application using mysql as a data source. We are using spring with hibernate as an ORM framework. We have hundreds of entities with relations (manyToMany \ ...
1
vote
1answer
149 views

How does the table scan in an UPDATE WHERE predicated on an unindexed column work?

I was looking into a deadlock caused by two concurrent UPDATE statements: UPDATE [table] SET [column] = 0 WHERE [unindexed_column] = @id My understanding is that because the column the WHERE is ...