1/10/2024 0 Comments Key lock sql server deadlockAt the time of the deadlock the lock was owned by tran 1 and tran 2 was first in line for it before tran 3. Though it says the deadlock victim was waiting on a lock owned by tran 2 this is not actually the case. The deadlock graph I received is as follows. | resource_description | request_status | request_session_id | X | The result of the query against tran_locks immediately before requesting the lock that will cause deadlock shows +-+-+-+-+ WHERE resource_associated_entity_id = (SELECT partition_id ON sys.fn_PhysLocFormatter(T.%% physloc%%) = '(' + RTRIM(resource_description) + ':0)' See what locks are granted just before the deadlock X INT PRIMARY KEY WITH(ALLOW_ROW_LOCKS = OFF),įiller AS CAST('A' AS CHAR(8000)) PERSISTED You then have 15 seconds to start tran 2 and tran 3 in sequence in different connections). You can reproduce it with the following (run the setup and then tran 1. This just means that there was a queue waiting on that lock. UPD: I've filed an item on Microsoft Connect to try to gather authoritative response. The full deadlock definition is available here. How it is even possible and what does it mean? If your query was trying to lock a row in an an index and was blocked, you get a totally different style of address.If I deciphered the following deadlock graph correctly, it looks like two processes (SPIDs: 216 and 209) own the exclusive (X) lock on the very same page: 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. Problem At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. 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? The deadlock appears to be happening on the last line of each query (the inserts). SP2 is waiting for an S mode lock on PKmemberactivity while SP1 holds a conflicting (X) lock. 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. SP1 is waiting for a RangeS-S key lock on the IXfollowmemberidincludes index while SP2 holds a conflicting (X) lock. 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: This is causing the compiler to not pre-sort the values, because it doesnt realize that the values in the CTE are being used for the seek. This is caused by an implicit conversion from nvarchar to bigint. Page lock waits Example 1: waitresource=“PAGE: 6:3:70133 " = Database_Id : FileId : PageNumber It looks like the reason you are getting this issue is that the the index seeks are not ordered. 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 |