|
|
sybperl-l Archive
Up Prev Next
From: Nicholas dot Gatland at reuters dot com
Subject: Re: Transaction Management with batch insert
Date: Dec 5 2001 4:09PM
Michael
Tks - just what I needed - it works a treat.
Am I right in saying that $DB_ERROR is a temporary value? So that to actually see the sybase errors generated for each statement when the script is run I would have to modify your syntax to read something like:
$dbh->nsql(first insert statement);
if ($DB_ERROR)
{
$rollback++;
print "$DB_ERROR \n";
}
etc
etc
The reason I ask is that if I include a print "$DB_ERROR \n"; at the end of my script with the rollback tran / commit tran statements, nothing is returned?
Is there a more efficient equivalent of my suggestion above?
Tks
Nick
Michael Peppler
rg> cc:
Sent by: Subject: Re: Transaction Management with batch insert
owner-SYBPERL-L@lis Header: Internal Use Only
t.cren.net
04/12/2001 19:39
Please respond to
SYBPERL-L
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
------------------------------------------------------------- ---
Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
|
