At 05:22 PM 3/11/2004 -0500, you wrote:

>I have a table which stores "Items". In most cases (but not all, its
>determined by a "type" flag in each row) each row can only be "used" once.
>Once I use it, I assign it to a field in anothoer table, lets call it
>"user". I cannot put a constraint on the user table as for some rows they
>can be used multiple times.
>
>How is the best way to "reserve" a row in the items table? I would like to
>do this in a non FB specfic manner if possible.
>
>My idea is this:
>Select min(serial) where used = false

Assuming you are displaying this is a grid or something similarly Delphish,
put the SELECT statement into a read-only, ReadCommitted transaction

>Update Item Set Used = true where serial=x and used=false

Put this statement in the thinnest possible update object that runs in an
explicitly controlled transaction in configured with concurrency isolation
and no wait lock resolution (not autocommit!!). Read on...

>If the execute count returns 1- I know I have it. If it returns 0, someone
>else got it before me. Is this ok to count on in FB (And SQL DBs in
>general?)

That's an implementation detail, not anything that could be applied to any
old SQL database (nor even most of them). User-applied locks are a
phenomenon of systems that control concurrency by two-phase locking.

In Firebird (which controls the multi-user concurrency and precedence
through managing multiple generations of record versions) it's a lot
simpler for the developer. If your application is able to post your
"pessimistic lock" statement, then your transaction has the lock. If the
statement excepts with a lock error, then another transaction already has
the lock.

So, all your application has to do is trap and handle the lock
exception. What you do with it after that is entirely up to your
application code: you might want to roll back the transaction, or just
simply cancel the statement in your statement object; or put it under some
kind of retry control using a timer; or whatever.

Pessimistic locking is available in Fb 1.5 through the the SELECT...FOR
UPDATE WITH LOCK syntax. It needs to be used with great discretion on a
very small set (preferably one row) with a very short life and with a total
understanding of what the various concurrency configurations do. There is
detailed documentation of it in the v.1.5 release notes. If you are still
finding your way with transactions, I wouldn't recommend it.

./hb