6

I've thoroughly read MSDN about table hints and I don't seem to find the locking granularity default. Suppose I have the following query:

SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC;

You see, I specified UPDLOCK and READPAST hints, but not any of granularity hints such as TABLOCK or ROWLOCK.

Which granularity lock level is used by default?

sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • By default, it's a row-level lock. When a session/transaction exceeds approx. 5000 row-level locks, the lock is **escalated** to table-level lock (or partition-level, if you have partitioned tables) – marc_s Dec 18 '12 at 13:54

1 Answers1

16

There is no 'default'. The granularity (row, page, (partition | object)) is computed dynamically based on allowed options for the object (allow_page_locks/allow_row_locks), information about the operation intent (probe, scan, insert), the estimated size of the rowset and a number of other factors (isolation level, is filegroup read only etc). In most cases you will get row-level granularity for singleton operations and page-level granularity for scans. The query you posted is probably going to go with page-level granularity because is a scan.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569