Transactions

Hybrid Data Pipeline supports transactions against data stores that provide transaction support such as DB2, MySQL, Oracle, and SQL Server. Transactions are supported for JDBC, ODBC, and OData client applications. For JDBC and ODBC applications, transactions are handled via the TransactionMode property and Transaction Mode option, respectively. For OData client applications, Hybrid Data Pipeline supports transactions for OData Version 4 batch requests.

Most ODBC and JDBC drivers that support transactions connect to backend data stores using a socket. However, the Hybrid Data Pipeline drivers connect to data stores through an HTTP(S) connection. Therefore, Hybrid Data Pipeline can only detect the abnormal termination of a transaction from a lack of activity on the session. To detect session inactivity, Hybrid Data Pipeline runs a transaction timeout thread through sessions every 5 seconds to look for idle transaction threads. If a transaction remains idle longer than a specified period, it will be rolled back and canceled. By default, the transaction timeout limit is 60 seconds. An administrator can specify the period a transaction thread can remain idle with the transaction timeout limit. The transaction timeout limit can be set at the system, tenant, user, or data source level, or a combination of these. See Manage Limits view and Limits API for information on setting limits.

In the following example, an administrator sets the transaction timeout limit to 10 seconds at the system level by executing a POST operation with the Limits API. Given the 5 second interval at which the transaction timeout thread runs, no transaction threads may remain idle for more than 15 seconds with this setting.

Sample request

In addition to a transaction timeout, server and session timeouts can also lead to transaction rollback and cancellation. Hybrid Data Pipeline will return the same error for each of these timeouts. When a transaction timeout error is thrown, the connection associated with the error is placed in a special state. The rollback and close methods are allowed for JDBC connections in this state, while only the rollback method is allowed for ODBC connections. However, calls that do not require a round trip to the server may still succeed.

The following isolation levels are supported depending on which isolation levels are supported by the data store.

The following data stores support transactions. The data stores marked with an asterisk(*) include parameters that can be configured on the Hybrid Data Pipeline data source definition.