![]() ![]() Lookup deadlockĪ lookup deadlock occurs when a process needs to look up a value on a row that another process has a lock on. The two transactions wait for each other until the SQL Server finally terminates one. Likewise, when the second query’s delay elapses, it requests a lock on the Customers table, which the first transaction already has. When the 20-second delay from the first query elapses, the first transaction requests a lock on the Suppliers table, which the second transaction already has. When you run the second query, that transaction acquires a lock on the Suppliers table. When you run the first query, the transaction acquires a lock on the Customers table and then starts waiting. To create the deadlock, execute both queries in quick succession. The difference between the first transaction and the second is the order of execution. The second query updates the Suppliers table first, waits for 20 seconds, then updates the Customers table. In SQL Server Management Studio (SSMS), create a new query comprising two update statements with a 20-second delay between them: The SQL Server must terminate one of them.Ĭonsider the following example that uses the WideWorldImportersDW database to simulate an order of operations deadlock. Neither can continue until both processes release a lock before completing. Process B has an exclusive lock on the Orders table and requests an exclusive lock on the Customer table. Process A has an exclusive lock on the Customer table and requests a lock on the Orders table. Say you have a Customer table, an Orders table, and processes A and B. Order of operations deadlockĪn order of operation deadlock is when two processes hold a lock that the other requires. The following section reviews several deadlocks you may encounter and walks you through detecting them in SQL Server. If your query was trying to lock a row in an an index and was blocked, you get a totally different style of address.To avoid and resolve SQL Server deadlocks, you need to know the different types of deadlocks and how they happen. What if you were waiting on a KEY? Key lock waits Example 2: waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id ( Magic hash that you can decode with %%lockres%% if you really want) That’s enough detail on waitresource=PAGE. ![]() I’ve added NOLOCK to the query because while we want a glance at this info, we have no guarantee that it’s the way it was earlier when the blocking happened anyway– we’re guessing, so we may as well do dirty reads.īut woo hoo, it gives me a clean display of the 25 rows which the query was fighting for: Like I said, this is slow even on tiny tables. WHERE sys.fn_PhysLocFormatter ( %%physloc %%) like '(3:70133%' GO Now that we know that the page lock wait was on Sales.OrderLines, we can see all the data in that table on data file = 3 and page number = 70133 with this query: You can use %%physloc%% with sys.fn_PhysLocFormatter in SQL Server 2008 and higher. %%physloc%% is an undocumented piece of magic that will return the physical record locator for every row. But it’s kinda fun, so… since you read this far… let’s talk about %%physloc%%! 1.4) Can I see the data on the page that was locked? But you have to query all the pages in the database, which seems not as awesome against large databases – so I listed the DBCC page method. Note: In SQL Server 2014 and higher, you could also find the object name using the undocumented sys.dm_db_database_page_allocations dynamic management object. So.object_id = 94623380 and si.index_id = 1 Īnd behold, this lock wait was on the PK_Sales_OrderLines index on the Sales.OrderLines table. If your query was waiting on a page level lock, SQL Server gives you the page address.īreaking “PAGE: 6:3:70133” down, we’ve got: Page lock waits Example 1: waitresource=“PAGE: 6:3:70133 " = Database_Id : FileId : PageNumber Here’s a reference on how to decode them.Īll of this information is out there on the internet already in various places, it’s just spread out! I’m going to pull the whole thing together, from DBCC PAGE to hobt_id to the undocumented %%physloc%% and %%lockres%% functions.įirst we’ll talk through PAGE lock waits, then we’ll hit the KEY lock waits. Sometimes there’s more information in the massive monster of XML that you’re scanning through (deadlock graphs have a resource list that help reveal the object and index name), but sometimes there isn’t. If you use SQL Server’s blocked process report or collect deadlock graphs, occasionally you’ll come across things that look like this: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |