Locks are not on-disk structures—you won't find a lock field directly on a data page or a table header—because it would be too slow to do disk I/O for locking operations. Locks are internal memory structures—they consume part of the memory used for SQL Server. Each locked data resource (a row, index key, page, or table) requires 64 bytes of memory to keep track of the database, the type of lock, and the information describing the locked resource. Each process holding a lock also must have a lock owner block of 32 bytes. A single transaction can have multiple lock owner blocks; a scrollable cursor sometimes uses several. Also, one lock can have many lock owner blocks, as in the case with a shared lock. Finally, each process waiting for a lock has a lock waiter block of another 32 bytes. Since lock owner blocks and lock waiter blocks have identical structures, I'll use the term lock owner block to refer to both of them.
The lock manager maintains a lock hash table. Lock resources, contained within a lock block, are hashed to determine a target hash slot in the hash table. (I'll discuss hashing in detail when I talk about the SQL Server hash join algorithm in Chapter 15.) All lock blocks that hash to the same slot are chained together from one entry in the hash table. Each lock block contains a 16-byte field that describes the locked resource. This 16-byte description is viewable in the syslockinfo table in the rsc_bin column. I'll dissect that column later in this section. The lock block also contains pointers to lists of lock owner blocks. A lock owner block represents a process that has been granted a lock, is waiting for the lock, or is in the process of converting to the lock. Figure 14-4 shows the general lock architecture.
Figure 14-4. SQL Server's locking architecture.
The number of slots in the hash table is based on the system's physical memory, as shown in Table 14-5. All instances of SQL Server on the same machine will have a hash table with the same number of slots. Each entry in the lock hash table is 16 bytes in size and consists of a pointer to a list of lock blocks and a spinlock to guarantee nonconcurrent access to the same slot.
Table 14-5. Number of slots in the internal lock hash table.
Physical Memory (MB) | Number of Slots | Memory Used |
---|---|---|
< 32 | 2^13 = 8K | 128 KB |
>= 32 and < 64 | 2^15 = 32K | 512 KB |
>= 64 and < 128 | 2^16= 64K | 1 MB |
>= 128 and < 512 | 2^17 = 128K | 2 MB |
>= 512 and < 1024 | 2^18 = 256K | 4 MB |
>= 1024 | 2^19 = 512K | 8 MB |
The lock manager preallocates a number of lock blocks and lock owner blocks at server startup. If the number of locks is fixed by sp_configure, it allocates that configured number of lock blocks and the same number of lock owner blocks. If the number is not fixed (0 means auto-tune), it allocates 500 lock blocks on a SQL Server 2000, Desktop Edition server and 2500 lock blocks for the other editions. It allocates twice as many (2 * # lock blocks) of the lock owner blocks. At their maximum, the static allocations can't consume more than 25 percent of the committed buffer pool size.
NOTE
In this context, I use the term process to refer to a SQL Server subtask. Every user connection is referred to as a process, as are the checkpoint manager, the lazywriter, the log writer, and the lock monitor. But these are only subtasks within SQL Server, not processes from the perspective of the operating system, which considers the entire SQL Server engine to be a single process with multiple threads.
When a request for a lock is made and no free lock blocks remain, the lock manager dynamically allocates new lock blocks instead of denying the lock request. The lock manager cooperates with the global memory manager to negotiate for server allocated memory. Each lock contains a flag indicating whether the block was dynamically allocated or preallocated. When necessary, the lock manager can free the dynamically allocated lock blocks. The lock manager is limited to 60 percent of the Buffer Manager's committed target size allocation to lock blocks and lock owner blocks.
The lock block is the key structure in SQL Server's locking architecture, as shown earlier in Figure 14-4. A lock block contains the following information:
The lock resource block is the most important element of the lock block. Its structure is shown in Figure 14-5. Each "row" in the figure represents 4 bytes, or 32 bits.
Figure 14-5. The structure of a lock resource block.
The meanings of the fields shown in Figure 14-5 are described in Table 14-6. The value in the "resource type" byte is one of the locking resources described earlier in Table 14-3. The meaning of the values in the three data fields varies depending on the type of resource being described. SR indicates a subresource (which I'll describe shortly).
Table 14-6. The meaning of fields in the lock resource block.
Resource Type | Resource Contents | ||
---|---|---|---|
Data 1 | Data 2 | Data 3 | |
Database (2) | SR | 0 | 0 |
File (3) | File ID | 0 | 0 |
Index (4) | Object ID | SR | Index ID |
Table (5) | Object ID | SR | 0 |
Page (6) | Page Number | 0 | |
Key (7)(For a Key resource, Data2 is 2 bytes and Data3 is 6 bytes) | Object ID | Index ID | Hashed Key |
Extent (8) | Extent ID | 0 | |
RID (9) | RID | 0 | |
Application (10) Application (For an application resource name resource, only the first 4 bytes of the name are used; the remaining bytes are hashed.) | Application resource name |
Following are some of the possible SR (SubResouce) values:
Each lock owned or waited for by a session is represented in a lock owner block. Lists of lock owner blocks form the grant, convert, and wait lists that hang off of the lock blocks. Each lock owner block for a granted lock is linked with all other lock owner blocks for the same transaction or session so that they can be freed as appropriate when the transaction or session ends.
The system procedures sp_lock and sp_lock2 are based on information extracted from the syslockinfo table, which exists only in the master database. Lots more information, including the name of the lock owner, is kept in the syslockinfo table. Table 14-7 shows the columns in that table. Columns prefixed by rsc_ are taken from a lock resource block. Columns prefixed by req_ are taken from a lock owner block.
Table 14-7. Columns in the syslockinfo table.
Column Name | Description |
---|---|
rsc_text | Textual description of a lock resource. Contains a portion of the resource name. |
rsc_bin | Binary lock resource. Described in Figure 14-5 and Table 14-6. |
rsc_valblk | Lock value block. Some resource types might include additional data in the lock resource that is not hashed by the lock manager to determine ownership of a particular lock resource. For example, page locks are not owned by a particular object ID. For lock escalation and other purposes, however, the object ID of a page lock might be placed in the lock value block. |
rsc_dbid | Database ID. |
rsc_indid | Index ID associated with the resource, if appropriate. |
rsc_objid | Object ID associated with the resource, if appropriate. |
rsc_type | Resource type from Table 14-3. |
rsc_flag | Internal resource flags. |
req_mode | Lock request mode. This column is the lock mode of the requester and represents either the granted mode or the convert or waiting mode. Can be any of the modes listed in Tables 14-1 or 14-2. |
req_status | Status of the lock request. 1 = Granted 2 = Converting 3 = Waiting |
req_refcnt | Lock reference count. Each time a transaction asks for a lock on a particular resource, a reference count is incremented. The lock cannot be released until the reference count equals 0. |
req_cryrefcnt | Reserved for future use. Always set to 0. |
req_lifetime | Lock lifetime bitmap. During certain query processing strategies, locks must be maintained on resources until the query processor has completed a particular phase of the query. The lock lifetime bitmap is used by the query processor and the transaction manager to denote groups of locks that can be released when a certain phase of a query is completed. Certain bits in the bitmap are used to denote locks that are held until the end of a transaction, even if their reference count equals 0. |
req_spid | Process ID of the session requesting the lock. |
req_ecid | Execution context ID (ECID). Used to denote which thread in a parallel operation owns a particular lock. |
req_ownertype | Type of object associated with the lock. Can be one of the following: 1 = Transaction 2 = Cursor 3 = Session 4 = ExSession Note that 3 and 4 represent a special version of session locks that track database and filegroup locks, respectively. |
req_transactionID | Unique transaction ID used in syslockinfo and in SQL Profiler events. |
req_transactionUOW | Identifies the Unit of Work (UOW) ID of the DTC transaction. For non-MS DTC transactions, UOW is set to 0. |
In syslockinfo, you can analyze the rsc_bin field as the resource block. Here's an example:
SELECT rsc_bin FROM master..syslockinfo
Here's an example result row:
0x00070500AAA1534E0100080000000000
From left to right, with the appropriate byte swapping within each field, this example means the following:
And since it is a Key resource: