[Dbix-class] transaction isolation levels

[Stanley Pilton]

I have been unable to find documentation about how to use different
transaction isolation levels, or even what the default isolation level
is, generally, but also specifically for postgres.

I've read documentation such as that for the methods
DBIx::Class::Schema::txn_do and DBIx::Class::Storage::txn_begin

By experiment, I have determined that "read committed" is in use.
This is postgres's default.  How do I go about using either postgres's
"repeatable read" or "serializable" isolation levels?  I'll then want
to know what the exceptions will look like if a transaction fails at
either of these levels due to a conflict.

The program I used to experimentally determine the isolation level is:

my $do = sub {
    $childpid or sleep 1;
    my ($o) = $schema->resultset('test')->search({id => 1});
    my $q = $o->q;
    rep($childpid, "q is $q");
    sleep 2;
    my $newq = $childpid ? ($q + 7) : ($q - 4);
    $o->q($newq);
    $o->update;
    rep($childpid, "q is " . $o->q);
};

$schema->txn_do($do);
wait;
exit;

The idea is to demostrate where one thread of execution wants to
increase a value by 7, and another wants to decrease the same value by
4.  The net result, after any failed transactions are retried, should
be that the value is increased by 3.

The program produces the output following, one line each second:

P: q is 6
C: q is 6
P: q is 13
C: q is 2

Our application will require "repeatable read" (rather than "read
committed") isolation level, but it would be good to know how to
invoke "serializable" too.

I realise that there are ways to work around the race demonstrated in
the test program above, such as causing the SELECT to be "FOR UPDATE"
(takes a lock), or by issuing verbatim SQL to go with the UPDATE such
as $o->update({ q => \'q - 4'}) (makes each update of the value atomic
and within the db rather than non-atomic and within the app).  Taken
in the whole, I am pretty sure that our application here should be
written with repeatable read isolation level, though.

Fallback plan is to use plain DBI and write lots of SQL (including SQL
that says "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ"), but
we'd rather make use of DBIx::Class if possible to take advantage of
its other features.

  regards, Stanley Pilton