Subject RE: [firebird-support] How to "lock" a record
Author Chad Z. Hower
ReadCommitted -

I assume it will only read values that are there before or after a commit -
that is it cannot read values in the middle of a commit correct (ie it will
read the values as if its before the commit)?

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

Yes, but back then I wanst going in at the same level - it was all BDE stuff
and from the "developer" standpoint was quite different. I also was not
building big systems on IB that I was on Oracle and Sybase. Now Im building
big systems on FB, so its very important to check all semantics.

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

You are thinking like FB though - the requirements of my app are cross
platform. I can rely on a return count, but I cannot rely that all DB's
support the transaction options that FB does.

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

Ive read the transactrion stuff - it will save me a lot of work as we had to
implemnet much of the optimistic locking ourself previously.

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

No, they are definitely different. But FB autowraps independent statemetns
in implicit transactions as I understand it. Whats new to me is the fact
that the db is handling read commits and a slightly different locking
scheme. Before we've always had to implement optimistic locking ourself
because the Oracle (in older versions at least) didn't handle it this way.
And thenthere is that piece of crap Sybase.. And don't get me started on
SQLDS (predecessor to DB2)

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

I cant in this case because of the layers of the system, I need to do it
lower than the application level. The wrapper *has* to iron out the
differences itself.

:: btw, watch out that your pessimistic locking strategy
:: doesn't kick off your Update triggers. Still, I guess if

We don't use triggers at all - it's not allowed in our systems. For us a DB
is *purely* storage.

isc_tpb_read Read-only access mode that allows a transaction only to select
data from
isc_tpb_write Read-write access mode of that allows a transaction to select,
update, and delete table data [Default]

If I know I am not going to write - is it faster/better to specify read or
is it just a protective mechanism? Because ADO.NET does not permit the
specfication of isc_tpb_read, all transactions are performed as

Under ADO.NET my only choices are as follows. So should I be doing ALL (ie
read and write) transactions as ReadCommitted?

FbTransactionOptions options =
FbTransactionOptions.Write |

/* Isolation level */
case IsolationLevel.Serializable:
options |= FbTransactionOptions.Consistency;

case IsolationLevel.RepeatableRead:
options |= FbTransactionOptions.Concurrency;

case IsolationLevel.ReadUncommitted:
options |= FbTransactionOptions.ReadCommitted;
options |= FbTransactionOptions.RecVersion;

case IsolationLevel.ReadCommitted:
options |= FbTransactionOptions.ReadCommitted;
options |= FbTransactionOptions.NoRecVersion;

Now lets return to my scenario - if I start a transaction, read and update a
single row this would "lock" it with a version and if someone read before or
after my read but updated it before me (Even if they updated it to the same
value) the I will get a lock error on ReadCommitted correct?