Microsoft KB Archive/125770

{|
 * width="100%"|

INF: Locking Behavior of Updates and Deletes in SQL Server

 * }

Q125770

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

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.

Updates and deletes that do not use an index to search for rows that require modifications also require an exclusive table lock. This lock is not generated as a result of page lock promotion, but rather an immediate exclusive table lock.

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

The results of the query show that a clustered index on au_id can be used to satisfy the search for a row to update:

  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

Since the clustered index is used, only exclusive page locks are necessary for a consistent transaction.

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

NOTE: An exclusive intent lock is held on the table to prevent another exclusive table lock.

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 query in this example requires an exclusive table lock on authors, because the server must perform a table scan to see which rows are required for modification. Because no index is available for au_id, the optimizer does not have any information to determine that only one row needs to be modified.

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

To avoid an exclusive table lock in this type of scenario, proper index design should be evaluated similar to considerations for SELECT queries. If the column modified in the SET clause is the same as the one used for searching, the performance trade offs associated with maintenance of the index must be evaluated with concurrency benefits.

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               --

Lock Compatibility - Table Locks

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

Lock Compatibility - Page Locks

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 IX = Intent Exclusive S = Shared X = Exclusive U = Update; Used for read/modify/write operations

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."