[Dbix-class] transaction isolation levels

[Stanley Pilton]

On Mon, May 6, 2013 at 2:04 PM, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> On Mon, May 06, 2013 at 01:35:48PM +0100, Stanley Pilton wrote:
>> It does seem strange that there's something that has a meaning at a
>> per-transaction scope (as well as the wider session scope), and,
>> instead of it being controllable via txn_do or related methods, it's
>> necessary to get multiple connections, each with a different setting
>> for transaction behaviour in its wider, session, scope.
>>
>> In SQL terms, this is the difference between "SET SESSION
>> CHARACTERISTICS AS TRANSACTION .... " and "BEGIN TRANSACTION ISOLATION
>> LEVEL ...".
>
> Apply s/In SQL terms/In PostreSQL terms/. While I generally am not
> opposed to allowing some sort of fine-grained control on transaction
> starts, doing it for a single RDBMS [..]

My illustration did indeed use some SQL in a dialect specific to
Postgres.  The concepts and names of some of the isolation levels, as
well as the idea of this being controllable per-transaction, is from
SQL.  As usual with SQL, there is some varitation between dialects.  I
did not mean to imply that I thought this feature should be designed
in a way that limits it to a single SQL DBMS, though I suppose it
would be reasonable to design it generically but initially only
implement the feature for a single SQL DBMS.

To provide the SQL illustration more generically, I should have used
"SET TRANSACTION ISOLATION LEVEL ...".  This is the form from the SQL
standard, and is supported by Oracle, Microsoft SQL Server, and
Postgres.

> [..] and/or supporting it for only one
> kind of txn control (txn_do) isn't a valid approach.

I didn't intend to imply this, either.  I deliberately avoided getting
into detailed API design discussion at that point.  I noted that
txn_do was likely a point of relevance, but the phrase "it being
controllable via txn_do or related methods" was intended to leave open
the details of how such a feature might be added to the existing API.
I certainly did not intend to imply, with that phrase, that adding
this feature to the API would affect txn_do and txn_do only.  The
intention was actually the opposite: to leave it open.  But my
expectation would be that existing methods of relevance would be
looked at, and possibly extended in a backwards compatible and
coherent way.  And that there would also be the possibility of
introducing new methods.  I don't know the existing API well enough to
say much more than that at this stage.

> For one it is a
> "worse-is-better" cop-out, and more importantly - it will make long term
> generic extensions to the system rather hard if not impossible.

I hope I have persuaded you that this conclusion is based on a misunderstanding.

> To substantiate the above here is the big picture, of which each element
> needs to be taken in consideration. If you think tackling this can of
> wors is a task you can embark upon - it would be awesome!
>
> - For starters a mechanism will need to be created within DBIC to pass
>   options from both txn_do and txn_scope_guard to txh_begin (which is the
>   single point where a txn is in fact started). This alone could then be
>   sufficient for what you are doing, provided it is clearly documented as
>   "not cross-engine".

Thanks, pointers like this will be really useful.  I have had a brief
look at the implementation for transaction-related stuff, and my main
conclusion so far is that there's a lot of indirection ;).

> If one wants to go further the following needs to be considered:
>
> - Very few engines support per-transaction isolation level specification,
>   emulation with transparent reconnect may be achievable for the ones that
>   do not.

As noted, Oracle, Microsoft SQL Server, and Postgres do, which is a
fairly major overall share.  For ones without this feature, another
possible technique occurs to me that doesn't involve additional
connections.  The session-scoped isolation level could be set just
before the transaction is executed, and set back again just after.

> - Only 3 transaction isolation levels are somewhat standard
>
> - The default transaction isolation level is not the same across engines,
>   meaning that introspection of "current txn type" needs to be implemented
>   per storage subclass

Noted.

  regards, Stanley.