Internal Locking Architecture

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.

click to view at full size.

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.

Lock 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:

  • Lock resource name
  • Pointers to connect the lock blocks to the lock hash table
  • General summary information
  • Pointer to a list of lock owner blocks for locks on this resource that have been granted (granted list)
  • Pointer to a list of lock owner blocks for locks on this resource that are waiting to be converted to another lock mode (convert list)
  • Pointer to a list of lock owner blocks for locks that have been requested on this resource but have not yet been granted (wait list)

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:

  • If the lock is on a DB resource, SR indicates one of the following:
    • Full database lock
    • Bulk operation lock
  • If the lock is on a Table resource, SR indicates one of the following:
    • Full table lock (default)
    • Update statistics lock
    • Compile lock
  • If the lock is on an Index resource, SR indicates one of the following:
    • Full index lock (default)
    • Index ID lock
    • Index name lock

Lock Owner Blocks

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.

Syslockinfo Table

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:

  • Byte 0: Flag: 0x00
  • Bytes 1: Resource Type: 0x07, a Key resource
  • Bytes 2 through 3: DBID: 0x0005

And since it is a Key resource:

  • Bytes 4 through 7: ObjectID: 0x4E53A1AA (1314103722 decimal)
  • Bytes 8 through 9: IndexID: 0x0001 (the clustered index)
  • Bytes 10 through 16: Hash Key Name: 0x080000000000


Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net