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, and the metadata that keeps track of locks is never written to disk. Locks are internal memory structuresthey consume part of the memory used for SQL Server. A lock is identified by lock resource, which is a description of the resource that is locked (a row, index key, page, or table). To keep track of the database, the type of lock, and the information describing the locked resource, each lock requires 64 bytes of memory on a 32-bit system and 128 bytes of memory on a 64-bit system. This 64-byte or 128 byte structure is called a lock block. Each process holding a lock also must have a lock owner, which represents the relationship between a lock and the entity that is requesting or holding the lock. The lock owner requires 32 bytes of memory on a 32-bit system and 64 bytes of memory on a 64-bit system. This 32-byte or 64-byte structure is called a lock owner block. 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 is the case with a shared lock. As mentioned, the lock owner represents a relationship between a lock and an entity, and the relationship can be granted, waiting, or in a state called waiting-to-convert. 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. All lock blocks that hash to the same slot are chained together from one entry in the hash table. Each lock block contains a 15-byte field that describes the locked resource. The lock block also contains pointers to lists of lock owner blocks. There is a separate list for lock owners in each of the three states. Figure 8-3 shows the general lock architecture. Figure 8-3. SQL Server locking architecture The number of slots in the hash table is based on the system's physical memory, as shown in Table 8-7. There is an upper limit of 231 slots. An exception is SQLExpress, which always has 211 slots. 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 serialized access to the same slot.
The lock manager allocates in advance a number of lock blocks and lock owner blocks at server startup. On NUMA configurations, these lock and lock owner blocks are divided among all NUMA nodes. So when a lock request is made, local lock blocks are used. 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 2005 SQLExpress edition 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. 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. 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 PartitioningFor large systems, locks on frequently referenced objects can become a performance bottleneck. The process of acquiring and releasing locks can cause contention on the internal locking resources. Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. For systems with 16 or more CPUs, SQL Server automatically splits certain locks into multiple lock resources, one per CPU. This is called lock partitioning, and there is no way for a user to control this process. An informational message is sent to the error log whenever lock partitioning is active. The error message is "Lock partitioning is enabled. This is an informational message only. No user action is required." Lock partitioning applies only to full object locks (for example, tables and views) in the following lock modes: S, U, X, and SCH-M. All other modes (NL, SCH_S, IS, IU, and IX) are acquired on a single CPU. SQL Server assigns a default lock partition to every transaction when the transaction starts. During the life of that transaction, all lock requests that are spread over all the partitions use the partition assigned to that transaction. By this method, access to lock resources of the same object by different transactions is distributed across different partitions. The resource_lock_partition column in sys.dm_tran_locks indicates which lock partition a particular lock is on, so you can see multiple locks for the exact same resource with different resource_lock_partition values. For systems with fewer than 16 CPUs, for which lock partitioning is never used, the resource_lock_partition value is always 0. For example, consider a transaction acquiring an IS lock in REPEATABLE READ isolation, so that the IS lock is held for the duration of the transaction. The IS lock will be acquired on the transaction's default partitionfor example, partition 4. If another transaction tries to acquire an X lock on the same table, the X lock must be acquired on ALL partitions. SQL Server will successfully acquire the X lock on partitions 0 to 3, but it will block when attempting to acquire an X lock on partition 4. On partition IDs 5 to 15, which have not yet acquired the X lock for this table, other transactions can continue to acquire any locks that will not cause blocking. With lock partitioning, SQL Server distributes the load of checking for locks across multiple spinlocks, and most accesses to any given spinlock will be from the same CPU (and practically always from the same node), which means the spinlock should not spin often. Lock BlocksThe lock block is the key structure in SQL Server's locking architecture, shown earlier in Figure 8-3. A lock block contains the following information:
The lock resource uniquely identifies the data being locked. Its structure is shown in Figure 8-4. Each "row" in the figure represents 4 bytes, or 32 bits. Figure 8-4. The structure of a lock resource The meanings of the fields shown in Figure 8-4 are described in Table 8-8. The value in the resource type byte is one of the locking resources described earlier in Table 8-5. The number in parentheses after the resource type is the code number for the resource type (which we'll see in the syslockinfo table a little later in the chapter). 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).
Following are some of the possible SR (SubResource) values. If the lock is on a DB resource, SR indicates one of the following:
If the lock is on a Table resource, SR indicates one of the following:
If the lock is on an Index resource, SR indicates one of the following:
Lock Owner BlocksEach 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 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 they can be freed as appropriate when the transaction or session ends. syslockinfo TableAlthough the recommended way of retrieving information about locks is through the sys.dm_tran_locks view, there is another metadata object called syslockinfo that provides internal information about locks. Prior to the introduction of the DMVs in SQL Server 2005, syslockinfo was the only internal metadata available. In fact, the stored procedure sp_lock is still defined to retrieve information from syslockinfo instead of from sys.dm_tran_locks. I will not go into full detail about syslockinfo because almost all the information from that table is available, in a much more readable form, in the sys.dm_tran_locks view. However, syslockinfo is available in the master database for you to take a look at. One column, however, is of particular interestthe rsc_bin column, which contains a 16-byte description of a locked resource. You can analyze the syslockinfo.rsc_bin field as the resource block. Let's look at an example. I'll select a single row from the Person.Contact table in AdventureWorks using the REPEATABLE READ isolation level, so my shared locks will continue to be held for the duration of the transaction. I'll then look at the rsc_bin column in syslockinfo for key locks, page locks, and table locks. USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN SELECT * FROM Person.Contact WHERE ContactID = 249 GO SELECT rsc_bin, rsc_type FROM master..syslockinfo WHERE rsc_type IN (5,6,7) GO Here are the three rows in the result set: rsc_bin rsc_type ---------------------------------- -------- 0xCDC17312000000000000000005000500 5 0xA1260000010000000000000005000600 6 0x510000000001F900CE79D52505000700 7 The last two bytes in rsc_bin are the resource mode, so after byte swapping you can see the same value as in the rsc_type columnfor example, you byte swap 0500 for 0005 for resource mode 5 (a table lock). The next 2 bytes at the end indicate the database ID, and for all three rows, the value after byte swapping is 0005, which is the database ID of my AdventureWorks database. The rest of the bytes vary depending on the type of resource. For a table, the first 4 bytes represent the object ID. The preceding row for the object lock (rsc_type =5) after byte swapping has a value of 1273C1CD, which is 309576141 in decimal. I can translate this to an object name as follows: SELECT object_name(309576141) This shows me the Contact table. For a PAGE (rsc_type = 6), the first 6 bytes are the page number followed by the file number. After byte swapping, the file number is 0001, or 1 decimal, and the page number is 000026A1, or 9889 in decimal. So the lock is on file 1, page 9889. Finally, for a KEY (rsc_type = 7), the first 6 bytes represent the partition ID but the translation is a bit trickier. We need to add another 2 bytes of zeros to the value after byte swapping, so we end up with 010000000051000, which translates to 72057594043236352 in decimal. To see which object this partition belongs to, I can query the sys.partitions table: SELECT object_name(object_id) FROM sys.partitions WHERE partition_ID = 72057594043236352 Again, the result is that this partition is part of the Contacts table. The next 6 bytes of rsc_bin for the KEY resource are F900CE79D525. This is a character field, so no byte swapping is needed. However, the value is not further decipherable. Key locks have a hash value generated for them, based on all the key columns of the index. Indexes can be quite long, so for almost any possible datatype, SQL Server needs a consistent way to keep track of which keys are locked. The hashing function therefore generates a 6-byte hash string to represent the key. Although you can't reverse-engineer this value and determine exactly which index row is locked, you can use it to look for matching entries, just like SQL Server does. If two rsc_bin values have the same 6-byte hash string, they are referring to the same locked resource. |
EAN: 2147483647
Pages: 115