Simulating locking in page level

,

Through this script, you will have the ability to know at run time, how Microsoft SQL Server performs data page locks or even rows from a table. To simulate this behavior, the code shared in this script makes use of a small environment composed of the paglocktable table, this being the storage source of the small portion of data.
Your order of execution is defined as follows:
1- Creation of table paglocktable;
2- Inserting a small portion of data;
3- Query the data entered in the table;
4- Simulation and use of the Paglock table hint, through the use of a new transaction defined at run time through the Begin transaction command, setting the TPL name for the transaction block;
5- Opening a new Query, to execute the Select command while the previously created TPL transaction is running;
6- After the 10-second processing period of the TPL transaction, Microsoft SQL Server must present the result of the data requested in step 5;
7- In parallel to the execution of steps 5 and 6, if you want to identify what is being blocked at the level of data pages or line run in another query the code blocks named:
-- Identify locks at level data pages --
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name,
             i.name as index_name,
page_lock_count,
page_lock_wait_count,
CAST(100. * page_lock_wait_count / NULLIF(page_lock_count,0) AS decimal(6,2)) AS page_block_pct,
page_lock_wait_in_ms, 
CAST(1. * page_lock_wait_in_ms / NULLIF(page_lock_wait_count,0) AS decimal(12,2)) AS page_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios  INNER JOIN sys.indexes i 
                                                                              ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC
Go

Or 

-- Identify locks at line level --

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name,
             i.name as index_name,
row_lock_count,
row_lock_wait_count,
CAST(1. * row_lock_wait_in_ms / NULLIF(row_lock_wait_count,0) AS decimal(12,2)) AS row_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios  INNER JOIN sys.indexes i 
                                                                              ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + row_lock_wait_count DESC, row_lock_count + row_lock_count DESC
Go
-- Creating the Table PagLockTable--
Create Table PagLockTable 
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
 Valores Varchar(20))
Go

-- Inserting a small portion of data --
Insert Into  PagLockTable
Values ('Pedro'), ('Antonio'), ('Galvão'), ('Junior'), 
       ('MVP'), ('MCC'), ('MSTC'), ('MIE'), ('SQL Server'),
       ('Banco de Dados'),('Table Hint PagLock')
Go

-- Querying the data --
Select Codigo, Valores From PagLockTable
Order By Valores Desc

-- Using Paglock--
Begin Transaction TPL

Update PagLockTable With (PagLock)
Set Valores = 'Novos Valores ' + Valores

-- Forcing a 10 second Delay to generate page-level locking -- 
WaitFor Delay  '00:00:10'

Commit Transaction TPL

-- Open new query and run the Select below, after 10 seconds the data will be presented --
Select * From PagLockTable
Go

Rate

1.5 (2)

Share

Share

Rate

1.5 (2)