SQL Deadlocks

[Kevin_Gray]

Hi List,

Lately I've been seeing the following message multiple times a day in the web server jas.log file.

2008-04-30 08:43:21.151 [ Worker Thread - OWVirtual_Thread_Group73 Assigned To com.jdedwards.runtime.virtual.OWVirtual@c0a85c0] JAS : JASResultSet could not be retrieved
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Transaction (Process ID 2949) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Does anyone know why we would be getting these errors and does JDE automatically restart these victimized processes?

Note: I'm not really a SQL guy so any help would be greatly appreciated.

Regards,
Kevin

 

[altquark]

You might want to consider moving to SQL2005 - it has a MUCH better way to deal with deadlocks than SQL2000 did. You will have to upgrade your service pack to the latest version, and carefully plan for any external integration.

More than likely, there is a type of transaction which has grown with use of the system - and the amount of time it "locks" a specific table is incurring a deadlock with other processes also going after the same resource. If you can identify what process this is occurring on, then you should be able to identify the table. What IS interesting is that you seem to be using JDBC to connect - so I presume you have a JAS server running under Xe ?

JDE doesn't automatically restart the "victimized" process - it WILL however generate an error in the application.

My strong recommendation is to investigate an upgrade to SQL 2005 - after all, SQL 2000 is no longer supported by Microsoft, or Oracle.

 

[Kevin_Gray]

Hi Jon,

You are pretty close with your presumption, we have a JAS server running under ERP8. We are having a hard time identifying the processes that are getting deadlocked though. I also believe that you are right about the type of transaction growing. We are definitely seeing a performance decrease in the Work Order area in the past couple of months.

Thanks for your recommendation on upgrading to SQL 2005. I'm trying to put together some reasons for upgrading and this should help a bit. We're getting a new Enterprise server soon so we might make this upgrade part of that project.

Kevin