Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the default lock granularity in SQL Server?

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?

like image 548
sharptooth Avatar asked Dec 18 '12 13:12

sharptooth


1 Answers

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.

like image 108
Remus Rusanu Avatar answered Oct 22 '22 10:10

Remus Rusanu