SELECT *FROM Sales.SalesOrderHeader a CROSS JOIN Sales.SalesOrderHeader b
The lock acquired by this session can be easily found using the below query
SELECT ResourceName = CASE resource_typerequest_status, * FROM sys.dm_tran_locksWHERE request_session_id = 53
WHEN 'database' THEN DB_NAME(resource_database_id)
WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
ELSE ''
END,request_mode,
Next we can see an Intent Shared(IS) lock on the table level.An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page.This IS lock also make sure that this table will not be modified till the select statement complete the operation.Next you an see a shared lock on page. This is to make sure that the data in the page are not getting modified while reading the data.
Let us see how it will work along with an update statement.
UPDATE Sales.SalesOrderHeader SET status=5 WHERE SalesOrderID=43659
SELECT *
FROM Sales.SalesOrderHeader a WITH (NOLOCK)
CROSS JOIN Sales.SalesOrderHeader b WITH (NOLOCK)
In this case we can see only shared schema lock on the table. It is not taking a shared lock on the page and this lead to a dirty read.The shared schema lock om table level is important to make to sure that the schema of the table is not getting changed while reading the data. Let us try this select statement after the update statement and still the select statement will run without blocking as it is not trying to acquire the shared lock on pages and it also cause for the dirty read.
