Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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');
while(my $d = $sth->fetch) {
    print "@$d\n";
$dbh->do("commit tran");
my $sth = $dbh->prepare('select @@transtate, @@trancount');
while(my $d = $sth->fetch) {
    print "@$d\n";

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
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 Peppler - Data Migrations Inc. - - - AIM MPpplr
International Sybase User Group -
Sybase on Linux mailing list: