ransaction (Process ID 52) was deadlocked on lock resources + SQL Server 2005

[Jigar Naik]

Hi i m getting below exception....

i have one process which update a table and at the same time another process is trying to update the same table at the same time..

I am using SQL server 2005 and for updating i am using Stored Procedure.

- INFO : Campaign Executing Campaign Scheduler : Next Execution Time : Fri Sep 25 15:56:00 GMT+05:30 2009
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
	at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at com.interactcrm.broadcast.dao.impl.BroadcastManager.updateDeliveryReport(BroadcastManager.java:53)
	at com.interactcrm.broadcast.sms.impl.DeliveryStatusServlet.doPost(DeliveryStatusServlet.java:59)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
	at java.lang.Thread.run(Unknown Source)

can anybody help me out solving this issue ???

[Paul Sturrock]

Well deadlocks are expected behaviour now and again in a relational database, which is why databases have recovery mechanisms. If it happens all the time (in a repeatable way) you have an issue in your stored procedure which you will need to address. If it happens fequently in an unrepeatable way you possibly have an issue with your data model (typically a poorly normalized data model), or your transaction times may be too long. It it happens only very occasionally you can code round it - catch the SQLException, check the error code == deadlock and retry the database operation.