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:02PM

Kiriakos Georgiou writes:
 > Does anybody know if nested transactions or transaction savepoints are
 > supported with DBD-Sybase, and if so how?
 > 
 > Rational:
 > I want to log an error (via an update) before I rollback a whole bunch
 > of statements, but if I rollback the whole transaction before logging
 > the error, the record won't be locked anymore so there is no guarantee
 > that another user won't lock it before I can update it.

As long as the transactions/savepoints are issued on the same physical 
connection you should be fine.

It should work both in autocommit mode and with autocommit turned off
(but not if chained transaction mode is turned on, however.)

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
...
");

etc...

In general I've used nested transactions in stored procs, where each
proc has:

create proc ...
as
...
begin tran
save tran foo
... do stuff, possibly calling other procs
if error
  rollback tran foo
  commit tran
  return error code
endif

commit tran

Applying the same logic should work perfectly well here too.

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