#View sql deadlock how to#

If you are on these versions I’d suggest enabling Trace flag 1222 in order to write the deadlock traces to the sql error log – or you can create an XE file target for the system health event session that mimics the one that comes with SQL Server 2012 (I’ll create a future post on how to do this).įor SQL Server 2012 and later, I would suggest finding the deadlock in the system health session by using the file target (as opposed to the ring buffer). Unfortunately, there is a bug in the SQL Server 2008 and SQL Server 2008 R2 ring buffer that makes these inaccurate sources of data for deadlock traces. The system health event session is an Extended Events session that is installed by default starting with SQL Server 2008 on. Since a deadlock occurred, we can try collecting it from the system health event session. Session 57 updated successfully, but this occurred after session 62 received a deadlock error and as a result had its transaction rolled back.

#View sql deadlock update#

Let’s perform one more update on session 57: This is the result of us dropping the index. Select CONVERT(XML, b.query_plan) from sys.dm_exec_requests a cross apply sys.dm_exec_text_query_plan(a.plan_handle, a.statement_start_offset, a.statement_end_offset) b where a.session_id = 62Īs you can see the CustomerTest clustered index is being scanned. We can do this by getting the plan handle from sys.dm_exec_requests. Let’s take a look at the execution plan this query is taking. Session 57 is blocking session 62 despite the two sessions updating different rows. Select session_id, blocking_session_id, wait_type, wait_resource from sys.dm_exec_requests where session_id = 62 We’ll look at the output of sys.dm_exec_requests to see why this session is hanging. In this example we will perform a similar update however, we will be updating 3 different rows: AW00012594, AW00012595, AW00012598. The index was on the CustomerTest.AccountNumber column.

To start with, I’m going to drop an index on the table we were using in our previous example. If you’re new to resolving deadlocks, this can seem a bit difficult to grasp because typically when this occurs the two processes performing updates are not even updating the same rows. I see this scenario occur quite frequently.

#View sql deadlock full#

Two or more sessions are performing full scans and locking pages Since this scenario isn’t that common I’ll skip details on fixing this and move on to the next scenario where I will provide more details. How often does this scenario occur? Not very often in my experience. To prevent an indefinite hang SQL Server will rollback one of the sessions when it detects this scenario. The reason this occurs is SQL Server detected two sessions were competing for the same locks which would cause the two sessions to hang indefinitely. The above statement also completes without issue.Īt this point SESSION 63 will hang because it is waiting on SESSION 60 to commit or rollback the transaction on AccountNumber AW00012597.Īt this point SESSION 63 will receive a deadlock error. The above statement completes without issue. In this example I will show how two records (AccountNumber AW00012597 and AW00012596) are updated by two different sessions which results in a deadlock. This is pretty much the textbook explanation of a deadlock.

Two sessions attempting to lock same rows in different order Overly restrictive transaction isolation levels.Two or more sessions are performing full scans and locking pages.Two sessions are attempting to lock the same rows but in different orders.There are several different scenarios I see that cause deadlocks: What is a deadlock?Ī deadlock is most easily described as what occurs when two sessions request a resource that the other session has, but each session is already holding a resource that the other session has a lock on. I’m not going to go into every single detail of reading the deadlock graph however, I am going to show you what you need to look for to quickly get to a fix. I’ll also explain how you can fix the most commonly occurring ones. I will also show the three most common scenarios that cause them. In this post I will explain what a deadlock is.

I consider this to be a hack for a real problem that can be addressed in most cases. In a lot of cases the DBA isn’t totally aware of them because applications use retry logic for deadlocked transactions.

A common problem I see in systems is deadlocks.