[Dbix-class] Manual Transaction Handling for Sybase ?

[David Ihnen]

This really does come down to the fundamental tradeoff previously 
discussed in various places regarding transactions and autocommit;

That is, that the connection is not necessarily monopolized by 
dbix-class.  When it comes 'online' with the connection from whatever 
pool it came from, there may or may not have been any statements 
executed since the last commit/rollback!  You may also have executed 
modification commands within dbix-class outside a txn_do command - do 
you want those to dissappear or become part of the transaction?

Thus to my understanding, when you turn off autocommit, dbix-class goes 
into a 'safety' mode, stating something like:

"I cannot know how to reconstruct the entire transaction because 
previous statements may have been executed before the connection or 
before the txn_do.  I do not know if its okay to roll them back or if 
its okay to commit them, and I certainly can't redo them (since I don't 
know of them) if I lose connection - so I won't do anything at all"

I was rather miffed to discover this myself, and half-created a patch to 
fix that behavior when I learned that it was intentional!

So I created an alternative I call 'trust_dbic_transactions'.  When this 
is enabled, I am enforcing the simple application/system rule of:

If you execute any statements on the database without explicitly 
commiting it, preferably through a txn_do transaction, it will be rolled 
back.

With that rule in place, it no longer becomes necessary to hedge one's 
bets against what was done outside the txn_do.   Use txn_do to make 
modifications to the db or they don't happen.

The maintainers of DBIx::Class don't seem to find my code quality use 
case (in a nutshell enforceing the rule break-early-break-often, hence 
making bugs easier to find) impressive or useful and though they have 
expressed mild interest in what I've done, I have heard no follow-up 
interest in adding the feature to the standard module.

David

PS: In some databases doing multiple begins starts a multi-layer 
transaction.  Though Mysql does not do this, I think sybase may and I'm 
pretty sure MSSQL, Oracle and DB2 do.  This multi-layer transactional 
behavior may be masked by dbic's implementation which only executes one 
begin and holds off on further until the final commit.

Robert Heinzmann wrote:
> Hello, 
>
> I'm using DBIx::Class and I want to implement manual transactions. 
>
> Connection is set to "AutoCommit => 0". I issue manual 
>
> ----
> txn_begin()
>
> Insert
> Update
> Select 
>
> txn_commit() / txn_rollback()
> ----
>
> However no Transaction takes place. DBIC Trace shows no BEGIN or COMMIT
> / ROLLBACK.
>
> I added the following: 
>
> print Dumper($db->storage->{transaction_depth});
> $db->storage()->txn_begin() or $logger->error_die("ERROR Starting
> Transaction");
> print Dumper($db->storage->{transaction_depth});
>
> print Dumper($db->storage->{transaction_depth});
> $db->storage()->txn_rollback() or $logger->error_die("ERROR Starting
> Transaction");
> print Dumper($db->storage->{transaction_depth});
>
>
> This gives 
>
> $VAR1 = 1;
> $VAR1 = 2;
>
> $VAR1 = 2;
> $VAR1 = 1;
>
> ----
> /usr/share/perl5/DBIx/Class/Storage/DBI.pm
> (Version Lenny: ii  libdbix-class-perl                0.08010-2
> Extensible and flexible object <-> relational mapper)
> ----
> sub txn_begin {
>   my $self = shift;
>   $self->ensure_connected();
>   if($self->{transaction_depth} == 0) {
>     $self->debugobj->txn_begin()
>       if $self->debug;
>     # this isn't ->_dbh-> because
>     #  we should reconnect on begin_work
>     #  for AutoCommit users
>     $self->dbh->begin_work;
>   }
>   $self->{transaction_depth}++;
> }
>
> sub txn_commit {
>   my $self = shift;
>   if ($self->{transaction_depth} == 1) {
>     my $dbh = $self->_dbh;
>     $self->debugobj->txn_commit()
>       if ($self->debug);
>     $dbh->commit;
>     $self->{transaction_depth} = 0
>       if $self->_dbh_autocommit;
>   }
>   elsif($self->{transaction_depth} > 1) {
>     $self->{transaction_depth}--
>   }
> }
>
> sub ensure_connected {
>   my ($self) = @_;
>
>   unless ($self->connected) {
>     $self->_populate_dbh;
>   }
> }
>
> sub _populate_dbh {
>   my ($self) = @_;
>   my @info = @{$self->_dbi_connect_info || []};
>   $self->_dbh($self->_connect(@info));
>
>   # Always set the transaction depth on connect, since
>   #  there is no transaction in progress by definition
>   $self->{transaction_depth} = $self->_dbh_autocommit ? 0 : 1;
>
>   if(ref $self eq 'DBIx::Class::Storage::DBI') {
>     my $driver = $self->_dbh->{Driver}->{Name};
>     if
> ($self->load_optional_class("DBIx::Class::Storage::DBI::${driver}")) {
>       bless $self, "DBIx::Class::Storage::DBI::${driver}";
>       $self->_rebless() if $self->can('_rebless');
>     }
>   }
>
>   my $connection_do = $self->on_connect_do;
>   $self->_do_connection_actions($connection_do) if ref($connection_do);
>
>   $self->_conn_pid($$);
>   $self->_conn_tid(threads->tid) if $INC{'threads.pm'};
> }
>
>
> For me this means with AutoCommit => 0 I cant do any transactions at all
> ... 
>
> Looks like a bug ? 
>
> P.s. Storage is Sybase. Any hints ? 
>
> Regards, 
> Robert 
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>