[Dbix-class] transaction isolation levels

[Peter Rabbitson]

On Mon, May 06, 2013 at 03:28:30PM +0100, Stanley Pilton wrote:
> 
> 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.

Hehe, except it isn't ;) In MSSQL the isolation level is a *session* 
setting, not a transaction setting. That is if you would need to reset 
it to the default after each successful commit or somesuch, if you are 
after consistency. I can't find the exact Oracle docu on the subject, 
and my testrig is down, but I believe it also maintains one setting per 
session, not per txn. In other words Pg is still a special snowflake in 
this regard ;)

> 
> > [..] 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.

Which is why I outlined an exhaustive-ish list of what I can think of 
knowing the API ;) It wasn't intended as an attack, it was intended as a 
reference point.

> > 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.

Fair enough ;)

> 
> > 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,

See above

> .. 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.

Which is what you would have to do for MSSQL anyway, no matter which way 
you chose. I retract the bit about "need extra connections" as I didn't 
think stuff clearly through the first time around.

> 
> > - 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.
> 

In conclusion - I didn't mean to dismiss your idea, nor overpaint it in 
extra complexity (which sadly is there, whether we like it or not). The 
response was worded the way it was as a response to the original premise 
"OMGWTFBBQ DBIC DOES NOT SUPPORT ISOLATION LEVEL SETTING?!?!"

I am still very much looking for something useful to come out of this thread.

Cheers!