Tag Info

Hot answers tagged

24

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 ...


17

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 = ...


15

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, ...


13

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]', ...


12

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 ...


12

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 ...


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

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 ...


11

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 * ...


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

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 ...


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 ...


8

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 ...


8

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

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 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 = ...


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; ...


7

To answer that I have to take a little detour, so bear with me. If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource ...


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 ...


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

With SQL 2008 there is a new feature that you can use for deadlocks and long running queires: extended events. Extended events are low level objects and consume much lesser resources than other methods like profiling/tracing, alerts, etc... For using extended events with deadlocks check out this post by Jonathan Kehayias, a SQL server MVP. For using ...


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 = ...



Only top voted, non community-wiki answers of a minimum length are eligible