DeadLock issue with SQL Server 2005

• muthukumaran_m

  SSC Enthusiast

  Points: 119

  More actions

  November 5, 2007 at 7:29 am

  #73851

  Hi,

  I am facing the following issue only with SQL Server (not with Oracle)

  -----------------------------------------------------------------------------------

  09:04:38,313 WARN [TransactionImpl] XAException: tx=TransactionImpl:XidImpl[FormatId=257, GlobalId=

  wkx-100095/173, BranchQual=, localId=173] errorCode=XA_UNKNOWN(0)

  javax.transaction.xa.XAException: java.sql.SQLException: ROLLBACK:Status:0 msg:*** SQLJDBC_XA DTC_ER

  ROR Context: xa_rollback, state=1, StatusCode:-4 (0xFFFFFFFC) ***

  at com.microsoft.sqlserver.jdbc.SQLServerXAResource.rollback(Unknown Source)

  at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.rollback(XAManagedConnection.java:

  -----------------------------------------------------------------------------------

  On analyzing the issue, I found that this is because of a deadlock. Following is the deadlock trace from the SQL Server Profiler

  UPDATE BATCH_DOCUMENT SET STATUS=@P0, UPDATED_DATE=@P1, UPDATED_BY=@P2 WHERE BATCH_ID=@P3 AND DOC_INDEX=@P4

  unknown

  sp_cursorclose 180150003

  SELECT TOP 18 * FROM BATCH_DOCUMENT WHERE BATCH_ID = @P0 AND STATUS =@P1

  unknown

  sp_cursorclose 180150031

  UPDATE BATCH_DOCUMENT SET STATUS=@P0, UPDATED_DATE=@P1, UPDATED_BY=@P2 WHERE BATCH_ID=@P3 AND DOC_INDEX=@P4

  unknown

  sp_cursorclose 180150003

  Scenario: The error is thrown when multiple threads are trying to update a column a table. Each threads updates individual rows (No two threads will update the same row).

  Please help me in understanding the cause of the deadlock.

  Thanks,

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  November 5, 2007 at 10:55 am

  #748344

  Check if using ROWLOCK hint helps...

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 5, 2007 at 11:37 pm

  #748518

  Take a look at the indexing on the Batch_Documents table, since it appears in all the deadlocks. Make sure that the updates and select are running as fast as possibel and aren't doing unnecessary table scans.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass