Set 'transaction-isolation' for datasources in JBoss EAP

Environment

• Red Hat JBoss Enterprise Application Platform (EAP)
  • 7
  • 6

Issue

• How can transaction-isolation be configured for connections from datasource pools managed by JBoss EAP?
• Can Microsoft SQL Server's custom SNAPSHOT isolation (value=4096) be configured for managed datasources?

Resolution

Disclaimer: Links contained herein to external website(s) are provided for convenience only. Red Hat has not reviewed the links and is not responsible for the content or its availability. The inclusion of any link to an external website does not imply endorsement by Red Hat of the website or their entities, products or services. You agree that Red Hat is not responsible or liable for any loss or expenses that may result due to your use of (or reliance on) the external site or content.

• Named transaction isolation levels defined by the JDBC Content from docs.oracle.com is not included.Connection interface may be specified using the datasource transaction-isolation¹ property:

  • TRANSACTION_READ_UNCOMMITTED
  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE
  • TRANSACTION_NONE

• As a result of a code change for Content from issues.jboss.org is not included.JBJCA-894, in EAP 6.0 CP1 (6.0.1) and later, custom (e.g. integral) levels may be specified such as 4096 (the integral equivalent of Microsoft SQL Server's Content from docs.microsoft.com is not included.SNAPSHOT):

                      ...
                      <transaction-isolation>4096</transaction-isolation>
                      ...
  

• Though the transaction-isolation property is the recommended means of setting isolation, it may also be configured using the datasource new-connection-sql property:

                      ...
                      <new-connection-sql>SET NOCOUNT OFF;SET TRANSACTION ISOLATION LEVEL SNAPSHOT</new-connection-sql>
                      ...
  

• On a per-connection basis, the Content from docs.oracle.com is not included.Connection.setTransactionIsolation(int) method may be used to set/alter the transaction isolation dynamically.

Configure transaction-isolation Using JBoss CLI

In Standalone Mode

/subsystem=datasources/data-source=MSSqlDS/:write-attribute(name=transaction-isolation,value=TRANSACTION_READ_COMMITTED)

In Domain Mode

/profile=full/subsystem=datasources/data-source=MSSqlDS/:write-attribute(name=transaction-isolation,value=TRANSACTION_READ_COMMITTED)

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.