Chapter 1: System Tables

syslocks

master database only

syslocks contains information about active locks, and built dynamically when queried by a user. No updates to syslocks are allowed.

The columns for syslocks are:

Name	Datatype	Description
id	int	Table ID.
dbid	smallint	Database ID.
page	int	Page number.
type	smallint	Type of lock (bit values for the type column are listed in Table 1-13).
spid	smallint	ID of process that holds the lock.
class	varchar(30)	Name of the cursor this lock is associated with, if any.
fid	smallint	The family (coordinating process and its worker processes) to which the lock belongs. fid values are listed in Table 1-14.
context	tinyint	Context type of lock request. context values are listed in Table 1-15.
row	smallint	Row number.
loid	int	Unique lock owner ID.
partitionid	int null	Patition ID.
nodeid	tinyint null	Reserved for future use

Table 1-13 lists the bit representations for the type column.

**Table 1-13: type control bits in the syslocks table**
Decimal	Hex	Status
1	0x1	Exclusive table lock
2	0x2	Shared table lock
3	0x3	Exclusive intent lock
4	0x4	Shared intent lock
5	0x5	Exclusive page lock
6	0x6	Shared page lock
7	0x7	Update page lock
8	0x8	Exclusive row lock
9	0x9	Shared row lock
10	0xA	Update row lock
11	0xB	Shared next key lock
256	0x100	Lock is blocking another process
512	0x200	Demand lock

Table 1-14 lists the values for the fid column:

**Table 1-14: fid column values in the syslocks table**
Value	Interpretation
0	The task represented by the spid is a single task executing a statement in serial.
Nonzero	The task (spid) holding the lock is a member of a family executing a statement in parallel. If the value is equal to the spid, it indicates that the task is the coordinating process in a family executing a query in parallel.

Table 1-15 lists the values for the context column:

**Table 1-15: context column values in the syslocks table**
Value	Interpretation
null	The task holding this lock is either executing a query in serial, or it is a query being executed in parallel in transaction isolation level 1.
0x1	The task holding the lock will hold the lock until the query is complete. A lock’s context may be FAM_DUR (0x1H) when the lock is: A table lock held as part of a parallel query. Held by a worker process at transaction isolation level 3. Held by a worker process in a parallel query and must be held for the duration of the transaction.
0x2	Range lock held by serializable read task.
0x4	Infinity key lock.
0x8	Lock acquired on an index pages of an allpages-locked table.
0x10	Lock on a page or row acquired to delete a row.
0x20	Address lock acquired on an index page during a shrink or split operation.
0x40	Intent lock held by a transaction performing repeatable reads. Valid only for shared intent and exclusive intent locks on data-only-locked tables.