Wrong tpb parameters for standard transaction isolation modes [DNET337]

[firebird-automations]

Submitted by: @ibaseru

Is related to DNET307

Votes: 2

Currently page
http://www.firebirdsql.org/dotnetfirebird/transaction-isolation-levels.html
and implementation of IsolationLevel.ReadUncommitted and IsolationLevel.ReadCommitted is not correct.

IsolationLevel.ReadUncommitted have READ COMMITTED RECORD_VERSION tpb options, wich is wrong, because in the Firebird ReadUncommitted can be simulated only like this:
read committed no record version no wait

This will cause an error if transaction will attempt to read records with uncommitted versions. Even with this options it will be semi-ReadUncommitted, because Firebird will not allow to read uncommitted data in any way.

Otherwise, IsolationLevel.ReadCommitted uses mentioned above "no record version" option, which does not allow to read only committed versions (error on nowait and lock on wait).
Instead, ReadCommitted must use
read committed record version no wait

This tpb parameters exactly corresponds to the standard ReadCommitted isolation mode.

Commits: 696c5a7 FirebirdSQL/firebird-odbc-driver@dd9ad29 FirebirdSQL/firebird-odbc-driver@95aa134 FirebirdSQL/firebird-odbc-driver@ee8b3b8 FirebirdSQL/firebird-odbc-driver@4fc0c53

[firebird-automations]

Commented by: @ibaseru

as I understand, JayBird and ODBC drivers have the same problem, and the source of the problem is that by default tpb option of read committed is no_rec_version.
Yes, it is default for Firebird's and (Interbase-s) tpb options, but this is wrong for the ReadCommitted isolation mode. TPB options for ReadCommitted must not lock (wait) or return error (no wait) when reading records with uncommitted versions (which no_rec_verison option does).

I hope that I've explained the problem. It was also in BDE (fixed in BDE 4.1 and higher) and maybe exists in some other drivers (except IBX and FIBPlus components). Maybe the cause is a bit unclear name of the no_rec_version option.

IB Api Gude (v6.0), page 424:

isc_tpb_rec_version
Enables an isc_tpb_read_committed transaction to read the most recently
committed version of a record even if other, uncommitted versions are
pending.

isc_tpb_no_rec_version
Enables an isc_tpb_read_committed transaction to read only the latest
committed version of a record
If an uncommitted version of a record is pending and isc_tpb_wait is also
specified, then the transaction waits for the pending record to be committed
or rolled back before proceeding; otherwise, a lock conflict error is reported at
once

[firebird-automations]

Modified by: @cincuranet

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10470 ]

[firebird-automations]

Modified by: @cincuranet

Link: This issue is related to DNET307 [ DNET307 ]