At 07:55 PM 3/02/2004 +0000, you wrote:

>Hi,
>
>I have a database with pictures like
>
>CREATE TABLE IMGS (
> IMG_ID BIGINT NOT NULL,
> IMG_DATA BLOB
> MIMETYPE VARCHAR(10) NOT NULL,
> CONVERT_TO VARCHAR(10)
>
> PRIMARY KEY (IMG_ID),
>)
>;
>
>(this is not the exact table but simplified for this example). This
>database would be accessed by multiple clients that look for rows that
>have CONVERT_TO not equal NULL, lock the row in some way so other
>clients would not see it or would know to ignore it without race
>conditions, the client would convert the blob to the requested
>mimetype, and set convert_to to NULL. Conversion can take anywhere
>from 1 second to 45 minutes; typical conversion time is 2 minutes.
>Although I guess I would only have to fetch and set to NULL the
>CONVERT_TO within the transaction.
>
>Is the new SELECT WITH LOCK the way to go here, or is there a simpler
>facility. This is really the only place I want transactions to
>'exclude' each other. In all other instances I want as high a level of
>concurrency as possible.

AFAICT from your description, this is exactly the kind of "strictly serial"
scenario that WITH LOCK was designed for, as long as you isolate the SELECT
statement from which selections are being made inside one transaction from
the UPDATE statement that is being used to trap the row for your update
operation in another transaction.

Both transactions should be READ COMMITTED, the one with the update should
have NO WAIT isolation, the first read-only (if possible) and both of them
explicit (no Autocommit).

Use the first query to select the unique key of the row to be updated and
the second to invoke a stored procedure that takes the unique key as its
input argument and performs the update.

Inside the SP, trap the row into a cursor, do the conversion, and
exit. Commit the transaction as soon as one invocation of the SP completes.

CREATE PROCEDURE CONVERT_BLOB (ThePKey BIGINT)
AS
DECLARE VARIABLE T_ID BIGINT;
DECLARE VARIABLE TheBlob BLOB SUB_TYPE 0;
DECLARE VARIABLE <other cols you need for the operation>
...,
DECLARE VARIABLE TheNewBlob BLOB SUB_TYPE 0;
BEGIN
FOR SELECT <list of columns>
FROM TheTable
WHERE PK_of_Table = :ThePKey
FOR UPDATE OF TheBlobColumn WITH LOCK
INTO
:T_ID,
:TheBlob,
:<other column variables>
AS CURSOR Crsr
DO
BEGIN
TheNewBlob = MassageBlob(:TheBlob); /*Your conversion function*/
UPDATE TheTable
SET TheBlobColumn = :TheNewBlob
WHERE CURRENT OF Crsr;
WHEN ANY DO /* or find out the gdscode for pess. lock */
EXCEPTION MyException;
END

END

The procedure call will fail if another transaction has already trapped it
WITH LOCK. If it succeeds, you will have the pessimistic lock and can
proceed with processing.

To handle the failure, create a custom exception MyException with some
friendly message like 'Someone else is already updating this record', or
trap the blockage directly using the gdscode.

I don't know the gdscode that would be returned for the blocked WITH LOCK
request -- I'm assuming it's record_lock (335544476) but this question
prompts me to find out and get it into the release notes!

/heLen