SQL Exception: deadlocked

Hi,

Ran across this error in my log files. I know what causes a deadlock. Can any shed some light on how to correct/avoid this error?

(Using v6.0, connecting to MS SQL Server 2005 (SP4))

Thanks,
Chris
--------------------

YF:2013-01-06 09:17:32: INFO (HtmlExporter:C) - Exporting report to HTML (91729: CLS-Cancelled Documents)
YF:2013-01-06 09:17:33: WARN (DBAction:closeResultSet) - Error occured closing result set: java.sql.SQLException: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
java.sql.SQLException: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:765)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:581)
at net.sourceforge.jtds.jdbc.JtdsResultSet.close(JtdsResultSet.java:474)
at com.hof.util.DBAction.closeResultSet(DBAction.java:292)
at com.hof.ip.manager.PersonFavouriteManager.selectPersonFavourite(PersonFavouriteManager.java:267)
at com.hof.mi.process.ReportBroadcastProcess.A(ReportBroadcastProcess.java:1893)
at com.hof.mi.process.ReportBroadcastProcess.broadcastReport(ReportBroadcastProcess.java:1812)
at com.hof.mi.servlet.ReportBroadcastTask.runBroadcastTask(ReportBroadcastTask.java:170)
at com.hof.mi.servlet.TaskScheduler$_A.A(TaskScheduler.java:100)
at com.hof.mi.servlet.TaskScheduler$_A.run(TaskScheduler.java:151)
at java.lang.Thread.run(Unknown Source)

Hi Chris,

What build of 6.0 are you using? You can see the build information via 'system information' under Administration.
When we come across deadlocks, we are usually able to put in some code changes to stop it occurring, and it's possible that we have already resolved this in a later release.

Regards,
David

Here's the build info.

Current Schema Version: 20120319
Original Code Version: 20090227
Current Code Version: 20121019

Chris

Thanks Chris,

A support task has been created (TASK ID = 124046) which will be assigned to the dev team next week to see if we can find a way around this.

It looks like it's related to the broadcast recipient list.
Are you seeing this at certain times of the day when a broadcast is being sent?

Regards,
David

Thanks David. Yes, we see it in the morning when we have a number of broadcasts set to go. We do try to stagger them so they don't step on each other.

Chris

Hi Chris,

Thanks for the clarification.
The task has been allocated to the dev team, so we're aiming for the upcoming 6.0 Jan release (Friday 25th Jan).

Regards,
David

Excellent. I'll look for it.

Best Regards,

Chris

Hi Chris,

It looks like we do not need to make a change to the YF code to resolve your issue.

Can you try running the following against your YF DB;

[code]
ALTER DATABASE
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE[/code]

Where is the name of the Yellowfin database.

You will need to restart YF after making this change, and as with any DB change, can you please backup the DB first.

Please let us know how it goes.

Regards,
David

Hi,

We have applied the ALTER statements to the database (applied 2013/2/2). So far we have not had any deadlock issues. Will continue to monitor though.

Thanks,
Chris

Hi,

We have applied the ALTER statements to the database (applied 2013/2/2). So far we have not had any deadlock issues. Will continue to monitor though.

Thanks,
Chris

Awesome news Chris!