--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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.

OK, good.

> 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.

Ah, OK!

> >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.

I figured that that was a 'hack', and that the SELECT WITH LOCK was
the preferred solution.

> > > 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.

Good that you mention that explicitly, but I was indeed planning to
use one transaction per conversion.

> >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. :-)

I had actually considered blob filters, but the preferred language for
writing the conversion logic would be Java or VB. I also couldn't
readily think of a way to have these blob filters run on separate
machines. The other pro that a locking select would achieve is that I
can easily add more converters when required without changes in
configuration to the server running Firebird.

To summarize what I've learned so far:
* the client (converter process) starts a transaction with READ
COMITTED, selects a candidate row, 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.
* It then converts the blob at its leasure, and updates the BLOB in a
* separate transaction when ready.

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.

[warning: fanboy comment ahead]As an aside, I'm coming from
Access/MySQL and have recently begun with Firebird, and I am
thouroughly impressed with both the features of Firebird and the
prompt help available. Just wanted to say I'm really
grateful.[/warning]

Thanks,
Emiliano