How to check blocking occurrence in Azure Synapse Analytics.

IT

Introduction

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.

Conclusion

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.

<Query>

BEGIN TRANSACTION

DELETE FROM myTable;

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

<Query>

BEGIN TRANSACTION

DELETE FROM myTable;

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

Conclusion

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

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

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