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

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

More than 200 page locks held by a large insert transaction may not be promoted to a table lock if the transaction has a while loop.

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

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

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

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.