How to Fix a Deadlock Caused by Open

How to fix a deadlock caused by open

You're not catching all exceptions here. When nothing is specified after rescue, it means that you're catching StandardError which is not at the root of Exceptions' hierarchy.

If you want to make sure you're catching all exceptions and retry opening a URL (or whatever behavior you'd like), what you want to do is:

rescue Exception => error

Getting DeadLock in SQL Server even after restarting server

Transaction (Process ID 67) was deadlocked on lock | communication buffer resources

This is probably caused by a bug while running a parallel query. Upgrade SQL Server to a later version, or disable parallelism for this query or the whole server.

How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(2), locks key(1);

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(1), locks key(2);

it will be impossible to get a deadlock.

So this is what I suggest:

1. Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.

2. Fix your delete statement to work in ascending order:

Change

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

To

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

How to deliberately cause a deadlock?

Here's some T-SQL to deliberately cause a deadlock.

Object creation:

CREATE TABLE dbo.DeadLockTest (col1 INT)
INSERT dbo.DeadLockTest SELECT 1

CREATE TABLE dbo.DeadLockTest2 (col1 INT)
INSERT dbo.DeadLockTest2 SELECT 1

Open up a new query window and paste this code and execute it:

BEGIN TRAN
UPDATE dbo.DeadLockTest SET col1 = 1

Open up another new query window and paste and execute this code:

BEGIN TRAN
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE dbo.DeadLockTest SET col1 = 1

Go back to your first query window (with the first BEGIN TRAN statement) and execute this code:

UPDATE dbo.DeadLockTest2 SET col1 = 1

Voila! That's a deadlock.

How to catch SqlException caused by deadlock?

The Microsft SQL Server-specific error code for a deadlock is 1205 so you'd need to handle the SqlException and check for that. So, e.g. if for all other types of SqlException you want the bubble the exception up:

catch (SqlException ex)
{
    if (ex.Number == 1205)
    {
        // Deadlock 
    }
    else
        throw;
}

Or, using exception filtering available in C# 6

catch (SqlException ex) when (ex.Number == 1205)
{
    // Deadlock 
}

A handy thing to do to find the actual SQL error code for a given message, is to look in sys.messages in SQL Server.

e.g.

SELECT * FROM sys.messages WHERE text LIKE '%deadlock%' AND language_id=1033

An alternative way to handle deadlocks (from SQL Server 2005 and above), is to do it within a stored procedure using the TRY...CATCH support:

BEGIN TRY
    -- some sql statements
END TRY
BEGIN CATCH
    IF (ERROR_NUMBER() = 1205)
        -- is a deadlock
    ELSE
        -- is not a deadlock
END CATCH

There's a full example here in MSDN of how to implement deadlock retry logic purely within SQL.

How is a deadlock possible on a SELECT

It is possible for a SELECT to cause a deadlock if someone else is using the table.

This example is ripped almost 100% from Brent Ozar's video on deadlocks, but changed one command to a SELECT.

To start with, create two tables

CREATE TABLE Lefty (ID int PRIMARY KEY)
CREATE TABLE Righty (ID int PRIMARY KEY)
INSERT INTO Lefty (ID)  VALUES (1)
INSERT INTO Righty (ID) VALUES (2)

Then open two windows in SSMS. In the first put this code (don't run it yet)

BEGIN TRAN
UPDATE Lefty SET ID = 5

SELECT * FROM Righty
COMMIT TRAN

In the second window put in this code (also don't run it yet).

BEGIN TRAN
UPDATE Righty SET ID = 5
UPDATE Lefty SET ID = 5
COMMIT TRAN

Now, in the first window, run the first two commands (BEGIN TRAN AND UPDATE LEFTY).
That starts.

In the second window, run the whole transaction. It sits there waiting for your first window, and will wait forever.

In the first window, go back and run the SELECT * FROM Righty and COMMIT TRAN. 5, 4, 3, 2, 1 Boom deadlock - because the second window already had a lock on the table and therefore the SELECT in the first window couldn't run (and the second window couldn't run because the first had a lock on a table it needed).

(I'd like to reiterate - this is Brent Ozar's demo not mine! I'm just passing it on. Indeed, I recommend them).

How can I clear a transaction deadlock?

Given some 'innodb status' output like this:

---TRANSACTION 0 0, not started, process no 1024, OS thread id 140386055603968
MySQL thread id 197, query id 771 localhost marc
show innodb status

you'd want to do

KILL QUERY 771

to kill one of the two queries that are deadlocked. That'll kill the query, but leave the connection open. if you want to kill the connection, then you'd do KILL 197.

sql server 2005 deadlock times out in production, not in test environment: why?

The explanation of why the JDBc connection enters the incorrect state is given here: The server failed to resume the transaction... Why?. You should upgrade to JDBC SQL driver v2.0 before anything else. The link also contains advice on how to fix the application processing to avoid this situation, most importantly about avoiding the mix of JDBC transaction API with native Transact-SQL transactions.

As for the deadlock repro: you did not recreate a deadlock in test. You just blocked waiting for a transaction to commit. A deadlock is a different thing and SQL Server will choose a victim, you do not have to set deadlock priority, lock timeouts or anything. Deadlock priorities are a completely different topic and are used to choose the victim in certain scenarios like high priority vs. low priority overnight batch processing.

Any deadlock investigation should start with understanding the deadlock, if you want to eliminate it. The Dedlock Graph Event Class in Profiler is the perfect starting point. With the deadlock graph info you can see what resources is the deadlock occuring on and what statements are involved. Most times the solution is either to fix the order of updates in application (always follow the same order) or fix the access path (ie. add an index).

Update

• The UPDATE .. WHERE key IN (SELECT ...) is usually deadlocking because the operation is not atomic. Multiple threads can return the the same IN list because the SELECT part does not lock anything. This is just a guess, to properly validate you must look at the deadlock info.

• To validate your hand made test for deadlocks you should validate that the blocking SPIDs form a loop. Look at SELECT session_id, blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0. If the result contains a loop (eg. A blocked by B and B blocked by A) adn the server does not trigger a deadlock, that's a bug. However, what you will find is that the blocking list will not form a loop, will be something A blocked by B and B blocked by C and C not in the list, which means you have done something wrong in the repro test.

---

Related Topics