At 12:05 PM 5/06/2005 +0300, you wrote:

>:: "Concurrency" is an isolation level, another word for "Snapshot" or
>:: "Repeatable Read". Other members of this paradigm are "Read
>:: Committed"
>:: ("ReadCommitted" in .NET?) and "Snapshot Table Stability"
>:: ("Consistency" in
>:: .NET?)
>
>ReadRepeatable. The issue is this - if I use FB.NET directly, I can specify
>FB as I want it. But now Im porting a library to be "indepenedent" so I cant
>use the FB specifics. I have to use the .NET values....

That goes without saying - the whole point of having a driver, i.e. being
able to create netproviders with Firebird back-ends, is to mash down the
vendor-specific options to the "nearest thing" that the particular
interface standard specifies. You get the same kind of mashing down with
an ADO, ODBC, Java, PHP, Python, etc. driver. They are all interfaces
between a standard and a vendor-specific set of features.

>I was using this:
>
> _TxOpts =
> FbTransactionOptions.Concurrency | FbTransactionOptions.Read
> | (aNeedsToWrite ? FbTransactionOptions.Write : 0);
>
>Based on a long thread and advice here many months ago. Currently I am not
>using the aNeedsToWrite, but I had planned for it.

I have no idea what it means: it's *some* .NET thing.

>So currently all are
>Concurrency + Read + Write.

Sounds right for normal use.

>In .NET I have the following choices:
>
> switch (this.isolationLevel)
> {
> case IsolationLevel.Serializable:
> options |=
>FbTransactionOptions.Consistency;
> break;
>
> case IsolationLevel.RepeatableRead:
> options |=
>FbTransactionOptions.Concurrency;
> break;
>
> case IsolationLevel.ReadUncommitted:
> options |=
>FbTransactionOptions.ReadCommitted;
> options |=
>FbTransactionOptions.RecVersion;
> break;
>
> case IsolationLevel.ReadCommitted:
> default:
> options |=
>FbTransactionOptions.ReadCommitted;
> options |=
>FbTransactionOptions.NoRecVersion;
> break;
> }

Which is precisely a "mashing down". For example, Fb doesn't support a
dirty read (ReadUncommitted) so you get the nearest thing -
ReadCommitted. Fb doesn't support Serializable so you get the nearest
thing - consistency (Snapshot Table Stability), which should be avoided.

>What I need is essentially my reads to be versioned - and repeatable, but
>not block other write transactions on the same record.

All reads in concurrency are "versioned" - from the beginning of the
transaction to the end, the transaction sees only the view of database
state as it was at the start, + any changes posted by that transaction. No
other transaction can see those changes.

DB engines that support dirty read (your ReadUncommitted) do allow other
transactions to see work that is posted by others but not yet
committed. The nearest Fb comes to that is ReadCommitted, where the
transaction's view of database state changes as others commit work.

>It's a financial
>system - the performance penalty is ok so long as my transactnois are
>isolated. If there are write conflicts - I get a version/update error and in
>fact we trap this in a few spots.

Your transactions are always isolated in Firebird. It's not something you
ever have to worry about yourself. The system is *designed* to let you
trap conflicts and deal with them in whatever way you want to.

In Concurrency and ReadCommitted, readers don't block writers as a
rule. Transactions see what they want to see; locks are created
automatically for writers.

>What is consistency? :)

It's a heavily blocking isolation. If a consistency transaction is able to
get what it asks for, it blocks all other transactions from accessing the
tables. When we want table locks in Firebird, we use a concurrency
transaction with a RESERVING clause, if we can, because it doesn't block
readers.

>Should I be using just the RepetableRead option? Is that pretty much what I
>had before?

Yes. In the normal run of things, it's your best isolation level.

>:: Besides isolation, a transaction can be Read Only or
>:: Read-Write. I don't
>:: know the .net interface too intimately, but it looks as if
>:: you have some
>:: flag to set to indicate whether the transaction needs to be
>:: read-write. Other interfaces tend to follow the engine's
>:: default, which is
>:: read-write, with readonly by request.
>
> options |=
>FbTransactionOptions.Concurrency;
>
>Will this not make it read+write?

Will it not? Will it? I can't process this question. But the isolation
level doesn't determine the read mode of the transaction.

>Aah.. Just above this case statement is:
>
> FbTransactionOptions options =
>FbTransactionOptions.Write;
>
> options |= FbTransactionOptions.Wait;
>
>So Write I assuem also implies a read? :)

Yes. There are two modes: ReadOnly and ReadWrite. The default is
ReadWrite, i.e. if you don't specifiy it, your transaction will be able to
both read and write.

Wait specifies that, if there is a conflict, the transaction that
encounters the conflict will wait until the locking transaction either
commits or rolls back and then attempt to post its request at that
point. I think the Firebird default is Wait, but many interfaces switch
*their* default to No Wait. I prefer No Wait in virtually all
circumstances, as it is much cleaner and simpler to handle a lock conflict
at the client than to leave things hanging indefinitely. But there must
have been *some* rationale for defaulting to Wait. No doubt Ann will
explain. :-)

>:: There are other parameters for a transaction, too.
>:: Wait/NoWait; and, for
>:: a ReadCommitted transaction, RecordVersion/NoRecordVersion.
>:: There's also
>:: an optional Reserving clause that lets you take table locks,
>:: with various
>:: sub-parameters to set up how the locks are to be respected by other
>:: transactions. Carlos should be able to answer any questions
>:: about the .NET
>:: provider's defaults and any peculiarities of implementation...
>
>Lots of options there... Maybe someday I'll figure all them out and wirte a
>more detailed article than exists now. But it appears that I don't need the
>+Read as if +Write is there than Read is implcit?

Not implicit. Explicit. All SELECTs read. All data-changing operations
have to read, or they wouldn't have any way to locate the rows they want to
write to. The modal part is whether the transaction can also write.

>So I guess my only major
>question now would be, what is FbTransactionOptions.Consistency?

Snapshot Table Stability. Avoid it unless your aim is to shut the shop and
party alone. It will have its place - rarely - in such operations as
freezing for audits and stocktakes.

./heLen