syslocks

master database only

Description

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

Columns

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 12-10)

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 12-11.

context

tinyint

Context type of lock request. context values are listed in Table 12-12.

row

smallint

Row number

loid

int

Unique lock owner ID

Table 12-10 lists the bit representations for the type column.

Table 12-10: 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 12-11 lists the values for the fid column:

Table 12-11: 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 12-12 lists the values for the context column:

Table 12-12: 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.

  • The lock is held by a worker process at transaction isolation level 3.

  • The lock is 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.