Add hook to tweak transaction options when using System.Data.IsolationLevel interface

[krilbe]

When using frameworks that support ADO but are agnostic about what the underlying database is, and the framework is creating transactions using only System.Data.IsolationLevel transaction settings, it's currently impossible to specify Firebird specific transaction options, e.g. WAIT.

Please add a hook or settings to tweak how the Firebird provider maps System.Data.IsolationLevel to Firebird transaction settings.

For example, at the end of FbTransaction.BuildTpb(), before returning, you could add something like this:

if (OptionsHook != null)
{
    options = OptionsHook(options, IsolationLevel);
}

And add the public static property OptionsHook as follows:

public static Func<FbTransactionOptions, IsolationLevel, FbTransactionOptions> OptionsHook { get; set; } = null;

In the app code using that ADO framework, this would for example allow to specify WAIT in the callback:

FbTransaction.OptionsHook = (opt, il) => {
    if (il == IsolationLevel.ReadCommitted)
    {
        opt.TransactionBehavior &= ~FbTransactionBehavior.NoWait;
        opt.TransactionBehavior |= FbTransactionBehavior.Wait;
    }
    return opt;
};

[cincuranet]

1. That's very non-standard.
2. The framework should provide a way for you to customize transaction (i.e. EF Core allows that).

[krilbe]

I agree about your second point. About the first point, yes, perhaps.

But I do think that your choice to map System.Data.ReadCommitted to use NoWait seems very arbitrary, and that this should really be up to us who use the provider to make that choice. Same goes for RecVersion/NoRecVersion.

So, it seems like a fair idea to provide some means to at least choose between Wait/NoWait and between RecVersion/NoRecVersion. One static property each? I.e.

public static bool DefaultUseWait { get; set; } = false;
public static bool DefaultUseRecVersion { get; set; } = true;

FWIW I have an open case with the framework provider too.

[krilbe]

We might be willing to sponsor implementation of those two options (or at least the Wait/NoWait option, which is really the one we need).

[cincuranet]

to use NoWait seems very arbitrary

It is what other Firebird tools use and FirebirdClient aligns with that.

One static property each? I.e.

Slapping static property onto a class isn't a good design. Besides, there's already a way to customize transaction all you want using this method.

It is a question of data access framework you use to support this (and it really should, because transactions are fundamental for (relational) databases). As I mentioned, i.e. EF Core supports that; LLBLGen Pro supports that, ...