How to check blocking occurrence in Azure Synapse Analytics.



Unlike SQL Server, Azure Synapse Analytics’ locking behaviour operates entirely at table level and above.

All Azure Synapse Analytics locks are table level or higher. The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock.

Reference:Transactions (Azure Synapse Analytics) – SQL Server | Microsoft Docs

Azure Synapse Analytics supports the following four locking modes.

  • Exclusive
  • Shared
  • ExclusiveUpdate
  • SharedUpdate

This article therefore explains how to check which of the four locking modes occurs in which process and at which level.


In conclusion, the following Transact-SQL can be used to check.

  • sys.dm_pdw_waits
  • sys.dm_pdw_exec_requests

Check blocking

Blocking occurrence

First, follow the procedure below to generate blocking.

Login to the server from a tool such as SQL Server Management Studio (SSMS).

Execute a query against the target database.

*In this case, the Delete statement is executed as an example. Also, we can cause blocking by explicitly specifying BEGIN TRANSACTION.




Open a new session and execute the query run in the same manner.




Execute the following queries to check for blocked queries and sessions

select top 100 A.request_id as WaitingRequestId,

A.object_type as LockRequestType,

A.object_name as ObjectLockRequestName,

A.request_time as ObjectLockRequestTime,

B.session_id as BlockingSessionId,

B.request_id as BlockingRequestId

from sys.dm_pdw_waits A, sys.dm_pdw_waits B

where A.object_type = B.object_type and A.object_name = B.object_name and A.state = ‘Queued’ and B.state = ‘Granted’

order by ObjectLockRequestTime asc

Confirm the contents of the blocked query or session from the QID or SID by the above query.

select * from sys.dm_pdw_exec_requests where request_id in (‘QID51304‘)

Confirm the lock mode and lock occurrence level * lock mode can be confirmed from the [type] column and lock occurrence level from the [object_type] column.

select * from sys.dm_pdw_waits


This article explained how to check which process and at which level the four locking modes are occurring.

The following Transact-SQL can be used to check.

  • sys.dm_pdw_waits
  • sys.dm_pdw_exec_requests


Reference:sys.dm_pdw_waits (Transact-SQL) – SQL Server | Microsoft Docs

Reference: sys.dm_pdw_exec_requests (Transact-SQL) – SQL Server | Microsoft Docs