INF: Locking Behavior of Updates and Deletes in SQL Server

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

REFERENCES

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

Q75722 INF: Reducing Lock Contention in SQL Server

Q122485 INF: Identifying SPID Responsible for Lock Chain

Q45542 INF: Shared Access to Modified Data

Q43199 INF: Concurrency and Consistency and SQL Server Alternatives

Additional query words: Windows NT

Keywords : kbprg SSrvLock SQLFAQ SSrvWinNT
Version : winnt:4.2x
Platform : winnt
Issue type : kbinfo