Database Reference
In-Depth Information
Heap or B-tree Lock
A heap or B-tree lock is used to describe when a lock to either type of object could be made. The target object could be
an unordered heap table, a table without a clustered index, or a B-tree object, usually referring to partitions. A setting
within the
ALTER TABLE
function allows you to exercise a level of control over how locking escalation (covered in the
“Lock Escalation” section) is affected with the partitions. Because partitions can be across multiple filegroups, each
one has to have its own data allocation definition. This is where the
HoBT
lock comes into play. It acts like a table-level
lock but on a partition instead of on the table itself.
Table-Level Lock
This is the highest level of lock on a table, and it is identified as a
TAB
lock. A table-level lock on a table reserves access
to the complete table and all its indexes.
When a query is executed, the lock manager automatically determines the locking overhead of acquiring multiple
locks at the lower levels. If the resource pressure of acquiring locks at the row level or the page level is determined to
be high, then the lock manager directly acquires a table-level lock for the query.
The resource locked by the
TAB
lock will be represented in
resource_description
in the following format:
DatabaseID:ObjectID
A table-level lock requires the least overhead compared to the other locks and thus improves the performance
of the individual query. On the other hand, since the table-level lock blocks all write requests on the entire table
(including indexes), it can significantly hurt database concurrency.
Sometimes an application feature may benefit from using a specific lock level for a table referred to in a query.
For instance, if an administrative query is executed during nonpeak hours, then a table-level lock may not impact
the users of the system too much; however, it can reduce the locking overhead of the query and thereby improve its
performance. In such cases, a query developer may override the lock manager's lock level selection for a table referred
to in the query by using locking hints.
SELECT * FROM <TableName> WITH(TABLOCK)
But, be cautious when taking control away from SQL Server like this. Test it thoroughly prior to implementation.
Database-Level Lock
A database-level lock is maintained on a database and is identified as a
DB
lock. When an application makes a
database connection, the lock manager assigns a database-level shared lock to the corresponding
session_id
. This
prevents a user from accidentally dropping or restoring the database while other users are connected to it.
SQL Server ensures that the locks requested at one level respect the locks granted at other levels. For instance,
once a user acquires a row-level lock on a table row, another user can't acquire a lock at any other level that may affect
the integrity of the row. The second user may acquire a row-level lock on other rows or a page-level lock on other
pages, but an incompatible page- or table-level lock containing the row won't be granted to other users.
The level at which locks should be applied need not be specified by a user or database administrator; the lock
manager determines that automatically. It generally prefers row-level and key-level locks when accessing a small
number of rows to aid concurrency. However, if the locking overhead of multiple low-level locks turns out to be very
high, the lock manager automatically selects an appropriate higher-level lock.
