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 MBAY dot NET>
Subject: DBlib Error handling
Date: Jan 6 1999 5:27PM

>>>>> "Nigel" == Smith, Nigel  writes:

Nigel> However ( there's always a however! ), one problem I have is
Nigel> with error detection: I understand about setting error/message
Nigel> handler routines with dbmsghandle and dberrhandle but what I
Nigel> don't understand is what to do once I'm in one of them! Is it
Nigel> possible to access the various status fields that enable me to
Nigel> determine why the error handler has been called? My specific
Nigel> problem at the moment is to try and recover from a deadlock but
Nigel> I'm having trouble knowing when I've got one and also in
Nigel> getting the script to continue after I'v handled it.

deadlock handling is kinda tricky, and depends on the situation.

Sybase sets error # 1205 when it decides to kill your query in a
deadlock situation. In general it's possible to simply re-run the
query that was terminated that way. To do so you obviously need to
have stored the query somewhere, and you need to have access to the
actual error code that was set in the message handler.

One way to do this is something like this:

First - when you create the $dbh, create an additional last error hash 
entry:

$dbh = new Sybase::DBlib user, pwd, server, appname, { LAST_ERROR =>
0}; 

(you can leave appname blank/undef if you want).

Now, in your message handler you do:

sub message_handler
{
    my ($dbh, $message, $state, $severity, $text, $server, $procedure, $line)
	= @_;

    # Don't display 'informational' messages:
    if ($severity > 10)
    {
	$dbh->{LAST_ERROR} = $message;

	etc...
    }
    etc...
}


And finally when you execute your query:

my $sql = "the query that causes the deadlock";
RETRY: $dbh->dbcmd($sql);
if($dbh->dbsqlexec != SUCCEED) {
    if($dbh->{LAST_ERROR} == 1205) {
	goto RETRY;
    }
}
my $ret;
while(($ret = $dbh->dbresults) != NO_MORE_RESULTS) {
    if($ret == FAIL && $dbh->{LAST_ERROR} == 1205) {
	$dbh->dbcancel;    # not sure if this is needed
	goto RETRY;
    }
   etc...
}

Now however you should still be carefull. If your query is written as
a multi-statement batch (ie more than one statement) the failure will
occur in the dbresults() call, not in the dbsqlexec() call. In that
case I am not *certain* if part of the batch will have been committed
or not if the code is not wrapped in a transaction. For example, if
$sql is:

update foo set bar = 123 where baz = 456
delete boz where xyz = 'xyzzy'

I am not *certain* if a deadlock happens on the delete if the update
will get rolled back or not (but others can certainly answer that, I'm 
sure).

In case of doubt wrap the calls in a transaction, that will guarantee
that the operation is atomic.

Hope this helps and doesn't confuse you further ;-)

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com