sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: RE: nested transactions / savepoints
Date: Nov 22 2000 8:17PM

Michael Peppler writes:
 > I'm not 100% certain that using named transactions for the BEGIN TRAN
 > issued by DBD::Sybase is the right thing to do. If you have the
 > ability to test this part of the code you could pull the transaction
 > name out of the code (it's in syb_db_opentran() in dbdimp.c, and of
 > course also in syb_db_commit() and syb_db_rollback()) to see if this
 > is better.

OK - well I wanted to make sure I got this right... so I wrote a
little test script. Assume a table called test with an identity col
and a name varchar() col (AutoCommit is OFF):

$dbh->do("insert test(name) values('foo')");
$dbh->do("begin tran");
$dbh->do("save tran foo");
$dbh->do("insert test(name) values('foo2')");
$dbh->do("rollback tran foo");
my $sth = $dbh->prepare('select @@transtate, @@trancount');
$sth->execute;
while(my $d = $sth->fetch) {
    print "@$d\n";
}
$dbh->do("commit tran");
my $sth = $dbh->prepare('select @@transtate, @@trancount');
$sth->execute;
while(my $d = $sth->fetch) {
    print "@$d\n";
}
$dbh->commit;

This correctly inserts a row with 'foo' in name, and the 'foo2' row is 
rolled back.

If the last statement is changed from a $dbh->commit to a
$dbh->rollback then no data is commited.

So it looks like things work as they should.

One thing that you should avoid in AutoCommit OFF mode, however, is to 
issue a
	BEGIN TRAN 
because nested *named* transactions are likely to confuse Sybase. See
the Transact-SQL manual (Chapter 18 in the 12.0 manuals) for details
on transaction handling and nested transactions.

NOTE: if you issue a 
		$dbh->do("begin tran"); 
      and don't match it with a 
		$dbh->do("commit tran"); 
      then the $dbh->commit call will NOT commit the entire batch!!!

Michael
-- 
Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
http://www.mbay.net/~mpeppler - mpeppler@mbay.net - AIM MPpplr
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com