Tersus: Database Transaction Support in Tersus (2.1.6 and later)

Database Transaction Support in Tersus (2.1.6 and later)Date: Mar 19, 2012Transaction Types

Tersus - A transaction implemented by the Tersus server. A Tersus transaction may apply to more than one data source, in which case, a commit/rollback applies to all participating data sources at once.
Note that since this type of transaction is not implemented using a two-phase commit protocol, there's a small probability that in cases of system failure, a commit will be applied to some, but not all, of of the data sources involved in the Tersus transaction.

Distributed - A transaction implemented using one of the 3^rd-party transaction managers supported by Tersus, covering interaction with all data sources configured to participate in it. 3^rd-Party transaction managers typically use a two-phase commit protocol, which should ensure better reliability in case of failure, but in some cases may have a significant performance overhead. Tersus currently supports Atomikos. Working with JOTM is possible but discouraged.
Integration with 3rd-party transaction managers is an enterprise feature, and is not supported by the open-source version of Tersus.

Auto-commit - Each SQL statement is a transaction in itself, committed immediately when issued. An error in one has no effect on previous or subsequent statements in the same server-side call.

DefinitionsTransaction scopeBy default, the transaction scope is defined implicitly by the scope of a Server-side call. The transaction starts when the server-side call begins, and is committed when the call ends, unless an unhandled exception occurs mid-call, in which case a rollback will be performed.The transaction can be controlled explicitly, using Commit and Rollback plugins (in the Database category), following which a new transaction is implicitly started.Server-side callA server-side call usually refers to one of the following:

The most common implementation is a model implemented using Tersus/Basic/Service, and called from the client-side. Note that if Service B is located in Service A which is called from the client-side, there is only one server-side call, whose scope is defined by A.

A model implemented using Tersus/Display Actions/Show File. Note that as above, even in cases where Show File contains a service, the call scope is defined by Show File.

A model implemented using Tersus/Basic/Callable Service.

A model implemented by a plugin which is not a display plugin, and which is a direct child of a model implemented using Tersus/Basic/System.

An instance of a server-side Timer/Activity.

A model implemented using one of the Tersus/Database/... plugins, and called from the client-side, when client-side database is not setup (clientSideDatabase=server).

Main data sourceEach server-side call has a main data source. By default the main data-source is jdbc/Main but this setting can be overridden by setting the dataSource property of the model definition the to the name of another data source configured in your application. The main data-source determines, along with the additional settings, what type of transaction is performed.Configuration settingsThe following settings effect which kind of transaction will take place:dataSource
Shared property of model.See Main data source definition.<Auto Commit>Trigger on database plugin.If this trigger is specified, and receives a boolean Yes value, the database action will be performed in auto-commit, regardless of any active transaction.auto_commit_data_sourcesParameter in context/configuration file.Specifies which data sources are accessed using an Auto-commit transaction.
If the parameter is missing, all data sources except the Main data source will be accessed.
If a data source is configured for Distributed transaction, and such a transaction takes place, then it's inclusion in auto_commit_data_sources is ignored.3^rd-party transaction manager configuration settingsIn context/configuration file.Specifies whether Distributed transactions can be used, and which data sources can participate in it (See the table below for more details).Currently Atomikos and JOTM transaction managers can be used. See specific configuration details below. Configuration matrixThe following table summarizes all possible configurations, and the transaction types employed on each data source.

Configuration

Data Sources and transaction types

Main data source
configured for distributed transactions

auto_commit_data_sources parameter

Main

Not defined

Tersus

Auto-commit

N/A

Auto-commit

N/A

Yes

Not defined

Distributed

N/A

Auto-commit

N/A

Defined, w/o Main data source

Tersus

Auto-commit

Tersus

Auto-commit

Yes

Defined, w/o Main data source

Distributed

Auto-commit

Defined, with Main data source

Auto-commit

Tersus

Auto-commit

Tersus

Auto-commit

Yes

Defined, with Main data source

Distributed

Auto-commit

Where configuration of data sources 1 through 4 above, is outlined in the following table:

Data source

Included auto_commit_data_sources parameter

Configured for Distributed transactions

Yes

Using a 3^rd-party transaction managerOnce a 3^rd-party transaction manager is installed and the application is configured (see below), a distributed transaction occurs whenever a server-side call occurs, and the model defining the call (usually one based on the Tersus/Basic/Server plugin) has a dataSource property whose value is one of the data sources configured to implement the transaction manager.AtomikosInstallationTBCConfigurationDownload

transactions.properties and place it in a directory of your choice, such as tomcat's conf folder (referred to as <Path>).Add the following JVM properties to your configuration:-Dcom.atomikos.icatch.file=<Path>/transactions.properties
-Dcom.atomikos.icatch.tm_unique_name=<TM_UNIQUE_NAME>
Where <TM_UNIQUE_NAME> is a unique identifier of your choice.Add the following to your configuration/context file:<Parameter
name="resource_handler_class_name"
value="tersus.enterprise.EnterpriseResourceHandler" />
<Resource name="UserTransaction" auth="Container"
type="javax.transaction.UserTransaction"
factory="com.atomikos.icatch.jta.UserTransactionFactory" />
To convert an existing data source to one participating in a distributed transaction, perform the following changes:SQL Server

Before:

After:

<Resource name="<Name>"
    type="javax.sql.DataSource"
    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    maxActive="<MaxActive>" maxIdle="<MaxIdle>" maxWait="<MaxWait>"
    username="<Username>"
    password="<Password>"
    url="<URL>"
    validationQuery="<Query>" />

<Resource name="<Name>"
    type="com.atomikos.jdbc.AtomikosDataSourceBean"
    auth="Container"
    factory="com.atomikos.tomcat.BeanFactory"
    uniqueResourceName="<UniqueResourceName>"
    xaDataSourceClassName="com.microsoft.sqlserver.jdbc.SQLServerXADataSource"
    xaProperties.serverName="<ServerName/IPAddress>"
    xaProperties.portNumber="<PortNumber>"
    xaProperties.databaseName="<DatabaseName>"
    xaProperties.user="<Usename>"
    xaProperties.password="<Password>"
    minPoolSize="1"
    maxPoolSize="30"
    testQuery="<Query>" />

Oracle

Before:

After:

<Resource name="<Name>"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.OracleDriver"
    maxActive="<MaxActive>" maxIdle="<MaxIdle>" maxWait="<MaxWait>"
    username="<Username>"
    password="<Password>"
    url="<URL>"
    validationQuery="<Query>" />

<Resource name="<Name>"
    type="com.atomikos.jdbc.AtomikosDataSourceBean"
    auth="Container"
    factory="com.atomikos.tomcat.BeanFactory"
    uniqueResourceName="<UniqueResourceName>"
    xaDataSourceClassName="oracle.jdbc.xa.client.OracleXADataSource"
    xaProperties.URL="<URL>"
    xaProperties.user="<Usename>"
    xaProperties.password="<Password>"
    minPoolSize="1"
    maxPoolSize="30"
    testQuery="<Query>" />

Where:

<UniqueResourceName> is any unique name, but we recommend you use a standard naming convention, such as <ServerName/IPAddress>:<DatabaseName>

<PortNumber> is the tcp-ip port on which the DBMS listens (1433 by default)