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

1
vote
1answer
101 views

Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?

Firstly 1 thread selecting a huge amount of data from a table. Then, before the SELECT query is not yet finished, another thread deletes data from that table. It causes SELECT query to failed with ...
0
votes
0answers
28 views

Resetting deadlock count statistics

It seems that just restarting the PostgreSQL service doesn't clear / reset the detected deadlock count. How can it be restarted to zero? select version(); PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu,...
2
votes
2answers
121 views

Where to look for possible deadlock causes?

We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues. Just realized we are having deadlocks (68 actually), on database ...
4
votes
1answer
80 views

Prevent Deadlocks in Vendor Databases

We have a Windows Update (WSUS) database that is used by Windows Update (obviously). A couple of times a day we will receive deadlocks on this database caused by the WSUS application running 2 update ...
0
votes
1answer
165 views

Pessimistic locking worst case

We are using Java EE and are making an application where in the worst case, a lot of message queue messages will come form the same User. Therefore, we are looking on Pessimistic, SELECT FOR UPDATE ...
1
vote
1answer
300 views

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 ...
2
votes
1answer
48 views

How to use two actions and a function in an Extended Events Histogram for deadlocks?

I'd like to get a histogram through extended events that shows the count of deadlocks per databasename by date, but I'm hitting two walls: How get a histogram to include two actions How to get the ...
1
vote
1answer
88 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 ...
0
votes
0answers
105 views

How can I avoid this deadlock in MariaDB?

I saw that I got a deadlock in a MariaDB cluster. I have 3 cluster database. What should I do to avoid that? 160211 16:29:02 *** (1) TRANSACTION: TRANSACTION 75AB6B17, ACTIVE 0 sec starting index ...
-4
votes
1answer
43 views

SQL db deadlocks [closed]

could any one please tell me how to monitor SQL db deadlocks How to prevent deadlocks How to cancel deadlocks without restarting the service. really highly appreciate your information
2
votes
0answers
74 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((...
10
votes
2answers
602 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 ...
2
votes
1answer
288 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 ...
11
votes
1answer
6k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
1
vote
0answers
107 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 ...
0
votes
1answer
143 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 ...
4
votes
1answer
106 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 happen?...
1
vote
0answers
56 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
49 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
70 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 17:...
7
votes
2answers
13k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
3
votes
2answers
9k views

How to find out what caused the database(DB2) in a locking state

I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db. IS there any log file getting created if something unexpected ...
6
votes
1answer
2k views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
-1
votes
1answer
689 views

dead lock when updating [closed]

Update Operation.TrTable Set RecordId = RecordTABLE.newRecordId From Operation.TrTable tr Inner Join ( SELECT r.Id AS newRecordId, r.KeyM AS MappingKey From Operation.Record ...
1
vote
1answer
51 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
59 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 ...
2
votes
0answers
85 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
2answers
399 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 ...
0
votes
1answer
290 views

Scale MySQL trigger for thousands of inserts per seconds

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256) A row ...
2
votes
3answers
1k views

How long can Deadlock info hold in innodb status page?

When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp ------------------------ LATEST DETECTED DEADLOCK ------------------------ 121118 17:14:31 Questions Any idea how long Deadlock ...
48
votes
6answers
45k views

What are the main causes of deadlocks and can they be prevented?

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was ...
0
votes
0answers
35 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# & ...
3
votes
2answers
144 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 ...
11
votes
3answers
9k views

Merge statement deadlocking itself

I have the following procedure (SQL Server 2008 R2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set ...
7
votes
1answer
1k 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, ...
0
votes
0answers
97 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
142 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 ...
4
votes
1answer
2k views

MySQL InnoDB Deadlock For 2 simple insert queries

I have a deadlock for this two insert queries: insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, ...
5
votes
1answer
5k views

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction". I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
7
votes
1answer
222 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
3
votes
1answer
96 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
274 views

How do I understand this DeadLock Graph?

I'm attempting to decipher the content of this deadlock graph. Is there some way to view this information in a more visually meaningful way? Manually reading through the XML is hurting my eyes. <...
1
vote
1answer
146 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 ...
8
votes
1answer
834 views

Trace Flag 1222 Not Working?

I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C". On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both servers:...
2
votes
1answer
332 views

Deadlock occurring on database, SQL Server 2008R2

Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ...
3
votes
1answer
107 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
795 views

Why do these concurrent MySQL inserts deadlock?

I am baffled by this deadlock: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 150511 18:42:43 *** (1) TRANSACTION: TRANSACTION B8972CA4, ACTIVE 0 sec inserting mysql ...
3
votes
1answer
189 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
191 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
61 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` ...