Lock addresses and page addresses don't match

Question

Today, I discovered the command DBCC PAGE(). I understand that it displays information about the data stored at the page level.

(This question is about SQL Server 2017 - v14.0)

So this is what I do :

1. I want to get data from my table named user
2. I access page number and file number of this table by using DBCC IND()

DBCC IND('gescom', 'user', -1)

3. The result of the previous command is :

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
4       8           NULL   NULL        917578307   0           1               72057594043105280    In-row data          10       NULL       0           0           0           0
5       8           4      8           917578307   0           1               72057594043105280    In-row data          1        0          0           0           0           0

4. I'm looking for the data in the second line, as it is the line with PageType = 1 (data page)
5. So I use the DBCC PAGE command with the following parameters : database = 'gescom', file num = 5, page num = 8, display option = 3

DBCC TRACEON(3604) -- Activate display trace
DBCC PAGE('gescom', 5, 8, 3)

6. Result of the previous command is (truncated result...) :

Page @0x0000018E2D3D6000
[...]
Memory Dump @0x0000005DD33FA000

0000005DD33FA000:   01010000 08020001 00000000 00001400 00000000  ....................
0000005DD33FA014:   00000400 b1000000 8a1e3f03 08000000 05000000  ....±....?.........
0000005DD33FA028:   26000000 b00f0000 6b000000 2a100000 00000000  &...°...k...*.......
0000005DD33FA03C:   70954bd5 00000000 00000000 00000000 00000000  pKÕ................
0000005DD33FA050:   00000000 00000000 00000000 00000000 30001400  ................0...
0000005DD33FA064:   b6ec768b aadc3c49 9ef64911 a2bb0766 04000003  ¶ìvªÜ<IöI.¢».f....
0000005DD33FA078:   00290031 0041004a 65616e4d 69636865 6c427570  .).1.A.JeanMichelBup
0000005DD33FA08C:   7550554f 494a6561 6e4d6940 7961686f 6f2e636f  [email protected]
0000005DD33FA0A0:   6d300014 0076cf59 d2c044d1 49866422 8f4afddf  m0...vÏYÒÀDÑId".Jýß

Then I finally try to update this data:

BEGIN TRAN T1

UPDATE [USER] 
SET pseudo = 'newPseudo' 
WHERE email LIKE '%Mi%'

Then, I want to see on which address the lock have been set so I run the following query while I don't end the previous transaction :

SELECT * FROM sys.dm_tran_locks

Here it's the result (look at the column lock_owner_address) :

resource_type                                                resource_subtype                                             resource_database_id resource_description                                                                                                                                                                                                                                             resource_associated_entity_id resource_lock_partition request_mode                                                 request_type                                                 request_status                                               request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type                                           request_owner_id     request_owner_guid                   request_owner_lockspace_id       lock_owner_address
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------
DATABASE                                                                                                                  5                                                                                                                                                                                                                                                                                     0                             0                       S                                                            LOCK                                                         GRANT                                                        1                       0                57                 0                       0                  SHARED_TRANSACTION_WORKSPACE                                 0                    00000000-0000-0000-0000-000000000000 0x0000018E3B366130:0:0           0x0000018E36E1AE80
PAGE                                                                                                                      5                    5:8                                                                                                                                                                                                                                                              72057594043105280             0                       IX                                                           LOCK                                                         GRANT                                                        0                       33554432         57                 0                       0                  TRANSACTION                                                  218160               00000000-0000-0000-0000-000000000000 0x0000018E3B366130:1:1           0x0000018E2D61E380
OBJECT                                                                                                                    5                                                                                                                                                                                                                                                                                     917578307                     0                       IX                                                           LOCK                                                         GRANT                                                        1                       33554432         57                 0                       0                  TRANSACTION                                                  218160               00000000-0000-0000-0000-000000000000 0x0000018E3B366130:1:1           0x0000018E2D61D9C0

Here I can't understand why the address of the object lock is different (=0x0000018E2D61D9C0) of the address previously seen with the DBCC PAGE command (= 0000005DD33FA078) ?

I see that the page number match : @0x0000018E2D3D6000 but I can't get why the object lock address don't match any address in the DBCC PAGE result...

Please tell me where I'm doing something wrong :|

Answer 1

I've found the solution. It's quite easier rather than i was thinking.

I'm going to illustrate it for thos who would be interessted in.

BEGIN TRAN T1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
UPDATE [USER] SET nom = 'Vincent' WHERE id = 2

SELECT * FROM sys.dm_tran_locks

Look at the resource_description column on row where resource_type is PAGE

-- Resource_description (result will be different for you of course) : 
-- file_id  :page_in_file
-- 1        :384 
-- 1        :440 

But you gonna ask, why two pages ? This is because one is for the data(384), and the second for the related index (440).

We can check this using the following command :

-- DBCC IND([database_name|database_id|0], table_name, [index_id|index_display_option {-1 for index and Index Allocation Map|-2 for IAM only}]
DBCC IND(0, 'USER', -1)

Look at the result, in the PageType column for rows where PagePID is 384, 440

-- PagePID :PageType
-- 384     :1
-- 440     :2

Now we can just access the data page to see how our data is stored by SQL SERVER. To do that, proceed as folowing.

-- DBCC TRACEON(3604) -- To redirect display in the console (or you'll not displaying anything)
-- DBCC PAGE(database_name, file_num, page_num, display_option : {1|2|3}) [WITH TABLERESULTS] 
DBCC TRACEON(3604)
DBCC PAGE('gescom', 1, 384, 3) WITH TABLERESULTS

Here we are ! This is the page where the lock have been acquired by SQL SERVER ! Just go trough the value columns wich store the data ordered by your own primary key you should found the data that we were looking for !

(Sorry for my hawful english, i'm french)