sa_diagnostic_blocking table

The sa_diagnostic_blocking table is owned by the dbo user, and records blocking events. If logging of blocking events is enabled, a row is inserted in this table each time a connection is blocked while trying to access a resource. Typically, this is caused by either a table or a row lock. A large number of blocks may indicate that you should examine the concurrency in your application to reduce contention for tables and rows.

There are two versions of this table: sa_diagnostic_blocking, and sa_tmp_diagnostic_blocking.

Columns
Column name Column type Column constraint Table constraints
logging_session_id UNSIGNED INT NOT NULL

Foreign key references sa_diagnostic_cursor.

Foreign key references sa_diagnostic_request.

lock_id UNSIGNED BIGINT NOT NULL
request_id UNSIGNED BIGINT

Foreign key references sa_diagnostic_request.

cursor_id UNSIGNED BIGINT

Foreign key references sa_diagnostic_cursor.

original_table_object_id UNSIGNED BIGINT
rowid UNSIGNED BIGINT
block_time TIMESTAMP NOT NULL
unblock_time TIMESTAMP
blocked_by UNSIGNED INT NOT NULL

logging_session_id   A number uniquely identifying the logging session during which the diagnostic information was gathered.

lock_id   The ID of the lock that caused the blocking if a row or table lock caused the block, otherwise NULL.

request_id   The ID of the request that was blocked if the block did not occur because of a cursor, otherwise NULL. This value corresponds to the ID assigned to the request in sa_diagnostic_request.

cursor_id   The ID of the cursor if the block occurred because of a cursor, otherwise NULL. This value corresponds to the ID assigned to the cursor in sa_diagnostic_cursor.

original_table_object_id   If the block occurred because of a table lock, the ID of the table on which the block occurred, otherwise NULL.

rowid   If the block occurred because of a row lock, the ID of the row on which the block occurred, otherwise NULL.

block_time   The time at which the block occurred.

unblock_time   The time at which the block ended.

blocked_by   The ID of the connection that held the lock, causing the block.

See also