Sql-server – Lock addresses and page addresses don’t match

dbcclockingsql-server-2017

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 :

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

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

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

I'm looking for the data in the second line, as it is the line with PageType = 1 (data page)
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)

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  uPUOIJeanMi@yahoo.co
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 😐

Best Answer

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)

Main Question

Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands?

Under the default locking read committed isolation level, shared (S) locks are taken when reading data, and typically (though not always) released soon after the read is completed. Some shared locks are held to the end of the statement.

A MERGE statement modifies data, so it will acquire S or update (U) locks when locating the data to change, which are converted to exclusive (X) locks just before performing the actual modification. Both U and X locks must be held to the end of the transaction.

This is true under all isolation levels except the 'optimistic' snapshot isolation (SI) not - to be confused with versioning read committed, also known as read committed snapshot isolation (RCSI).

Nothing in your question shows a session waiting for an S lock being blocked by a session holding a U lock. These locks are compatible. Any blocking is almost certainly being caused by blocking on a held X lock. This can be a bit tricky to capture when a large number of short-term locks are being taken, converted, and released in a short time interval.

The open_tran_count: 1 on the InsertOrUpdateInverterData command is worth investigating. Although the command hadn't been running very long, you should check that you don't have a containing transaction (in the application or higher-level stored procedure) that is unnecessarily long. Best practice is to keep transactions as short as possible. This may be nothing, but you should definitely check.

Potential solution

As Kin suggested in a comment, you could look to enable a row-versioning isolation level (RCSI or SI) on this database. RCSI is the most often used, since it typically does not require as many application changes. Once enabled, the default read committed isolation level uses row versions instead of taking S locks for reads, so S-X blocking is reduced or eliminated. Some operations (e.g. foreign key checks) still acquire S locks under RCSI.

Be aware though that row versions consume tempdb space, broadly speaking proportional to the rate of change activity and the length of transactions. You will need to test your implementation thoroughly under load to understand and plan for the impact of RCSI (or SI) in your case.

If you want to localize your usage of versioning, rather than enabling it for the whole workload, SI might still be a better choice. By using SI for the read transactions, you will avoid the contention between readers and writers, at the cost of readers seeing the version of the row before any concurrent modification started (more correctly, the read operation under SI will always see the committed state of the row at the time the SI transaction started). There is little or no benefit to using SI for the writing transactions, because write locks will still be taken, and you'll need to handle any write conflicts. Unless that is what you want :)

Note: Unlike RCSI (which once enabled applies to all transactions running at read committed), SI has to be explicitly requested using SET TRANSACTION ISOLATION SNAPSHOT;.

Subtle behaviours that depend on readers blocking writers (including in trigger code!) make testing essential. See my linked article series and Books Online for details. If you do decide on RCSI, be sure to review Data Modifications under Read Committed Snapshot Isolation in particular.

Finally, you should ensure your instance is patched to SQL Server 2008 Service Pack 4.

Best Answer

Related Solutions

SQL Server 2005 – Fix Detected Inconsistency

SQL Server – How to Prevent Massive INSERTs from Blocking SELECTs

Main Question

Potential solution

Related Question