Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000

[Chuck Fox]

Ahhh, my favorite error code, the infamous 1205.  Obviously the insert 
statement is not the problem, its the trigger that is being called.  
Please provide source for the trigger and perhaps I can help.

Chuck

[EMAIL PROTECTED] wrote:

I have a small perl script using DBI and DBD::Sybase as well as 
DBD::MySQL
It pops mail for a feedback system, after parsing the message to see 
if it's good or bad it then inserts good messages (customer feedback) 
into a Microsoft SQL 2000 database or if bad inserts them into a MySQL 
database.

Recently the Coldfusion developers and SQL admin's decided to rework 
things and put all the different site feedback tables into one table. 
So I had to change my code to just update this one table with all the 
site feedback.

But since my perl code runs from the crontab sometimes jobs overlap 
and I might be popping mail for more then one site's feedback address 
at a time and thus making multiple inserts into the MS SQL database 
server. Then I get these damn deadlock errors.

What's odd is I never get errors with MySQL, I can have all 11 site 
pop scripts running at the same time inserting bounced messages into 
the MySQL table without problem. But just 2 scripts trying to insert 
good feedback into Microsoft SQL crap out.

Is it my code? The driver? The way our SQL guys built the database?
I just recently added the syb_deadlock_retry => 3 , syb_deadlock_sleep 
=> 5 commands hoping that would help, but it did't.

Below is the code and error any insight would be very much appreciated.

Also I am using FreeTDS and have been using the same drivers and 
FreeTDS for 3 years. Without problems.

Thanks
zack
DBD::Sybase::st execute failed: Server message number=1205 severity=13 
state=50 line=5 server=SQL82 procedure=trg_cleanup text=Transaction 
(Process ID 92) was deadlocked on lock resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction. at 
./dating_pop2sql.pl line 448.
Can't execute the SQL statement: Server message number=1205 
severity=13 state=50 line=5 server=SQL82 procedure=trg_cleanup 
text=Transaction (Process ID 92) was deadlocked on lock resources with 
another process and has been chosen as the deadlock victim. Rerun the 
transaction.
1205
S1000 at ./dating_pop2sql.pl line 448.
Segmentation fault

Perl Code in question:

sub insert_good_data {

       my ($dsquery, $data_base, $data_source, $username, $password) = 
@_;
       ($dbh)          = DBI->connect($data_source, $username, 
$password, { syb_deadlock_retry => 3 , syb_deadlock_sleep => 5 })
                               || die "Can't connect: $DBI::errstr";
       $message{to} = substr($message{to},0, 49);
       $message{from} = substr($message{from},0, 49);
       $message{date} = substr($message{date},0, 49);
       $message{subject} = substr($message{subject},0, 49);
       $sql_statement = sprintf "INSERT INTO feedback
                                 (site_id, category, status, to_email, 
message_header, message_subject, from_email,program_name,message_body)
                                 VALUES 
('25','1','1',%s,%s,%s,%s,'perlpop',%s)",
                                 $dbh->quote($message{from}),
                                 $dbh->quote($message{headers}),
                                 $dbh->quote($message{subject}),
                                 $dbh->quote($message{to}),
                                 $dbh->quote($message{message_body});
               $sth = $dbh->prepare($sql_statement)
                       || die "Can't prepare the SQL statement: 
$DBI::errstr $DBI::err \n $DBI::state";
               $sth->execute || die "Can't execute the SQL statement: 
$DBI::errstr \n $DBI::err \n $DBI::state";
       $dbh->disconnect;
}

_________________________________________________________________
Get MSN 8 and enjoy automatic e-mail virus protection.   
http://join.msn.com/?page=features/virus