Subject RE: [firebird-support] How to "lock" a record
Author Chad Z. Hower
:: 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. 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.

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

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

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