deadlock errors w/Perl DBD::Sybase and MS SQL 2000
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
Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
"Zachary Buckholz" <[EMAIL PROTECTED]> writes: > 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. You probably need to read up on how MS SQL handles locking. Perhaps MS SQL is using some kind of block-level locking? Deadlocks between two processes P and Q are often caused by a scenario like 1. P locks resource A (for example a disk block) 2. Q locks resource B 3. P needs B, and waits for Q to release it 4. Q needs A, and waits for P to release it 5. Deadlock! Maybe by changing the way MS SQL does locking (eg. use row-level or table-level locking) could eliminate the deadlocks. I assume that your script does multiple inserts in the same transaction (otherwise how could it deadlock?) If you performance constraints and transaction semantics allow it, you might be able to avoid the problem by committing after every insert. > What's odd is I never get errors with MySQL, I can have all 11 site I think MySQL locks the whole table by default, that would prevent deadlock in this case. > Also I am using FreeTDS and have been using the same drivers and > FreeTDS for 3 years. Without problems. FreeTDS doesn't allow placeholders, does it? But if it does, you should switch to using them rather than $dbh->quote(). - Kristian.
Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
On Wed, 27 Aug 2003 14:45:00 -0700, Zachary Buckholz wrote:
> 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.
> 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.
syb_deadlock_retry and syb_deadlock_sleep *only* work when using the
nsql() function.
> 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.
> 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
Aha.
This looks like a trigger is fired on the insert (the deadlock happens in
a stored procedure (trq_cleanup), but you use a straight insert.)
So the deadlocks stem from the way the database is designed, not directly
from your code: when two inserts happen simultaneously the trigger then
needs to lock pages or rows in a different location (or in a different
table) and fails because each needs one or more resource locked by the
other.
You either need to get the DBA/database developers to fix this problem, or
code around it. The latter is done by testing for error 1205 and simply
re-running the operation if it occurs. You can check the code for nsql()
(in Sybase.pm) for an example that handles deadlock retries.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
Thank You, we had a similiar trigger issue two weeks ago and it took several
days to figure it out. This was the first thing I asked the DBA yesterday he
said no there are no triggers.
I went to the other DBA this morning and asked him to look and he said yes.
You were right procedure=trg_cleanup is causing the issue.
Thank You
Zack
From: "Michael Peppler" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
Date: Thu, 28 Aug 2003 08:04:02 -0700
MIME-Version: 1.0
Received: from onion.perl.org ([63.251.223.166]) by
mc1-f20.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Thu, 28
Aug 2003 08:05:51 -0700
Received: (qmail 57208 invoked by uid 1005); 28 Aug 2003 15:04:53 -
Received: (qmail 57191 invoked by uid 76); 28 Aug 2003 15:04:53 -
Received: from [EMAIL PROTECTED] (HELO ran-out.mx.develooper.com)
(64.81.84.115) by onion.perl.org (qpsmtpd/0.26) with SMTP; Thu, 28 Aug 2003
08:04:53 -0700
Received: (qmail 15281 invoked by uid 225); 28 Aug 2003 15:04:46 -
Received: (qmail 15273 invoked by uid 507); 28 Aug 2003 15:04:45 -
Received: from main.gmane.org (HELO main.gmane.org) (80.91.224.249) by
one.develooper.com (qpsmtpd/0.27-dev) with SMTP; Thu, 28 Aug 2003 08:04:14
-0700
Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id
19sOKQ-0002WK-00 for <[EMAIL PROTECTED]>; Thu, 28 Aug 2003 17:04:50 +0200
Received: from sea.gmane.org ([80.91.224.252]) by main.gmane.org with esmtp
(Exim 3.35 #1 (Debian)) id 19sOKP-0002W8-00 for
<[EMAIL PROTECTED]>; Thu, 28 Aug 2003
17:04:49 +0200
Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id
19sOJg-wM-00 for
<[EMAIL PROTECTED]>; Thu, 28 Aug 2003
17:04:04 +0200
X-Message-Info: JGTYoYF78jHwEuDVfqlo2jf07FG0v/yd
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
Precedence: bulk
List-Post: <mailto:[EMAIL PROTECTED]>
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Subscribe: <mailto:[EMAIL PROTECTED]>
Delivered-To: mailing list [EMAIL PROTECTED]
Delivered-To: [EMAIL PROTECTED]
X-Injected-Via-Gmane: http://gmane.org/
Lines: 50
Message-ID: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
X-Complaints-To: [EMAIL PROTECTED]
User-Agent: Pan/0.13.4 (She had eyes like strange sins.)
Sender: news <[EMAIL PROTECTED]>
X-SMTPD: qpsmtpd/0.27-dev, http://develooper.com/code/qpsmtpd/
X-Spam-Check-By: one.develooper.com
X-Spam-Status: No, hits=-0.2 required=7.0
tests=CARRIAGE_RETURNS,FORGED_RCVD_FOUND,QUOTED_EMAIL_TEXT,REFERENCES,SPAM_PHRASE_01_02,USER_AGENT
version=2.44
X-SMTPD: qpsmtpd/0.26, http://develooper.com/code/qpsmtpd/
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 28 Aug 2003 15:05:52.0600 (UTC)
FILETIME=[DF921580:01C36D75]
On Wed, 27 Aug 2003 14:45:00 -0700, Zachary Buckholz wrote:
> 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.
> 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.
syb_deadlock_retry and syb_deadlock_sleep *only* work when using the
nsql() function.
> 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.
> 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
Aha.
This looks like a trigger is fired on the insert (the deadlock happens in
a stored procedure (trq_cleanup), but you use a straight insert.)
So the deadlocks stem from the way the database is designed, not directly
from your code: when two inserts happen simultaneously the trigger then
needs to lock pages or rows in a different location (or in a different
table) and fails because each needs one or more resource locked by the
other.
You either need to get the DBA/database developers to fix this problem, or
code around it. The latter is done by testing for error 1205 and simply
re-running the operation if it occurs. You can check the code for nsql()
(in Sybase.pm) for an example that handles deadlock retries.
Michael
--
Michael Peppler Data Migrations, Inc.
Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
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
