PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
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 7:43PM

Kiriakos Georgiou writes:
 > > Assuming you are not using stored procs, something like this should
 > > work perfectly:
 > >
 > > $sth=$dbh->prepare("
 > > begin tran
 > > save tran point_one
 > > ... do something
 > > ");
 > > $sth->execute; etc.
 > > $sth->prepare("
 > > save tran point_two
 > > ...
 > > ");
 > >
 > 
 > 
 > I use $dbh->rollback; to rollback the entire transaction.

Meaning you use AutoCommit => 0, correct? (otherwise $dbh->rollback
shouldn't be able to work...)

 > Is there
 > anything magical to the
 > $dbh->rollback or can I rollback to a savepoint by simply executing a
 > 'rollback tran point_two' in a statement?

With AutoCommit = 0 and syb_chained_txn off DBD::Sybase issues a
	BEGIN TRAN 
on the first prepare() (or a prepare() after a commit or rollback)

You can issue explicit additional BEGIN TRAN/SAVE TRAN/COMMIT
TRAN/ROLLBACK TRAN using your own transaction names, which should work 
just fine.

Calling $dbh->commit would then commit the entire batch (although you
may have to have matching COMMITs for additional BEGIN TRAN), and
$dbh->rollback cancels the entire batch.

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.

(I know the correct way to do nested transactions in stored procs
using save points, but I am just a little bit unsure of this case.)

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