Microsoft KB Archive/64557

PRB: Updating Text/Image Data Within User-Defined Transaction

PSS ID Number: Q64557 Article last modified on 12-18-1992

1.00 1.10 OS/2

Summary:

SYMPTOMS The following commands are being used to insert and update a record containing an image type field within a user-defined transaction: 1. Begin transaction proc1. 2. Insert record using proc1. 3. Select for text pointer and timestamp using proc1. 4. Call the dbwritetext function using proc2. 5. Call the dbmoretext function using proc2. However, on a second call to the dbmoretext function, the system hangs. This is not a problem in SQL Server version 1.0. The dbwrite and dbmoretext calls can be used while using the same dbproc.

CAUSE It is not possible to update data using the dbwritetext and dbmoretext functions within the inner bounds of a user-defined transaction. The BEGIN TRANSACTION statement forces an exclusive page lock that is held by proc1 on the affected data structures in order in ensure integrity. This lock is not released until the COMMIT TRANSACTION is executed by the process.

RESOLUTION While this integrity constraint of the system does limit the use of the insert and update commands within an atomic transaction, the Browse mode may be used to attain the exclusive functionality that is needed for this type of transaction. While the Browse mode uses optimistic concurrency control (if another process modifies the timestamp, the transaction is rolled back), when the dbwritetext function is issued together with the dbmoretext function in Browse mode, an exclusive lock is established so that no other process can read or update the selected column. The additional overhead cost is associated in checking whether a collision has occurred prior to issuing the dbwritetext call. If it is necessary to keep other users from accessing the image data prior to it being initially updated, it might be helpful to establish an associated column of type bit that is initially set to “false” until the dbwritetext call has succeeded.

= Additional reference words: 1.00 1.10 1.x =

Copyright Microsoft Corporation 1992.