Blocked processes
When a process must wait to acquire a lock, it is a waiter. The other process that already has the lock (and is causing the waiter to wait) is a blocker.
This collection contains rows for both the blocker processes and the waiter processes in the database. If the row is a blocker process, then BLOCKERKPID = 0 and BLOCKERSPID = 0. If the row is a waiter process, then the "BLOCKERxx" columns have information about its immediate blocker.
Waiters being blocked by a blocker can themselves also block other waiters, potentially forming a blocktree involving many processes. Thus, a row can be a waiter and a blocker, in some cases (and can involve different locks).
Many of the columns are similar to ones in MDA_PROCESS and MDA_LOCK.
(KPID, LOCKID)
| Name | Data Type | Description |
|---|---|---|
| INSTANCEID | long | Instance ID; 0 = non-CE |
| KPID | long | Kernel process ID |
| SPID | long | Session process ID |
| LOCKID | long | Lock ID |
| BLOCKERINSTANCEID | long | Blocker instance ID |
| BLOCKERKPID | long | Blocker kernel process ID; 0 = the row is a blocker process |
| BLOCKERSPID | long | Blocker session process ID (SPID); 0 = the row is a blocker process |
| BLOCKERLOCKID | long | Blocker lock ID |
| TABLEDBID | long | Database ID for the locked object |
| TABLEDBNAME | string(32) | Database name for the locked object |
| TABLENAME | string(32) | Name of locked object |
| LOCKTYPE | string(32) | Lock type:
|
| LOCKLEVEL | string(32) | Lock level (ROW, PAGE, TABLE, ADDRESS) |
| PAGENUMBER | long | Page # that is locked if LockType = PAGE |
| ROWNUMBER | long | Row # that is locked if LockType = ROW |
| FAMILYID | long | Session process ID (SPID) of the parent process, if this is a worker process (for parallel query) |
| BATCHID | long | Batch ID |
| DBNAME | string(32) | Database name |
| PROCDBNAME | string(32) | Database name of the current procedure, if applicable |
| PROCNAME | string(32) | Name of the current procedure, if applicable |
| LINENUMBER | long | Line number in the batch |
| WAITEVENTID | long | Wait event ID |
| LOGIN | string(32) | Login name |
| TEMPDBNAME | string(32) | Name of tempdb assigned to this process |
| BLOCKERFAMILYID | long | Blocker's SPID of its parent process, if the blocker is a worker process (for parallel query) |
| BLOCKERBATCHID | long | Blocker batch ID |
| BLOCKERDBNAME | string(32) | Blocker current database name |
| BLOCKERPROCDBNAME | string(32) | Blocker's database name for its current procedure, if applicable |
| BLOCKERPROCNAME | string(32) | Blocker's name of its current procedure, if applicable |
| BLOCKERLINENUMBER | long | Blocker line number in its batch |
| BLOCKERWAITEVENTID | long | Blocker wait event ID (why the blocker is still holding the lock) |
| BLOCKERLOGIN | string(32) | Blocker login name |
| BLOCKERTEMPDBNAME | string(32) | Blocker's name of the tempdb assigned to its process |
| TIMEBLOCKED | long | Duration of time blocked, in seconds |
| BLOCKEDCOUNT | long | Number of processes being blocked, if the row is a blocker process |
| ROOTBLOCKERBLOCKEDCOUNT | long | Total number of processes being blocked in the entire blocktree involving this row |
| MAXTIMEBLOCKED | long | Maximum duration of time blocked in the entire blocktree involving this row |
| LCNAME | string(32) | Logical cluster name, if Cluster Edition |
| HOSTNAME | string(32) | Host name |
| CMD | string(32) | General command type |
| APPNAME | string(32) | Application name |
| STATUS | string(16) | Status of the process |
| CLIENTOSPID | string(32) | Client OS PID |
| CLIENTIPADDR | string(64) | Client IP address |
| CLIENTNAME | string(32) | Client name |
| CLIENTHOSTNAME | string(32) | Client host name |
| CLIENTAPPLNAME | string(32) | Client application name |
| XACTSTARTTIME | time | Start time of the current transaction |
| XACTELAPSEDTIME | long | Elapsed time of the current transaction |
| XACTNAME | string(256) | Transaction name |
| GTRIDXA | string(256) | Binary version of the global transaction ID portion of XID |
| BQUALXA | string(256) | Binary version of the branch qualifier portion of XID |
| BLOCKERLCNAME | string(32) | Blocker logical cluster name, if Cluster Edition |
| BLOCKERHOSTNAME | string(32) | Blocker host name |
| BLOCKERCMD | string(32) | Blocker general command type |
| BLOCKERAPPNAME | string(32) | Blocker application name |
| BLOCKERSTATUS | string(16) | Blocker's status of its process |
| BLOCKERCLIENTOSPID | string(32) | Blocker client OS PID |
| BLOCKERCLIENTIPADDR | string(64) | Blocker client IP address |
| BLOCKERCLIENTNAME | string(32) | Blocker client name |
| BLOCKERCLIENTHOSTNAME | string(32) | Blocker client host name |
| BLOCKERCLIENTAPPLNAME | string(32) | Blocker client application name |
| BLOCKERXACTSTARTTIME | time | Blocker's start time of its current transaction |
| BLOCKERXACTELAPSEDTIME | long | Blocker's elapsed time of its current transaction |
| BLOCKERXACTNAME | string(256) | Blocker transaction name |
| BLOCKERGTRIDXA | string(256) | Blocker's binary version of its global transaction ID portion of XID |
| BLOCKERBQUALXA | string(256) | Blocker's binary version of its branch qualifier portion of XID |
| Attributes: | MDA |
|---|---|
| Minimum database version: | 15.5 |