June 5, 2009 at 12:19 pm
Does anyone know if it's possible to tell which record in a table is locked? I can get the name of the resource and the type but is it possible from that to tell which record it is?
For example, someone is updating the Item table and has an exclusive lock. The record that is locked has an ID = 100.
I have a query that selects from the dm_tran_locks that shows me the all the info like the dbname, objectname, index_name, resource_type, etc. But is there a way to get the exact record, in this case, the item that has an ID = 100 somehow? Or is that not possible at all?
Thanks in advance!
June 7, 2009 at 4:12 am
In principle, you could get the page number from the page lock associated with the RID/KEY lock you are interested in, and then use the undocumented DBCC PAGE ('DatabaseName', file_id, page_number, 3) to show the information stored on that page.
The resource_description for the page lock has the form [file_id:page_number] - use those values with DBCC PAGE.
Note that you need to turn trace flag 3604 on for the session to see the output of the command in SSMS - otherwise it goes to the server error log.
As an example:
create table test(a int primary key, b char(5) not null)
begin tran
insert test values (50, 'Hello')
results in this output from DBCC PAGE:
[font="Courier New"]PAGE: (1:126)
BUFFER:
BUF @0x03984300
bpage = 0x050D0000 bhash = 0x00000000 bpageno = (1:126)
bdbid = 2 breferences = 0 bUse1 = 40261
bstat = 0xc0000b blog = 0xbbbbbbbb bnext = 0x00000000
PAGE HEADER:
Page @0x050D0000
m_pageId = (1:126) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 10654 m_indexId (AllocUnitId.idInd) = 4608
Metadata: AllocUnitId = 1297036693380923392
Metadata: PartitionId = 1297036693379874816 Metadata: IndexId = 1
Metadata: ObjectId = 261575970 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 13 m_slotCnt = 1 m_freeCnt = 8078
m_freeData = 128 m_reservedCnt = 0 m_lsn = (21:285:476)
m_xactReserved = 0 m_xdesId = (0:658) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x70 Length 16
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x6274C070
00000000: 10000d00 32000000 48656c6c 6f020000 †....2...Hello...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
a = 50
Slot 0 Column 2 Offset 0x8 Length 5 Length (physical) 5
b = Hello
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (3200c9109984)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[/font]
The highlighting is mine.
Paul
edit: added example output
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2009 at 11:40 am
Awesome, this is exactly what I was looking for! Thank you!
June 8, 2009 at 2:41 pm
No worries, I enjoyed answering the question.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply