At 12:37 PM 4/02/2004 +0000, you wrote:

>To summarize what I've learned so far:
>* the client (converter process) starts a transaction with READ
> COMITTED, selects a candidate row,

Now starts a new transaction which

>tries to obtain a pessimistic
> lock by doing an identity update on one of the columns with NO WAIT,
> and if it succeeds,


>* marks the column as 'in progress', and commits the transaction.

No, omit this step entirely. It is flaky. If the "operational"
transaction has secured a pess. lock on the row, the SELECT transaction
won't be able to update *anything* on it. The SELECT transaction should be
read-only, otherwise it will hold up garbage collection.

If the second transaction's request for the lock succeeds,

>* It then converts the blob at its leasure, and updates the BLOB in a
>* separate transaction when ready.

No, the client updates the BLOB in the transaction it started for the
purpose of doing the task.

>Or could I just keep the first transaction open until the conversion
>is ready? That would make the 'mark in process' unnecesary, but I
>don't know how bad long transactions are.

Long transactions that tie up multiple records are death.

Re the Dummy Update - yes, it's a hack, and putting an explicit lock on a
cursor is less of a hack - but any pessimistic locking in Firebird is
inherently hackish. If you do want to go the cursor route, you can use the
API functions directly from C/C++ or using an API conversion for another
language. There is no DSQL command for declaring a named cursor.

/hb