FIX: Direct Update Gets 2 update_page Locks

Last reviewed: April 30, 1997
Article ID: Q121060

The information in this article applies to:

  - Microsoft SQL Server version 4.2
BUG# NT: 908 (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


Additional query words: sql6 Windows NT
Keywords : kbbug4.20 kbfix6.00 kbprg SSrvLock SSrvTran_SQL
Version : 4.2 6.0
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.