FIX: Direct Update Gets 2 update_page Locks

  - Microsoft SQL Server version 4.2

SYMPTOMS

When performing an update-in-place on a table with the row size padded out to one row per page, Microsoft SQL Server obtains an update_page lock on two different pages - one on the page actually being updated and the other on the next page in the page chain. The second page is never actually written to, but the update_page lock is held until the transaction is complete. This scenario is commonly used to create a sequence number table to generate row sequence numbers.

WORKAROUND

You can use any of the following options to avoid this problem:

• Add a dummy row after each regular row in the sequence number table. The table could then be restructured so that it contains two rows per page (one regular row followed by one dummy row) in order to save space.
• Create a separate sequence number table for each user table instead of using one central sequence number table.
• Use an alternate method as documented in Chapter 8 of the "Transact-SQL Reference" for generating row sequence numbers.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. This problem was corrected in SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

The sp_lock output in the following script illustrates the problem:

   CREATE TABLE keys(
      key_name char ( 30 ) ,
      key_value int,
      filler0 char  ( 255 ) ,
      filler1 char  ( 255 ) ,
      filler2 char  ( 255 ) ,
      filler3 char  ( 255 ) )
   GO

   CREATE  UNIQUE  CLUSTERED  INDEX keys_pkey_idx ON keys ( key_name )
   GO

   insert into keys values ('a', 1, ' ' ,' ', ' ', ' ')
   insert into keys values ('b', 1, ' ' ,' ', ' ', ' ')
   insert into keys values ('c', 1, ' ' ,' ', ' ', ' ')
   GO

   BEGIN TRAN
   SET SHOWPLAN ON
   GO

   UPDATE keys SET key_value = key_value + 1
   WHERE key_name = 'b'
   GO

   SET SHOWPLAN OFF
   GO

   EXEC sp_lock
   GO

   ROLLBACK TRAN
   GO