At 08:30 PM 3/11/2004 -0500, you wrote:

>:: Assuming you are displaying this is a grid or something
>:: similarly Delphish, put the SELECT statement into a
>:: read-only, ReadCommitted transaction
>
>Transactions for reading?? I've seen references to this in IB, but I always
>assumed it was some sort of optional thing to do with reading and
>committing.

Everything in Fb/IB happens inside a transaction (the ONE thing that
doesn't is fetching a value from a generator).

>My background is mostly Orace, Sybase, and a bit of Squirrel
>Server. I've done quite a bit of IB but back in the 4 days.

The model hasn't changed since IB 4.

>:: 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.
>
>Yes -but most DB's that Ive worked with do return a count on execute.

So does Fb, but it'd no good depending on that, since there will be nothing
returned but an exception if your update fails. I guess you could "dummy"
the logic somehow. Seems a weird thing to do just to make Fb behave like
some other dbms.

>:: 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.
>
>Hm - and this is based on when I started my transaction?

No. Fb has an optimistic, row-level locking system. What this means is
that, when your transaction starts, it can read the last committed version
of all of the rows, including those on which other transactions may have
pending updates. It's only when your transaction requests an update that
it becomes aware of those pending updates - and also any updates or deletes
that occurred to the row(s) affected by this request since your transaction
began. What happens then depends on how your transaction is configured.

> I'll have to
>experiment how to specifically catch this in .NET.
>
>:: 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.
>
>I need to build it into the framework and return a signal as this framework
>is for multiple DB's. Its only running on FB right now, but the framework
>has to support Oralce and SQL server too.
>
>:: 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.
>
>Well Im still finding my way with FB transactions. :)

I seem to get the impression that you see "transactions" and "statements"
as being the same thing.

A transaction is a kind of "world" that wraps "the database state" around a
client task. There can one statement or a great many, all within this
wrapping. The transaction holds a consistent view of database state as
seen when the transaction started. In Read Committed isolation, the
database state gets updated and the new state becomes visible inside your
transaction's "world". (You can configure a ReadCommitted transaction to
care about any updates that are still pending or to treat them as though
they were not there..but ReadCommitted isolation is not what you want for
your pessimistic locking hack).

As soon as your locking statement posts an update to this "lock" field on
your selected record, the engine creates a new record version for it, that
only your transaction can see. However, now that your transaction has a
lock on that record, no other transaction will be able to post an update to
that row. Your transaction "has the lock" until the transaction is either
committed or rolled back.

If it happens to be your transaction that tries to submit the locking
statement, and another transaction already "has the lock", then your
locking statement will fail with a locking exception.

>The lock looks attractive and Im pretty sure I understand it pretty well,
>but the problem here is its FB specific and I need to try to avoid such
>items.

I'd want to re-design the application so that, instead of reading some
vendor-specific "result" from a do-nothing statement, you had some
polymorphic function IsLocked() that returns a Boolean result. In DB
programming it was never a cool idea to bind application logic tightly to
vendor-specific implementations.

btw, watch out that your pessimistic locking strategy doesn't kick off your
Update triggers. Still, I guess if you are trying to write Everyman's
application, you won't be using triggers...

./heLen