sys.dm_tran_locks - Get value from KEY lock

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sys.dm_tran_locks - Get value from KEY lock

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-10 : 09:31:24

hey all!

how do i get the value of the key that is locked?

i use this simple script:


select	object_name(P.object_id) as TableName, resource_type, 	resource_description
from	sys.dm_tran_locks L
	join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

-- i get this result (a part of it shown) from that script
TableName      	resource_type 	resource_description
PDM_MasterItems		KEY	(ff008e181896)
PDM_MasterItems		KEY	(6a006ff5eb8c)
PDM_MasterItems		KEY	(1d000b52b4df)

so how do i get value that is locked from this?

Thanx!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-03-10 : 14:11:33

Well, since BOL defines this as "a hash of the key columns from the row that is represented by this resource" I don't think you'll be able to determine what the key value is. I'm tempted to think the latter 4 bytes are CHECKSUM or BINARY_CHECKSUM and the first 2 bytes might be a page number, but I don't know for sure.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 10:20:24

well i found a kind of a way to do this.
i deleted a row in a transaction, so that's from where the X lock came.
with every key lock there was also a page lock.

so i used a resource_description value from the page lock row which is in format <file_id>:<page_in_file>
and i used
DBCC TRACEON(3604)
GO
DBCC PAGE('MyDbName', <file_id>, <page_in_file>, 1)

and in the page i looked for the ghost record and looked at it's id value.

if anyone knows of a better way i'm all ears.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 10:25:30

Please post full code so we can mimic the sitation. I am too lazy to write the necessary code.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 10:29:18

a bit later

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 10:44:09

here you go!

another problem about orphaned transactions that has risen from all this is here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98747

basicaly my query from the client is:


begin tran
delete PDM_MasterItems where id = 100
-- here connection goes down so the so the transaction is orphaned

running in SSMS


select object_name(P.object_id) as TableName, resource_type, resource_description
from	 sys.dm_tran_locks L
       join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

/* result:
TableName         resource_type     resource_description
PDM_MasterItems	  PAGE	            1:204
PDM_MasterItems	  KEY	              (6400b740ff6a)
*/

DBCC TRACEON(3604)
GO
DBCC PAGE('MyDbName', 1, 204, 1)

/*
-- relevant output:
-- my ID and markings of a ghost record are in bold red

PAGE: (1:204)
BUFFER:
BUF @0x03434DA8
bpage = 0x31D6C000                   bhash = 0x00000000                   bpageno = (1:204)
bdbid = 18                           breferences = 1                      bUse1 = 40803
bstat = 0x1c0000b                    blog = 0xbbbbbbbb                    bnext = 0x00000000
PAGE HEADER:
Page @0x31D6C000
m_pageId = (1:204)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 89     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043760640                                 
Metadata: PartitionId = 72057594038648832                                 Metadata: IndexId = 1
Metadata: ObjectId = 181575685       m_prevPage = (0:0)                   m_nextPage = (1:115)
pminlen = 96                         m_slotCnt = 17                       m_freeCnt = 1433
m_freeData = 7766                    m_reservedCnt = 0                    m_lsn = (32:697:31)
m_xactReserved = 0                   m_xdesId = (0:1468)                  m_ghostRecCnt = 1
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

DATA:


Slot 0, Offset 0x1d1f, Length 311, DumpStyle BYTE

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x645EDD1F

00000000:   3c006000 64000000 00000000 0083df09 †<.`.d...........         
00000010:   3adc4806 28000000 bb839708 00000000 †:.H.(...........         
00000020:   00000040 00000000 00000840 0f270000 †...@.......@.'..         
00000030:   00000000 00000000 00000000 00000000 †................         
00000040:   3e1d1b01 01000308 0083df09 20637e02 †>........... c~.         
00000050:   11000000 74d3ef64 18c41c01 00000000 †....t..d........         
00000060:   22007050 0080ff15 009d009d 009d00a3 †".pP............         
00000070:   00af00b3 00bf00e9 00e900fd 00fd0001 †................         
00000080:   01030105 010b011d 01210121 0121012b †.........!.!.!.+         
00000090:   01370139 00390031 00300030 00300030 †.7.9.9.1.0.0.0.0         
000000A0:   00300039 00390039 00310030 00300030 †.0.9.9.9.1.0.0.0         
000000B0:   00310039 0039002e 00310030 0030004b †.1.9.9...1.0.0.K         
000000C0:   006f006e 00730074 00720075 006b0074 †.o.n.s.t.r.u.k.t         
000000D0:   0069006f 006e0073 00660072 00650069 †.i.o.n.s.f.r.e.i         
000000E0:   00670061 00620065 00440065 00730063 †.g.a.b.e.D.e.s.c         
000000F0:   00200039 00390031 00300030 00300031 †. .9.9.1.0.0.0.1         
00000100:   00450043 00310031 00310031 00200078 †.E.C.1.1.1.1. .x         
00000110:   00200032 00200078 00200033 00300030 †. .2. .x. .3.0.0         
00000120:   00310078 00320078 00330069 006e0073 †.1.x.2.x.3.i.n.s         
00000130:   00650072 007400††††††††††††††††††††††.e.r.t.                  
*/

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources