Forums » Interstage BPM Knowledgebase Articles

• C.Y. Chen

  

  • 33 posts
   

  February 22, 2012 2:19:52 AM PST

  Goal: How to Avoid DB Deadlock in DB Adapter Causing Interstage BPM Server to Stall
   
  Fact: Microsoft SQL Server 2005
   
  Fact: Interstage BPM (Business Process Manager) v7.4 AE (Advanced Edition) Build IF740244
   
  Fact: DB Adapter dbadapter.log
   
  Symptom:  Interstage BPM Server stalls due to DB deadlocks.  Warning message found in dbadapter.log is:
   
  WRN:Mon Nov 07 14:22:34 CET 2011: TID=11681653: DbConnectionImpl(94)                                        : getIteratorValues: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (SQL Error Code: 1205, Retry Number: 1)
   
  Fix:
  The following SQL Server 2005 setting help avoid DB deadlock encountered by DB Adapter.
  
  The query to change the transaction isolation level of the database is as
  follows:
  
  ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON
   
  To check the change in “READ_COMMITTED_SNAPSHOT” setting you can execute the
  query given below:
  
  SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name=
  <database_name>
   
  Example:
  ALTER DATABASE teamflowdb SET READ_COMMITTED_SNAPSHOT ON
  SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'teamflowdb'
   
  Please note the following when trying to set the “READ_COMMITTED_SNAPSHOT”
  option:
  
  Make sure the Interstage BPM application server / any other custom application using the database is stopped while executing this command.
   
  When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing
  the ALTER DATABASE command is allowed in the database.
   
  ** There must be no other open connection in the database until ALTER DATABASE
  is complete. The database does not have to be in single-user mode. **
   
  Reference:
  http://msdn.microsoft.com/en-us/library/ms173763.aspx
  http://stackoverflow.com/questions/51969/how-to-detect-read-committed-snapshot-
  is-enabled
   
   
  

  Author: C.Y. Chen
  Attachment: