How to control FB transaction settings from ADO?

[Kjell Rilbe]

Hi,

So I'm using an O/R mapper framework (really a model driven dev framework), that supports persistence through ADO, and I can configure the ADO isolation level for select and update transactions.

I have these set to System.Data.IsolationLevel.ReadCommitted (value 4096 = 0x1000).

Question: What exact Firebird transaction settings do I get with this setting? I believe the framwork ends up calling FirebirdSql.Data.FirebirdClient.BeginTransaction(System.Data.IsolationLevel level) but I haven't tracked it down properly, so can't say for certain.

The settings I would like is something like, in Firebird terms:

SET TRANSACTION
  READ WRITE -- For update transaction, READ ONLY for select ones, I suppose, although not important.
  WAIT -- This is important, because we need to allow concurrent updates with wait semantics.
  ISOLATION LEVEL
    READ COMMITTED
    RECORD_VERSION
  LOCK TIMEOUT xxx -- Not sure what xxx should be, maybe not specify it at all.

If it's not possible to achieve this using any available System.Data.IsolationLevel, then I would hope that there's some other way I can get the settings i Want, by specifying some default for the process, some callback, event, hook, or whatever.

Any ideas?

(I have a separate thread with the framework authors to see if they can enable DB specific transaction settings and not just ADO stuff.)

Regards,
Kjell

[Jiří Činčura]

> Question: What exact Firebird transaction settings do I get with this
> setting? I believe the framwork ends up calling
> FirebirdSql.Data.FirebirdClient.BeginTransaction(System.Data.IsolationLevel
> level) but I haven't tracked it down properly, so can't say for certain.

Here's the code that converts S.D.IsolationLevel into Firebird terms: https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbTransaction.cs#L551

> The settings I would like is something like, in Firebird terms:
>
> SET TRANSACTION
> READ WRITE -- For update transaction, READ ONLY for select ones, I
> suppose, although not important.
> WAIT -- This is important, because we need to allow concurrent
> updates with wait semantics.
> ISOLATION LEVEL
> READ COMMITTED
> RECORD_VERSION
> LOCK TIMEOUT xxx -- Not sure what xxx should be, maybe not specify it
> at all.
> If it's not possible to achieve this using any available
> System.Data.IsolationLevel, then I would hope that there's some other
> way I can get the settings i Want, by specifying some default for the
> process, some callback, event, hook, or whatever.
>
> Any ideas?

You can't do that with just S.D.IsolationLevel. You need to have access to FirebirdClient specific methods so that you can specify FbTransactionOptions (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbTransactionOptions.cs) for BeginTransaction.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Kjell Rilbe]

Den 2024-05-20 kl. 18:40, skrev Jiří Činčura:

Question: What exact Firebird transaction settings do I get with this 
setting? I believe the framwork ends up calling 
FirebirdSql.Data.FirebirdClient.BeginTransaction(System.Data.IsolationLevel 
level) but I haven't tracked it down properly, so can't say for certain.

Here's the code that converts S.D.IsolationLevel into Firebird terms: https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbTransaction.cs#L551

OK, so for ReadCommitted I end up with a transaction with settings like these:

SET TRANSACTION
  READ WRITE
  NO WAIT
  ISOLATION LEVEL
    READ COMMITTED
    RECORD_VERSION

That would be fine except for the NO WAIT. Too bad...

The settings I would like is something like, in Firebird terms:

SET TRANSACTION
  READ WRITE -- For update transaction, READ ONLY for select ones, I 
suppose, although not important.
  WAIT -- This is important, because we need to allow concurrent 
updates with wait semantics.
  ISOLATION LEVEL
    READ COMMITTED
    RECORD_VERSION
  LOCK TIMEOUT xxx -- Not sure what xxx should be, maybe not specify it 
at all.
If it's not possible to achieve this using any available 
System.Data.IsolationLevel, then I would hope that there's some other 
way I can get the settings i Want, by specifying some default for the 
process, some callback, event, hook, or whatever.

Any ideas?

You can't do that with just S.D.IsolationLevel. You need to have access to FirebirdClient specific methods so that you can specify FbTransactionOptions (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbTransactionOptions.cs) for BeginTransaction.

I see. Thanks. Will discuss further with the framework developer.

Regards, Kjell

[Kjell Rilbe]

FWIW I added an issue suggesting a simple hook implementation that would solve this problem and allow any user of the FB provider to specify whatever FbTransactionOptions they desire for any System.Data.IsolationLevel, i.e. tweak your default:

https://github.com/FirebirdSQL/NETProvider/issues/1172

Regards,
Kjell

[Mark Rotteveel]

On 20/05/2024 18:40, Jiří Činčura wrote:
> You can't do that with just S.D.IsolationLevel. You need to have access to FirebirdClient specific methods so that you can specify FbTransactionOptions (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbTransactionOptions.cs) for BeginTransaction.

Have you considered making it configurable on the connection properties
level? For example, Jaybird allows you to define the definition in
connection properties (and also allows you to override it later on,
either permanently for an isolation level, or one-off).

Mark
--
Mark Rotteveel

[Jiří Činčura]

> Have you considered making it configurable on the connection properties
> level? For example, Jaybird allows you to define the definition in
> connection properties (and also allows you to override it later on,
> either permanently for an isolation level, or one-off).

ADO.NET has more or less standard way of doing that in connection string (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbConnectionStringBuilder.cs#L199), but again, only S.D.IsolationLevel.

But it would be something that I would consider, because it seems to be pretty straightforward and discoverable approach.