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
