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