INF: Locking Behavior of Updates and Deletes in SQL Server

• Microsoft SQL Server, version 4.21

SUMMARY

UPDATE and DELETE statements that require a table scan result in an exclusive lock held on the modified table.

MORE INFORMATION

Page 103 the SQL Server "Troubleshooting Guide" states:

   ...an exclusive table lock is usually acquired for a mass update
   as a result.

Consider the following example from the pubs database:

   set showplan on
   go
   begin tran
   update authors
   set city = 'Ft. Worth'
   where au_id = '172-32-1176'
   go

   STEP 1
   The type of query is BEGINXACT
   STEP 1
   The type of query is UPDATE
   The update mode is direct
   FROM TABLE
   authors
   Nested iteration
   Using Clustered Index
   TO TABLE
   authors

The output from running the sp_lock command with this transaction active is as follows:

spid   locktype             table_id    page        dbname

5      Ex_intent            16003088    0           pubs
5      Ex_page              16003088    352         pubs
5      Update_page          16003088    352         pubs

However, if the table authors has no index that can be used for au_id, then the same query will require a table scan:

   STEP 1
   The type of query is BEGINXACT
   STEP 1
   The type of query is UPDATE
   The update mode is deferred
   FROM TABLE
   authors
   Nested iteration
   Table Scan
   TO TABLE
   authors

The output from running the sp_lock command with this transaction active is as follows:

spid   locktype             table_id    page        dbname

5      Ex_table             16003088    0           pubs

Statements other than updates and deletes can also require exclusive or shared table locks. The following table is a summary of locking behavior for INSERT, SELECT, UPDATE, DELETE, and other Transact-SQL statements for SQL Server. The table also contains information about which lock types are allowed concurrently between transactions and information about lock promotions.

               Statement                  Table Lock      Page Lock

               Insert                     IX              X
 Using         Select                     IS              S
 Index         Select w/Holdlock          IS              S
               Update                     IX              U,X
               Delete                     IX              X
 Without       Select                     IS              S
 Using         Select w/Holdlock          S               --
 Index         Update                     X               --
               Delete                     X               --
               Create clust. Index        X               --
               Create non-cl. Index       S               --

                IS      IX      S      X

   IS           YES     YES     YES    NO
   IX           YES     YES     NO     NO
   S            YES     NO      YES    NO
   X            NO      NO      NO     NO

                S      U      X

   S            YES    YES    NO
   U            YES    NO     NO
   X            NO     NO     NO

Lock Types

IS = Intent Shared; Intent locks flag at table level type of page

     locks held

Lock Promotion: Server will try to satisfy requests with page locks first. During a transaction, if more than 200 page locks per SDES are held, it will escalate to a Shared or Exclusive table lock instead, which lowers lock overhead.

This table can also be found on the Microsoft TechNet CD under the title "Summary of Locks on SQL Server."

For additional information regarding concurrency and locking topics, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q75722
   TITLE     : INF: Reducing Lock Contention in SQL Server

   ARTICLE-ID: Q122485
   TITLE     : INF: Identifying SPID Responsible for Lock Chain

   ARTICLE-ID: Q45542
   TITLE     : INF: Shared Access to Modified Data

   ARTICLE-ID: Q43199
   TITLE     : INF: Concurrency and Consistency and SQL Server Alternatives