sybperl-l Archive

From: "Dave Aiello" <dave_aiello at ctdata dot com>
Subject: Can transactions span databases?
Date: Nov 8 2001 9:54PM

A project at one of my consulting clients has code written using Sybase::DBlib 
that attempts to operate on two separate databases through a single handle 
under explicit transaction conditions.  Some of the code I have seen is written 
as follows:

$dbh->dbuse("database1");
$dbh->dbcmd("begin transaction");
$dbh->dbsqlexec;

$dbh->dbuse("database2");
$dbh->dbcmd("begin transaction");
$dbh->dbsqlexec;

#
# Database operations here
#

$dbh->dbuse("database2");
$dbh->dbcmd("commit transaction");
$dbh->dbsqlexec;

$dbh->dbuse("database1");
$dbh->dbcmd("commit transaction");
$dbh->dbsqlexec;

Note that there are no SQL statements between the inner and outer 
transactions.  I have read the warnings in various books and on-line resources 
against nesting transactions.  But, some people at this client apparently feel 
that they should have nested database transactions because they think that 
transactions cannot span two databases, even if those databases are on a single 
Sybase server.

I suspect that transactional integrity can be preserved with only one explicit 
transaction, but I cannot find documentation that definitively says I am 
right.  So my questions:

1) Is the scope of an explicit transaction the data server or the database?

2) Does nesting transactions in this fashion have any benefit in a Sybperl 
application where all operations are taking place through a single database 
handle?  Or, is this more trouble than its worth?

Thanks for any help you can provide,

Dave Aiello
CTDATA