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.
2
votes
3answers
1k 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: ...
2
votes
1answer
288 views
How do you avoid a foreign key constraint creating deadlocks?
So I have two tables that create a deadlock. The application literally do nothing but update two different tables in two different transactions. There is a foreign key constraint (not cascading, just ...
2
votes
2answers
402 views
SQL Server Deadlock Log Entries
We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222.
In the resulting logs we're seeing the following line repeatedly:
"Log Viewer could not ...
2
votes
1answer
160 views
Why my application is blocked infinitely and SQL Server never timeout and kill the deadlock
I have a multi-thread application that can opened more than one database connection to perform database operation. These connections are using the same credential.
Here is a problem, the application ...
2
votes
2answers
247 views
Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008
Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008?
The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
2
votes
2answers
64 views
How to rollback the identity seed after deadlock
Now that's an approximate sequence of operations Im performing:
SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
...
2
votes
1answer
44 views
When and why can this kind of deadlock occur?
------------------------
LATEST DETECTED DEADLOCK
------------------------
130409 0:40:58
*** (1) TRANSACTION:
TRANSACTION 3D61D41F, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT ...
2
votes
1answer
325 views
InnoDB : Deadlock from one reader and one writer
This sort of has me miffed. One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that ...
2
votes
2answers
614 views
Deadlock on insert for a table with an insert trigger that updates column in same triggered table
By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
2
votes
1answer
338 views
mysql replication deadlock slave stopped
I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again.
111128 8:42:24 [Note] Slave I/O ...
2
votes
2answers
677 views
Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction.
This is done in order to "lock ...
2
votes
1answer
352 views
Locking, deadlocking workaround while process data at the same table
My database has a table: tableX.
Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.
At the ...
2
votes
0answers
120 views
Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off
Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related.
E.g.
Query 1 runs for a few seconds
Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
2
votes
0answers
245 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
143 views
Intent Exclusive (IX) Lock questions [closed]
I was experiencing a deadlock so I set up a trace flags as well as profile trace to capture a deadlock. During this exercise I found something interesting. In the profile trace I noticed that "sa" ...
2
votes
1answer
114 views
SSMS Object Explorer Details causes deadlock while job is running
I have a problem with one of my SQL Server jobs. I've found that while the job is running, SQL Server Management Studio locks up. It always locks up while I'm looking at tables and sprocs within the ...
1
vote
2answers
456 views
Is problem with parallelism more common today than before? And what issues can arise from paralellism?
This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and ...
1
vote
2answers
1k views
Deadlock on Insert/Update
Using SQL Server 2008 R2:
I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup.
TABLE Scores:
id INT IDENTITY(1,1),
first_name ...
1
vote
2answers
754 views
How to analyse innodb status on deadlock in insert Query?
I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status.
OS WAIT ARRAY INFO: reservation count 247864, signal count 247470
Mutex ...
1
vote
1answer
524 views
Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X
I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same ...
1
vote
2answers
941 views
How do I determine why my stored procedure is getting deadlocked?
We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition. ...
1
vote
1answer
233 views
Why isn't the objectname and indexname populated in deadlock graph?
I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.
Traceflag 1222 wasn't enabled ...
1
vote
1answer
35 views
MySQL - how can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
1
vote
1answer
99 views
sp_getapplock doubt
We are using sp_getapplock as a transaction level in a procedure which in turn calls another procedure where again we use sp_getapplock with the same resource names. I wanted to know whether this ...
1
vote
2answers
151 views
SQL Server deadlock too long [closed]
I have a really weird problem with a deadlock between a business process and a monitoring job. The both were involved on a deadlock that lasted around 5.5 hours. I don't know why SQL Server took so ...
1
vote
1answer
97 views
How do I understand this deadlock?
I have two threads doing inserts into a table at the same time, causing a deadlock. The interaction with the table is in a new transaction, so I'm pretty confident there is nothing else going on here.
...
1
vote
1answer
780 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
154 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
240 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
0answers
38 views
Concurrent Access causing deadlock on the same table
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
1answer
60 views
dead lock when updating
Update Operation.TrTable
Set RecordId = RecordTABLE.newRecordId
From Operation.TrTable tr
Inner Join (
SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
From ...
1
vote
0answers
105 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
50 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
157 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>
...
0
votes
1answer
39 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 ...
0
votes
1answer
53 views
Understanding Deadlock graph on one table
I do not understand this deadlock graph that we are experiencing on this table (I have had to redact the exact column names) I have not had much exposure to deadlocks outside of reading material and ...
0
votes
1answer
143 views
ORA-04020 on Oracle SE1 (11.2.0.1)
We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit)
Seeing ORA-04020 errors in the logs.
Is going to 11.2.0.2 the only way to resolve the ...
0
votes
1answer
25 views
sql server 2005 deadlock error 3930
I am trying to simulate a solution for a deadlock issue happened on our production server.
Run this to create a table to be used in 2 and 3.
IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL
...
0
votes
1answer
37 views
Pessimistic locking worst case
We are using Java EE. And are making an apllication where in the worst case, aloot of message queue messages willl come form the same User.
Therefore we are looking on Pessimistic, SELECT FOR UPDATE ...
0
votes
3answers
125 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 ...
0
votes
2answers
133 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 ...
0
votes
2answers
121 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 ...
0
votes
1answer
89 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`) ) ...
0
votes
1answer
65 views
Keeping an eye on contention
In SQL Server, I am using the following command to see blocked sessions:
select * from sys.sysprocesses where blocked>0
This is super. The problem is I need to see what block sessions there ...
0
votes
0answers
52 views
Table Constraint causing deadlock
I have a table. When two transactions are inserting a large no of rows into table grid I am getting a deadlock due to INSERT/SELECT. So INSERT query has X lock on clustered index but since i am doing ...
0
votes
1answer
65 views
Deadlock : Behaviour of NOLOCK
We are running into deadlocks when we run concurrent instances of following query :
BEGIN TRAN
IF NOT EXISTS (SELECT TOP 1 * FROM Table1 WITH (NOLOCK) WHERE Col1 = Val1 AND Col2 = Val2)
BEGIN
...
0
votes
0answers
15 views
Oracle transactions deadlock
How to rollback all active transactions on Oracle DB?
I execute query and see 4 transcations in ACTIVE status.
SELECT *
FROM V$TRANSACTION
0
votes
0answers
25 views
SQL Server 2008 R2 database locked [duplicate]
I canceled a query which took too long time. After that, my SQL Server database was locked. After I restarted SQL Server, the mode of my database was "in recovery". Now I can't access my database ...
0
votes
0answers
44 views
Deadlocks in Small Table
Our application is seeing deadlocks while inserting records into a table shortly after installation, while the table is small. These deadlocks eventually go away as the table fills up.
The ...
0
votes
1answer
114 views
How I prevent deadlock occurrence in my application?
I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am ...
