At 07:48 AM 4/02/2004 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
>
> > AFAICT from your description, this is exactly the kind of "strictly
> > serial" scenario that WITH LOCK was designed for,
>
>OK, that's good to hear. The release notes mention something to the
>effect of "if you don't understand the implications, you don't want to
>be messing with this", so... ;)

Indeed, as you seem to be discovering... ;-|

> > 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.
>
>You mean that the other selects should explicitly exclude based on the
>flag column I set?

No, this "flag column" idea is pretty pointless - since, if the "busy
transaction" updates it, other transactions can't see it. You might as
well just have the "busy transaction perform a "dummy update", (set
thePK=thePK) since, with NO WAIT, this will do a pessimistic lock for the
record.

> > Both transactions should be READ COMMITTED,
>
>I can do that per-transaction?

Of course. It is a parameter of every transaction.

> > the one with the update should have NO WAIT isolation,
> > the first read-only (if possible) and both of them
>
>The initial SELECT ... WITH LOCK would be read-only, right?

WRONG!! The SELECT statement would not be WITH LOCK. It would be just be
a plain SELECT statement in a read-only, read committed transaction.

>How could I perform a write in a SELECT?

You can't.

But the WITH LOCK style of pessimistic locking operates on a named
cursor. You use a SELECT statement to open a cursor and then, when
updating, you do UPDATE THETABLE SET BLAH WHERE CURRENT OF THECURSOR.

> > explicit (no Autocommit).
>
>OK, got that.
>
> > 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.
>
>Ah, OK.
>
>The thing is, I'd prefer to have the actual blob transform occur
>outside the database. It uses external tools, so what I'd like to do
>is:
>
>1. Select a row that needs conversion and mark it hands-off to other
>connections

You can do this with a "dummy update" style of pess. lock.

>2. Fetch the blob and store it in the filesystem
>3. Convert
>4. Update the row, storing the converted blob.
>

Rest not applicable to the "process-on-the-client" approach...

> > 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.
>
>Wouldn't that be 'the' invocation of the SP? One converter will always
>be working on one BLOB at the time.

Sure. But you can run the same statement many times inside a single
transaction. What I'm saying is, with the pess. lock, you shouldn't do
that: you should commit the single invocation before going again with another.

>I see... but much of this logic would run outside Firebird since the
>conversion is costly (time-wise and cpu-wise) and I want to be able to
>run these conversions on different machines if need be.

Dare I mention "blob filter"? No, I dare not. I think we're already lost
enough. :-)

/hb