[Dbix-class] transaction isolation levels

[Peter Rabbitson]

On Mon, May 06, 2013 at 01:35:48PM +0100, Stanley Pilton wrote:
> On Mon, May 6, 2013 at 9:33 AM, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> > It is entirely out of the scope of DBIC to handle isolation levels,
> > and as you correctly noticed you observe the default isolation level
> > upon connection.
> >
> > All you need to do is get a connection (or several for different levels,
> > in case you need them) with the proper on_connect_do [1] magic [2] being
> > executed on every (re)connect.
> >
> > Does this answer your question or did I miss something...?
> 
> This is helpful and will be good enough for proceeding with this application.
> 
> 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 and/or supporting it for only one 
kind of txn control (txn_do) isn't a valid approach. 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.

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

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.

- 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

Cheers