Understanding the Transactional Behavior of DataDirect XQuery Updates

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 or roll back each transaction explicitly. DataDirect XQuery, by default, rolls back the active transaction when a connection is closed.

To perform commits and rollbacks, use the commit and rollback methods, respectively, of XQConnection. See XQConnection Interface.

Transaction Isolation Levels

DataDirect XQuery supports the following isolation levels as defined in the JDBC interface java.sql.Connection:

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 non-modified 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.

Not all databases support all of these isolation levels, as summarized in following table.

Table 11-1. Isolation Level Support
Database	Read Committed	Read Uncommitted	Repeatable Read	Serializable	None
DB2	X (default)	X	X	X	X
Informix	X (default)	X	X	X
MySQL Enterprise (InnoDB*)	X (default)	X	X	X
Oracle	X (default)			X
PostgreSQL	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 (see Table 6-1).

To set an isolation level for multiple connections, specify the appropriate value for the TransactionIsolationLevel property of DXQJDBCConnection (see Table 6-2).

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, provided this is done in separate transactions and not in a single transaction.