This section describes how DataDirect XQuery supports transactions, transaction isolation levels, and distributed transactions.
Transactions
A transaction consists of one or more updating XQueries that have been executed, completed, and then either committed or rolled back. By default, a DataDirect XQuery connection (a new XQConnection object) is in auto-commit mode. Auto-commit causes a commit after each XQuery is evaluated.
To disable auto-commit, specify false as the argument value for setAutoCommit(), which is a method of the XQConnection interface. For example:
...
DDXQDataSource ds = new DDXQDataSource();
ds.setJdbcUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=stocks");
XQConnection conn = ds.getConnection("myuserid", "mypswd");
conn.setAutoCommit(false);
...
When auto-commit is disabled, the application must either commit each transaction explicitly or roll back each transaction explicitly. When a connection is closed, DataDirect XQuery, by default, rolls back the active transaction.
To perform commits and rollbacks, use the commit() and rollback() methods, respectively, of XQConnection. .
Transaction Isolation Levels
DataDirect XQuery supports the isolation levels as defined in the JDBC interface java.sql.Connection, which are as follows:
- java.sql.Connection.TRANSACTION_READ_UNCOMMITTED (Read Uncommitted). Locks are obtained on modifications to the database and held until end of transaction (EOT). Reading from the database does not involve any locking.
- java.sql.Connection.TRANSACTION_READ_COMMITTED (Read Committed). Locks are acquired for reading and modifying the database. Locks are released after reading but locks on modified objects are held until EOT.
- java.sql.Connection.TRANSACTION_REPEATABLE_READ (Repeatable Read). Locks are obtained for reading and modifying the database. Locks on all modified objects are held until EOT. Locks obtained for reading data are held until EOT. Locks on nonmodified access structures (such as indexes and hashing structures) are released after reading.
- java.sql.Connection.TRANSACTION_SERIALIZABLE (Serializable). All data read or modified is locked until EOT. All access structures that are modified are locked until EOT. Access structures used by the query are locked until EOT.
- java.sql.Connection.TRANSACTION_NONE (None). Transactions are not supported.
The database to which you are connecting may not support all of these isolation levels, as detailed in following table.
|
Database
|
Read
Committed
|
Read
Uncommitted
|
Repeatable
Read
|
Serializable
|
None
|
|
DB2
|
X (default)
|
X
|
X
|
X
|
X
|
|
Oracle
|
X (default)
| | |
X
| |
|
SQL Server
|
X (default)
|
X
|
X
|
X
| |
|
Sybase
|
X (default)
|
X
|
X
|
X
| |
The names of the DB2 isolation levels do not map one-to-one to the names of the JDBC isolation levels. The following table maps the JDBC isolation levels to the appropriate DB2 isolation levels.
|
JDBC Isolation Level
|
DB2 Isolation Level
|
|
Read Committed
|
Cursor Stability
|
|
Read UnCommitted
|
Uncommitted Read
|
|
Repeatable Read
|
Read Stability
|
|
Serializable
|
Repeatable Read
|
|
None
|
No Commit *
|
|
* Supported for DB2 for iSeries versions that do not enable journaling.
|
To set an isolation level for a single connection, specify the appropriate value for the JdbcTransactionIsolationLevel property of DDXQDataSource.
To set an isolation level for multiple connections, specify the appropriate value for the TransactionIsolationLevel property of DXQJDBCConnection.
NOTE: Once a connection is made, the transaction isolation level cannot be changed for that connection (XQConnection object).
Distributed Transactions
DataDirect XQuery does not support distributed transactions. However, it is possible to have a single DataDirect XQuery connection (XQConnection object) with multiple underlying JDBC connections and perform updates if the updates target only one of the JDBC data sources. It is, also, possible during the lifetime of an XQConnection object to update two different JDBC data sources if this is done in separate transactions (not in a single transaction).