Sybperl - Problem with Handling Transactions with Commit and Rollback

cool_ravi2 has asked for the wisdom of the Perl Monks concerning the following question:

I have a set of updates that need to be done on a set of database tables. The whole update operation must be part of a single transaction. My code for this operation goes as follows.

use Sybase::DBlib;

my $dbh = new Sybase::DBlib( <username>, <password>, <server> );
#A reference to my own error handler for sybase errors
dberrhandle( \&myerrorhandler );

# if status flag is 1 then transaction was successful else the transac
+tion was not successful
$status = 1;
sub myerrorhandler
{
< when error occurs the status flag $status is set to 0 >
$status &=0;
&INT_CANCEL; # in order to continue with the rest of the process
}


$dbh->nsql( "begin transaction" );
$dbh->nsql( <update query 1> );
.
.
.
$dbh->nsql( <update qurey n> );

if ( $status )
{
    $dbh->nsql( "commit" );
}
else
{
    $dbh->nsql( "rollback" );
}
[download]

When I run this code and some kind of database error occurs in update query number 3. The rest of the updates from 3 to n do take place. But since the status is now false, the database has to do a rollback. But the problem is that the updates 1 and 2 get rolled back but the updates that were done after query 3( where the database error occured ) are not rolled back.

Could someone pls tell me about this unpredictable behavior and how can we get around this problem.

Thank you,
Ravi Malayambakkam.

Comment on Sybperl - Problem with Handling Transactions with Commit and Rollback Download Code

Replies are listed 'Best First'.
Re: Sybperl - Problem with Handling Transactions with Commit and Rollback by Aragorn (Curate) on May 16, 2003 at 14:36 UTC
Sybase probably supports transactions, so maybe `Sybase::DBlib` provides an API for that. Another possibility is to use DBI and DBD::Sybase which supports transactions and rollbacks. Arjen	[reply] [d/l]
Re: Sybperl - Problem with Handling Transactions with Commit and Rollback by VSarkiss (Monsignor) on May 16, 2003 at 14:53 UTC
I don't think your problem is with Sybase::DBlib, which is just an interface to DB-Library, so much as in your transaction management logic. It's been about a year since I worked with Transact-SQL, but I think the error in your "query 3" is causing the transaction to roll back; therefore, the updates following it are not done as part of the original transaction, and thus the rollback has no effect. Check the transactions reference in the Sybase manuals to see if your error will cause automatic rollback. To get around the problem, you may want to check your `$status` variable before even initiating the update. Hard to say without knowing the structure of the rest of your code.	[reply] [d/l]
Re: Sybperl - Problem with Handling Transactions with Commit and Rollback by derby (Abbot) on May 16, 2003 at 15:07 UTC
VSarkiss is correct. Although I would switch to DBI (with RaiseError set), here's an approach: `eval { $dbh->nsql( "begin transaction" ); $dbh->nsql( <update query 1> ); die "problem with query 1" if $status == 0; . . $dbh->nsql( <update qurey n> ); die "problem with query n" if $status == 0; }; if( $@ ) { $dbh->nsql( "rollback" ); } else { $dbh->nsql( "commit" ); }` [download] -derby	[reply] [d/l]