Microsoft KB Archive/121060

{|
 * width="100%"|

FIX: Direct Update Gets 2 update_page Locks

 * }

Q121060

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

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 : kbprogramming

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2