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: Transaction Management with batch insert
Date: Dec 4 2001 7:39PM

Nicholas.Gatland@reuters.com writes:
 > Hi
 > 
 > I need a little help with transaction handling using Sybperl. I
 > have read Michael Peppler's documentation concerning Two Phase Commit
 > but I am unsure as to whether this will do what I want. 

No - two-phase commits are a different beast altogether.

What you want to do is issue a "begin tran" before the first insert
statement, execute the insert statements and monitor the error codes,
and at the end issue a rollback if there was any error, or a commit if
all is fine.

An alternative solution is to set the chained transaction mode to on
(equivalent to "autocommit" off for ODBC/JDBC drivers) which will
automatically begin a transaction for the first insert, and then issue
a rollback or commit as necessary at the end.

begin tran, rollback tran, save tran and commit tran statements can be
issued via nsql() or any other method at any time, and can span sql
statements. 

In your case I might do something like this:

$dbh->nsql("begin tran");
$rollback = 0;
$dbh->nsql(first insert statement);
$rollback++ if $DB_ERROR;
$dbh->nsql(second insert statement);
$rollback++ if $DB_ERROR;
etc...

if($rollback) {
    $dbh->nsql("rollback tran");
} else {
    $dbh->nsql("commit tran");
}

The above isn't optimum in the sense that inserts #2..7 will still be
executed even if #1 fails (although they will get rolled back), but
the code may be a little cleaner.

One thing to keep in mind when using transactions in client code is
that locks will be held for the length of the transaction, so if you
are updating a table in an active database, or if there are a lot of
concurrent updating going on for that particular table you will likely
get deadlock errors. If the traffic is low then that is not a problem,
of course.

Michael
-- 
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler@peppler.org - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com