sql server - What are row, page and table locks? And when they are acquired?

Question

I want to know more about different types of locks Database Engine aquires.

1. What are
   • Row Locks
   • Page Locks
   • Table Locks
2. What is a page actually? As I know "row" represents one record, "table" represents all records in a table. But what is page with respect to a table?
3. When these locks are acquired (scenarios) by the Database Engine?

Please help me understand these concepts.

Answer 1

Row Lock

A row lock is the lowest level of granularity of locking possible in SQL Server. This means one or more specific rows will be locked, and the adjacent rows are still available for locking by concurrent queries.

Page Lock

A page lock in SQL Server will lock 8K worth of data even when your query only needs 10 bytes from the page. So your query will lock additional data which you do not request in your query.

Hobt Lock

When a table is partitioned with "SQL Server Table partitioning" it is possible a Single partition will be locked (Hobt stands for Heap or B-Tree)

Note: Lock escalation to HOBT locks is disabled by default. run ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO) to enable HOBT lock escalation.

Table Lock

A table lock will lock the complete table.

What are Data Pages

Microsoft SQL Server organizes all it's data in "Data Pages" which can hold 8K worth of data. This means that for any data access in SQL Server 8K information will be read.

Data pages can only contain information from one table and the layout of a page is well documented on MSDN

The fact that SQL Server will always read a complete data page also gives you an idea why it prefers to use page level locks. The implication of page level locks are that you might lock a lot more data than you think.

For example, assume we have a table with a total record size of 1024 bytes with a clustered index on the field ID. When we run the following query: SELECT * from MyTable (xlock) where ID = 123 not only that record will be locked, but (depending on the page fill) maximum 3 additional records will be locked as well.

When are these locks acquired

A query will be parsed by the query governor and the required locks will be determined and requested from the lock manager. SQL Server will try to make a balance between performance and contention to determine the locking strategy.

SQL Server also follows a "lock escalation" system which will reduce the granularity of locking when more than 5000 locks of a certain type are being acquired. See this article on lock escalation for more information.

This behavior can be tweaked using locking hints with stress on hints, in a query you can specify per table what kind of locks you would prefer. SQL Server will try to honor your request, but it will still apply lock escalation.