INF: Page Locks of Large Insert Trans Not Promote Table Lock (112347)

MORE INFORMATION

The following is an example:

     /* create a table */ 

     create table test_tab
     (RecNum int,
       txt1 char(40),
       txt2 char(40),
       txt3 char(40),
       txt4 char(40),
       txt5 char(40),
       txt6 char(40),
       txt7 char(40) )
     go

     declare @count int
     select @count = 0

     set nocount on

     begin tran big_insert

       while (@count < 5000)
             begin
          select @count = @count+1

          insert into test_tab
          values ( @count, 'A', 'B', 'C', 'D', 'E', 'F', 'G')
             end

     commit tran big_insert

     go
				

This behavior is as expected. Since the insert statements are inside a while loop each one is considered an individual statement with its own private session. Since the total number of locks are kept on a per session basis, this number is 1 for every session; thus, it never gets escalated to a table lock.



SQL Server is designed for an OLTP environment where transactions are typified by a relatively small size, and the design point of SQL Server is to maximize concurrency for a typical OLTP query/transaction mix.

To meet this goal, SQL Server uses page-level locking as its finest granularity and usually escalates to table locking at 200 page locks per SDES. SDES is the SQL Server session descriptor structure used to maintain session information. A session in this context is an active query from a process which affects a single object. Exceptions to this include some types of inserts, which for concurrency reasons retain page locks, and extent locks used in clustered index creation.

The below example queries all escalate to a table lock:

- select * from t1 holdlock order by col1 desc

- select * from t1 holdlock

- update t1 set col1=col1

- delete from t1

- insert into t1 select * from t1

- insert into t1 select * from t2
				

Whereas this query does not escalate:

   begin transaction
   insert into t1 values("test data")
   go
   insert into t1 values("test data")
   go
   <repeat several hundred times>
   commit transaction
				

To avoid situations where too many page locks are being held by a transaction which has a while loop or separate insert statements, break the transaction into smaller ones, or increase the lock configuration number.

Page locks held by a bcp process are not escalated to table lock either. If that is a problem, the transaction should be broken down into smaller batches.