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
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 ...
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 ...
1
vote
1answer
58 views

Deadlock scenario, where is this S lock coming from?

I am encountering the below deadlock scenario. Please see the attached object definitions and execution plans. The plans were pulled out of cache from a system where this deadlock does occur. I ...
1
vote
1answer
334 views

table partition in order to solve frequent deadlock issues - how would I know if it will work?

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS ...
1
vote
1answer
885 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, ...
1
vote
1answer
177 views

MySQL Latest Deadlock Explanation

Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a ...
1
vote
2answers
229 views

Changing two publications at the same time causes deadlock in distribution database

I have transactional replication setup. Let's say three databases on server A replicate to corresponding three databases on Server B. The databases are identical (same structure for different ...
1
vote
2answers
70 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 ...
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 ...
1
vote
1answer
243 views

What happens if two mySQL queries both try to lock the same row at the same time?

So this isn't exactly my strong suit, but here's my basic understanding of the situation: If two threads of the same application both try to execute the same query at the same time, mySQL will ...
1
vote
1answer
271 views

Deadlock detected on CREATE TABLE in PostgreSQL

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C. Scripts CREATE TABLE ...
1
vote
3answers
3k views

How to avoid deadlock while updating 2 rows in MySQL

This is a interview problem: There is a perl program that updates the database, and it could run in different processes. One process may execute a transaction like: update row A -> update ...
1
vote
1answer
11k views

Getting deadlock information easily

I know to check for live deadlocks select * from sys.sysprocesses where blocked>0 However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? ...
1
vote
1answer
74 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 ...
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 ...
1
vote
1answer
632 views

InnoDB deadlock with INSERT .. SELECT

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on ...
1
vote
2answers
275 views

Getting a Deadlock,

I have this code : public void saveItems(List<Pair<Item, MapleInventoryType>> items, int id) throws SQLException { PreparedStatement ps = null; PreparedStatement pse = null; ...
1
vote
1answer
219 views

Deadlock while updating rows and inserting new ones

I'm working with SQL Server 2012 Express Edition and I getting this error: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock ...
1
vote
1answer
386 views

TRUNCATE in transaction: dead lock

In a unittest we run this: BEGIN; TRUNCATE table1; TRUNCATE table2; ... UPDATE ...; ROLLBACK; Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and ...
1
vote
1answer
1k views

How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating ...
1
vote
1answer
327 views

DB2 Insert and Deadlocks

My application has 4 different threads doing inserts into the same DB2 table simultaneously. Could this cause a deadlock / lockwait situtation? Theoretically I don't see a problem here; as one ...
1
vote
1answer
940 views

MySQL InnoDB locking on combined UPDATE-JOIN statements

on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors: Statements writing to a table with an auto-increment column after selecting from another table ...
1
vote
1answer
352 views

What would be possible reasons for a DeadLock to occur in Mysql Database?

If some one has a Table structure some thing like this, CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ...
1
vote
1answer
259 views

apache hang on deadlock

i use code like this to prevent deadlock. while($retry<3 and $notsone) { try { $transaction->commit(); $notdone=false; } catch { $transaction->rollback(); $retry++; } } if ($retry==3) { ...
1
vote
1answer
320 views

Update and select concurrently running on same set of rows but different indexes are referred?

In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of ...
1
vote
1answer
39 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
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 ...
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 ...
1
vote
1answer
33 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
2answers
102 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 ...
1
vote
1answer
126 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 ...
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
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 ...
1
vote
0answers
184 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
39 views

Can a critical section cause deadlock in SQL Server? [duplicate]

We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work ...
1
vote
0answers
150 views

SQL server deadlock graph explained

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

InnoDB Deadlocks Involving Currency Transactions

We're dealing with InnoDB deadlocks that are affecting our virtual transaction system. LATEST DETECTED DEADLOCK 150210 20:51:03 *** (1) TRANSACTION: TRANSACTION 0 4240405649, ACTIVE 0 sec, process no ...
1
vote
2answers
324 views

Notification email when deadlock is encountered in Oracle alert log

How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log) Node 1: Mon Sep ...
1
vote
0answers
1k 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 ...
1
vote
0answers
258 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
1
vote
1answer
2k views

Why are these two INSERTs deadlocking? Is it the trigger? What does this deadlock trace 1222 log tell me?

We are seeing intermittent deadlocks in production when receiving multiple simultaneous API requests. Each request basically culminates in an INSERT statement into the same table, which is where we ...
1
vote
0answers
713 views

SELECT + INSERT + Query Cache = MySQL lock up

MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", ...
1
vote
0answers
235 views

Concurrent Access causing deadlock on the same table [closed]

Transaction 1 : SELECT distinct column_id from grid where row_id = @P0 Transaction 2 : INSERT INTO grid (grid_id, row_id, column_id) In this table grid grid_id is primary key clustered. ...
1
vote
0answers
240 views

Why Am I Getting Deadlock - Please Read Trace

can anyone help me? i'm getting deadlock on this trace, i'm using symmetricds and i can't understand why i'm getting a deadlock on a certain table. I'm using sql server 2008 r2, ...
1
vote
1answer
101 views

Appropriate Action for Dead Locks / Blocking

I've setup my databases so that anytime a procedure is blocked for more than 45 seconds the database notifies the DBA email. Is it bad practice to setup a way to auto kill the process that is doing ...
1
vote
0answers
326 views

SQL Server 2008 R2 DeadLock on query Notification

for the first time, I'm running into a deadlock on ...The deadlock graph doesn't help me much except the fact the query notification system seems to responsible. <resource-list> ...
1
vote
1answer
288 views

how to avoid deadlock while updating 2 rows in mysql [duplicate]

This is a interview problem. There is a perl program that updates the database, and it could run in different processes. One process may execute a transaction like: update row A -> update row B -> ...
1
vote
1answer
157 views

Increased server load after InnoDB deadlock

I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, ...
0
votes
1answer
2k views

postgres deadlock without explicit locking

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...