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 -0000
Received: (qmail 57191 invoked by uid 76); 28 Aug 2003 15:04:53 -0000
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 -0000
Received: (qmail 15273 invoked by uid 507); 28 Aug 2003 15:04:45 -0000
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-0000wM-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. [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
_________________________________________________________________
Get MSN 8 and enjoy automatic e-mail virus protection. http://join.msn.com/?page=features/virus
