Hot answers tagged deadlock
19
tracking deadlocks is the easier of the two:
By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.
Write deadlock info to the SQL Server error log:
DBCC TRACEON(-1, 1204, 3605)
Turn it off:
DBCC TRACEOFF(-1, 1204, 3605)
See "Troubleshooting ...
15
The best way would be to use tables you already have.
Create two tables -- table-a, table-b
For a test you can even update the same column with the same information
so you don't affect any real data.
For instance
UPDATE table_a set ID = ID where ID = 100;
Open two sessions to the same database.
On one, run
BEGIN TRAN
update table_a set ID=ID where ID = ...
11
If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation ...
11
No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database. The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to ...
11
my favorite articles to read and learn about deadlocks are:
Simple Talk - Track down deadlocks
and
SQL Server Central - Using Profiler to resolve deadlocks.
They will give you samples and advices about how to handle suck a situation.
In short, to solve a current problem, I'd make the transactions involved shorter, take out the unneeded part out of them, ...
11
The data you need is recorded in the default extended events trace.
DECLARE @xml XML
SELECT @xml = target_data
FROM sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
SELECT CAST(XEventData.XEvent.value('(data/value)[1]', ...
11
This exact issue was just announced on Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2. It is linked from Cumulative update package 4 for SQL Server 2008 R2 SP2.
It finally pays off to read SQL Server fix descriptions.
10
In the deadlock graph XML you will see something like:
<deadlock-list>
<deadlock victim="...">
<process-list>
<process id="..." ... waitresource="X:..."
...
The X is the interesting bit, possible values you are interested in are:
RID for row id (row level locking)
PAG for page level lock
OBJECT (which may be further ...
10
In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby ...
10
It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password.
I was able to reproduce this as follows
CREATE TABLE T(X varchar(1000))
Connection 1
BEGIN TRAN
INSERT INTO T VALUES('password1')
WAITFOR DELAY '00:01:00'
SELECT * ...
9
Sometimes a deadlock can be solved by adding indexing, as it allows the database to lock individual records rather than the whole table, so you reduce contention and the possibility of things getting jammed up.
For example, in InnoDB :
If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every ...
9
It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table.
The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess ...
7
You can configure alerts for both of these with SQL Agent. Create a new alert and select type "SQL Server performance condition alert"
For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go.
For deadlocks, the ...
7
Three things leap out:
Your DELETE is on the 2nd column (RespondentID) of the current PK which means a scan, not a seek.
Pointless ROWLOCK hint
Your "UPSERT" pattern is not concurrency safe. The test for existence may pass for 2 overlapping (in time) concurrent threads giving an error.
To fix
Reverse your PK order in DEXTable to (RespondentID, ...
7
There would not be a problem if the table variable only ever held one value. With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company.
Process A reads the destination, finds no row, and inserts the value '1'. It holds an exclusive ...
7
The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:
CREATE TABLE dbo.LockTest
(
col1 ...
7
Use of the XLOCK hint on either your SELECT approach or the following UPDATE should be immune to this type of deadlock:
DECLARE @Output TABLE ([NewId] INT);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE
dbo.tblIDs WITH (XLOCK)
SET
LastID = LastID + 1
OUTPUT
INSERTED.[LastId] INTO @Output
WHERE
IDName = @IDName;
...
6
Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.
How To Track Down Deadlocks in SQL Server using Profiler
Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.
6
This looks like a typical case of table scan (possible missing indexes, but could be more). The SELECT has chosen a page lock granularity, indicative of a large scan (on blocks table). Also note how all the locks are on the same resource rowset, the clustered index, another indication that the SELECT does not use a selective secondary index to locate the ...
6
You are dealing with a deadlock, not a performance bottleneck issue.
If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.
Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in ...
6
On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:
a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index
The SELECT accesses the nonclustered index first, then the clustered index.
...
6
The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock.
The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock.
The execution plan for the UPDATE doesn't feature Accounts at all so there is no ...
6
T1 SPID 93 processec49b8: UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2 triggers INSERT INTO ZAT_KID with (PAGLOCK)
T2 SPID 64 processedb6d8: insert into Image ... triggers INSERT INTO ZAT_Image with (PAGLOCK)
T1 wants X page 295182 (ZAT_KID) and has X page 295211 (ZAT_Image). T2 wants X page 295211 (ZAT_Image) and has X on 295182 ...
6
The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below):
CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
TRANSITION_UUID,
STATUS,
ACTION_SEQ
);
The first query is:
UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED',
CLOSED = ...
5
I don't expect you to mark this post as an answer but sharing more information here by other SQL Server experts on this topic.
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx
http://rusanu.com/2009/05/16/readwrite-deadlock/
5
Here is what I am seeing
I see three queries, all almost identical.
UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = ...
5
Deadlocks are a fact of life on all databases, and Oracle is no exception. There is no magic that can be done in this situation - it is a fundamental consequence of concurrency (letting multiple users access the data at the same time without harming integrity):
create table t(id integer primary key);
--session 1
insert into t(id) values(1);
--session 2:
...
5
Deadlock Detection and Resolution Issues has been around as long as RDBMSs have. Even though Oracle owns InnoDB, do not expect Oracle to fix InnoDB. Most applications are to blame for deadlocks, not so much the RDBMS. Regardless of Oracle, MySQL or any other RDBMS, a Deadlock Error can rear its ugly head.
Oracle acquired InnoDB October 7, 2005 when the ...
5
This particular deadlock is being caused by attempting to read from your v3_cam_date table for inserting rows into your usertmp table while another thread is updating the v3_cam_date table. Indicated by this statement:
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY ','
This documentation page ...
5
Deadlocks will always happen at some point under the default locking strategy. However, it is unlikely in your given scenario because of the straight table scan.
However, it's more likely if you have several concurrent INSERTs and SELECTs.
Using NOLOCK means dirty reads and isn't best practice. Everyone seems to suggest them though...
The alternative is ...
Only top voted, non community-wiki answers of a minimum length are eligible
