Bug 862306

Summary: Update jbpm.esb/jbpm-ds.xml configuration for MSSQL databases.
Product: [JBoss] JBoss Enterprise SOA Platform 5 Reporter: Marek Baluch <mbaluch>
Component: ToolingAssignee: Julian Coleman <jcoleman>
Status: VERIFIED --- QA Contact: Marek Baluch <mbaluch>
Severity: high Docs Contact:
Priority: high    
Version: 5.3.0 GACC: jcoleman, jpechane, soa-p-jira
Target Milestone: ER1   
Target Release: 5.3.1   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Release Note
Doc Text:
Issues arose from using jBPM with MS SQL Server 2005. When using these together, jBPM would experience deadlocks. This has been addressed through a revision of the mssql-ds.xml file. As a result, these deadlocks no longer occur.
 Story Points: ---
Clone Of: Environment:
Last Closed: Type: Task
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Marek Baluch 2012-10-02 15:10:57 UTC 
Description of problem:

Customers have been experiencing jBPM deadlocks. Please see https://bugzilla.redhat.com/show_bug.cgi?id=802756.

Suggested fix for that issue is the following configuration of the jBPM datasource.

- Add <new-connection-sql>SET TRANSACTION ISOLATION LEVEL SNAPSHOT;</new-connection-sql> to jbpm.esb/jbpm-ds.xml

<datasources>
    <local-tx-datasource>
        <jndi-name>JbpmDS</jndi-name>
        <connection-url>jdbc:sqlserver://xxxx.redhat.com:1433;DatabaseName=yourdatabase</connection-url>
        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
        <user-name>xxxx</user-name>
        <password>xxxx</password>
        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
        <new-connection-sql>SET TRANSACTION ISOLATION LEVEL SNAPSHOT;</new-connection-sql>
        <min-pool-size>15</min-pool-size>
        <max-pool-size>50</max-pool-size>
        <metadata>
            <type-mapping>MS SQLSERVER2000</type-mapping>
        </metadata>
    </local-tx-datasource>
</datasources>
Comment 1 Julian Coleman 2012-10-30 16:42:38 UTC 
Resolved with revision 11565 of:
  soa-p/src/main/resources/soa-p/schema-tool/mssql2008/template/mssql-ds.xml

Commit message:
  BZ-862306
  Add "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" to fix jBPM deadlocks on
  SQL Server.
Comment 2 Marek Baluch 2012-11-19 08:43:27 UTC 
Verified on 5.3.1.ER1.