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. 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. 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
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |