1. Administering JDBC Data Sources for Oracle WebLogic Server
  2. JDBC Data Source Transaction Options

5 JDBC Data Source Transaction Options

When you configure a JDBC data source using the WebLogic Server Administration Console, WebLogic Server automatically selects specific transaction options based on the type of JDBC driver.

WebLogic JDBC data sources supports the following transaction options:

  • XA drivers: For XA drivers, the system automatically selects the Two-Phase Commit protocol for global transaction processing.

  • Non-XA drivers: For non-XA drivers, local transactions are supported by definition. In addition, WebLogic Server offers Global Transactions options too.

    You must select Support Global Transactions option if you want to use connections from the data source in global transactions, even though you have not selected an XA driver. See Enabling Support for Global Transactions with a Non-XA JDBC Driver for more information.

    When you select Supports Global Transactions, you must also select the protocol for WebLogic Server to use for the transaction branch when processing a global transaction:

    • Logging Last Resource: With this option, the transaction branch in which the connection is used is processed as the last resource in the transaction and is processed as a local transaction. Commit records for two-phase commit (2PC) transactions are inserted in a table on the resource itself, and the result determines the success or failure of the prepare phase of the global transaction. This option offers some performance benefits and greater data safety than Emulate Two-Phase Commit, but it has some limitations. See Understanding the Logging Last Resource Transaction Option.

      Note:

      Logging Last Resource is not supported for data sources used by a multi data source except when used with Oracle RAC version 10g Release 2 (10gR2) and greater versions as described in Administrative Considerations and Limitations for LLR Data Sources.

    • Emulate Two-Phase Commit: With this option, the transaction branch in which the connection is used always returns success for the prepare phase of the transaction. It offers performance benefits, but also has risks to data in some failure conditions. Select this option only if your application can tolerate heuristic conditions. See Understanding the Emulate Two-Phase Commit Transaction Option.

    • One-Phase Commit: (selected by default) With this option, a connection from the data source can be the only participant in the global transaction and the transaction is completed using a one-phase commit optimization. If more than one resource participates in the transaction, an exception is thrown when the transaction manager calls XAResource.prepare on the 1PC resource.

Enabling Support for Global Transactions with a Non-XA JDBC Driver

If you use global transactions in your applications, you should use an XA JDBC driver to create database connections in the JDBC data source. If an XA driver is unavailable for your database, or you prefer not to use an XA driver, you should enable support for global transactions in the data source.

You should also enable support for global transaction if your applications meet any of the following criteria:

  • Use the EJB container in WebLogic Server to manage transactions

  • Include multiple database updates within a single transaction

  • Access multiple resources, such as a database and the Java Messaging Service (JMS), during a transaction

  • Use the same data source on multiple servers (clustered or non-clustered)

With an EJB architecture, it is common for multiple EJBs that are doing database work to be invoked as part of a single transaction. Without XA, the only way for this to work is if all transaction participants use the exact same database connection. When you enable global transactions and select either Logging Last Resource or Emulate Two-Phase Commit, WebLogic Server internally uses the JTS driver to make sure all EJBs use the same database connection within the same transaction context without requiring you to explicitly pass the connection from EJB to EJB.

If multiple EJBs are participating in a transaction and you do not use an XA JDBC driver for database connections, configure a Data Source with the following options:

  • Supports Global Transactions selected

  • Logging Last Resource or Emulate Two-Phase Commit selected

This configuration will force the JTS driver to internally use the same database connection for all database work within the same transaction.

With XA (requires an XA driver), EJBs can use a different database connection for each part of the transaction. WebLogic Server coordinates the transaction using the two-phase commit protocol, which guarantees that all or none of the transaction will be completed.

Parent topic: JDBC Data Source Transaction Options

Understanding the Logging Last Resource Transaction Option

WebLogic Server supports the Logging Last Resource (LLR) transaction optimization with JDBC data sources. LLR is a performance enhancement option that enables one non-XA resource to participate in a global transaction with the same ACID guarantee as XA. LLR is a refinement of the "Last Agent Optimization”. It differs from Last Agent Optimization in that it is transactionally safe.

The LLR resource uses a local transaction for its transaction work. The WebLogic Server transaction manager prepares all other resources in the transaction and then determines the commit decision for the global transaction based on the outcome of the LLR resource's local transaction.

The LLR optimization improves performance by:

  • Removing the need for an XA JDBC driver to connect to the database. XA JDBC drivers are typically inefficient compared to non-XA JDBC drivers.

  • Reducing the number of processing steps to complete the transaction, which also reduces network traffic and the number of disk I/Os.

  • Removing the need for XA processing at the database level

When a connection from a data source configured for LLR participates in a two-phase commit (2PC) global transaction, the WebLogic Server transaction manager completes the transaction by:

  • Calling prepare on all other (XA-compliant) transaction participants.

  • Inserting a commit record to a table on the LLR participant (rather than to the file-based transaction log).

  • Committing the LLR participant's local transaction (which includes both the transaction commit record insert and the application's SQL work).

  • Calling commit on all other transaction participants.

For a one-phase commit (1PC) global transaction, LLR eliminates the XA overhead by using a local transaction to complete the database operations, but no 2PC transaction record is written to the database.

The Logging Last Resource optimization maintains data integrity by writing the commit record on the LLR participant. If the transaction fails during the local transaction commit, the WebLogic Server transaction manager rolls back the transaction on all other transaction participants. For failure recovery, the WebLogic Server transaction manager reads the transaction log on the LLR resource along with other transaction log files in the default store and completes any transaction processing as necessary. Work associated with XA participants is committed if a commit record exists, otherwise their work is rolled back.

For instructions on how to create an LLR-enabled JDBC data source, see Create LLR-enabled JDBC data sources in the Oracle WebLogic Server Administration Console Online Help. For more details about the Logging Last Resource transaction processing, see Logging Last Resource Transaction Optimization in Developing JTA Applications for Oracle WebLogic Server.

Parent topic: JDBC Data Source Transaction Options

Advantages to Using the Logging Last Resource Optimization

Depending on your environment, you may want to consider the LLR transaction protocol in place of the two-phase commit protocol for transaction processing because of its performance benefits. The LLR transaction protocol offers the following advantages:

  • Allows non-XA JDBC drivers and even non-XA–capable databases to safely participate in two-phase commit transactions.

  • Eliminates the database's use of the XA protocol.

  • Performs better than JDBC XA connections.

  • Reduces the length of time that database row locks are held.

  • Always commits database work prior to other XA work. In XA transactions, these operations are committed in parallel, so, for example, when a JMS send participates in the transaction, the JMS message may be delivered before database work commits. With LLR, the database work in the local transaction is completed before all other transaction work.

  • Has no increased risk of heuristic hazards, unlike the Emulate Two-Phase Commit option for a JDBC data source.

    Note:

    The LLR optimization provides a significant increase in performance for insert, update, and delete operations. However, for read operations with LLR, performance is somewhat slower than read operations with XA.

    For more information about performance tuning with LLR, see Optimizing Performance with LLR in Developing JTA Applications for Oracle WebLogic Server.

Parent topic: Understanding the Logging Last Resource Transaction Option

Enabling the Logging Last Resource Transaction Optimization

To enable the LLR transaction optimization, you create a JDBC data source with the Logging Last Resource transaction protocol, then use database connections from the data source in your applications. WebLogic Server automatically creates the required table on the database.

See Create LLR-enabled JDBC data sources in the Oracle WebLogic Server Administration Console Online Help.

Parent topic: Understanding the Logging Last Resource Transaction Option

Programming Considerations and Limitations for LLR Data Sources

You use JDBC connections from an LLR-enabled data source in an application as you would use JDBC connections from any other data source: after beginning a transaction, you look up the data source on the JNDI tree, then request a connection from the data source. However, with the LLR optimization, WebLogic Server internally manages the connection request and handles the transaction processing differently than in an XA transaction. For more information about how Logging Last Resource works, see Logging Last Resource Transaction Optimization in Developing JTA Applications for Oracle WebLogic Server.

Note the following:

  • When programming with an LLR data source, you must start the global transaction before calling getConnection on the LLR data source. If you call getConnection before starting the global transaction, the connection will be independent, and will not be associated with any subsequently started global transaction. The connection will operate in the autoCommit(true) mode. In this mode, every update will commit automatically on its own, and there will be no way to roll back any update unless application code has explicitly set the autoCommit state to false and is explicitly managing its own local transaction.

  • Only one internal JDBC LLR connection is reserved per transaction. And that connection is used throughout the transaction processing.

  • The reserved connection is always hosted on the transaction's coordinator server. Make sure that the data source is targeted to the coordinating server or to the cluster. Also see Optimizing Performance with LLR" in Developing JTA Applications for Oracle WebLogic Server.

  • For additional JDBC connection requests within the transaction from a same-named data source, operations are routed to the reserved connection from the original connection request, even if the subsequent connection request is made on a different instance of the data source (i.e., a data source deployed on a different server than the original data source that supplied the connection for the first request). Note the following:

    • Routed LLR connections may be less capable and less performant than locally hosted XA connections. See Possible Performance Loss with Non-XA Resources in Multi-Server Configurations.

    • Connection request routing limits the number of concurrent transactions. The maximum number of concurrent LLR transactions is equal to the configured size (MaxCapacity) of the coordinator's JDBC LLR data source.

    • Routed connections have less capability than local connections, and may fail as a result. Specifically, non-serializable "custom" data types within a query ResultSet may fail.

  • Only instances of a single LLR data source may participate in a particular transaction. A single LLR data source may have instances on multiple WebLogic servers, and two data sources are considered to be the same if they have the same configured name. If more than one LLR data source instance is detected and they are not instances of the same data source, the transaction manager will roll back the transaction.

  • Resource adapters (connectors) that implement the weblogic.transaction.nonxa.NonXAResource interface cannot participate in global transaction in which an LLR resource also participates because both must be the last resource in the transaction. If both resource types participate in the same transaction, the transaction commit() method throws a javax.transaction.RollbackException when this conflict is detected.

  • Because the LLR connection uses a separate local transaction for database processing, any changes made (and locks held) to the same database using an XA connection are not visible during the LLR processing even though all of the processing occurs in the same global transaction. In some cases, this can cause deadlocks in the database. You should not combine XA and LLR processing in the same database in a single global transaction.

  • Connections from an LLR data source cannot participate in transactions coordinated by foreign transaction managers, such as a transaction started by a remote object request broker or by Tuxedo.

  • Global transactions cannot span to another legacy domain that includes a data source with the same name as an LLR data source.

  • For JDBC LLR 2PC transactions, if the transaction data is too large to fit in the LLR table, the transaction will fail with a rollback exception thrown during commit. This can occur if your application adds many transaction properties during transaction processing. Your database administrator can manually create a table with larger columns if this occurs. See Oracle WebLogic Extensions to JTA in Developing JTA Applications for Oracle WebLogic Server.

Parent topic: Understanding the Logging Last Resource Transaction Option

Administrative Considerations and Limitations for LLR Data Sources

Consider the following requirements and limitations when configuring an LLR-enabled JDBC data source. For more information about how Logging Last Resource works, see Logging Last Resource Transaction Optimization in Developing JTA Applications for Oracle WebLogic Server.

  • There is one LLR table per server:

    • Multiple LLR data sources may share a table.

    • WebLogic Server automatically creates the table if it is not found.

    • Default name is WL_LLR_SERVERNAME. You can configure the table name in the WebLogic Server Administration Console on the Server > Configuration > General tab under Advanced options. See Servers: Configuration: General in Oracle WebLogic Server Administration Console Online Help.

  • A server will not boot if the database is down or the LLR table is unreachable during boot.

  • Multiple servers must not share the same LLR table. Boot checks to ensure domain and server name match the domain and server name stored in the table when the table is created. If WebLogic Server detects that more than one server is sharing the same LLR table, WebLogic Server will shut down one or more of the servers.

  • LLR supports server migration and transaction recovery service migration. To use the transaction recovery service migration, ensure that each LLR resource be targeted to either the cluster or the set of candidate servers in the cluster. See Recovering Transactions For a Failed Clustered Server in Developing JTA Applications for Oracle WebLogic Server.

  • The LLR transaction option is not permitted for use in JDBC application modules.

  • When using Multi Data Sources, the LLR transaction option can only be used with Oracle RAC version 10g Release 2 (10gR2) and greater versions with the following settings:

    • All WebLogic application database JDBC interactions must use the READ_COMMITTED transaction isolation level (the default).

    • The Oracle RAC setting MAX_COMMIT_PROPAGATION_DELAY must be set to a value of 0 (zero, the default).

    The use of LLR with Multi Data Sources is supported only with Oracle RAC. All (or none) of the members of the Multi Data Source must be LLR data sources.

    • When using Oracle RAC, at least one of the members of the Multi Data Source must be available for recovery processing when the server is booted or the server fails to boot.

    • When not using Oracle RAC, all of the members of the Multi Data Source must be available for recovery processing when the server is booted or the server fails to boot.

  • If you use credential mapping or identity pooling on an LLR data source, all mapped users must have write permissions on the LLR table.

  • You cannot use a JDBC XA driver to create database connections in a JDBC LLR data source. If the JDBC driver used in a JDBC LLR data source supports XA, a warning message is logged, and the data source participates in transactions as a full XA resource rather than as an LLR resource.

  • Transaction statistics for LLR resources are tracked under NonXAResource. See View transaction statistics for non-XA resources in the Oracle WebLogic Server Administration Console Online Help.

  • When using LLR with a Sybase DBMS, Sybase's JDBC driver requires that certain JDBC stored procedures be installed in the DBMS in order to implement some standard JDBC metadata methods. See the Sybase jConnect documentation for details.

Parent topic: Understanding the Logging Last Resource Transaction Option

Understanding the Emulate Two-Phase Commit Transaction Option

If you need to support distributed transactions with a JDBC data source, but there is no available XA-compliant driver for your DBMS, you can select the Emulate Two-Phase Commit for non-XA Driver option for a data source to emulate two-phase commit for the transactions in which connections from the data source participate.

This option is an advanced option on the Configuration > General tab of a data source configuration.

When the Emulate Two-Phase Commit for non-XA Driver option is selected (EnableTwoPhaseCommit is set to true), the non-XA JDBC resource always returns XA_OK during the XAResource.prepare() method call. The resource attempts to commit or roll back its local transaction in response to subsequent XAResource.commit() or XAResource.rollback() calls. If the resource commit or rollback fails, a heuristic error results. Application data may be left in an inconsistent state as a result of a heuristic failure.

When the Emulate Two-Phase Commit for non-XA Driver option is not selected in the Console (EnableTwoPhaseCommit is set to false), the non-XA JDBC resource causes XAResource.prepare() to fail. When there is only one resource participating in a transaction, the one phase optimization bypasses XAResource.prepare(), and the transaction commits successfully in most instances.

Note:

There are risks to data integrity when using the Emulate Two-Phase Commit for non-XA Driver option. Oracle recommends that you use an XA-compliant JDBC driver or the Logging Last Resource option rather than use the Emulate Two-Phase Commit option. Make sure you consider the risks below before enabling this option.

This non-XA JDBC driver support is often referred to as the "JTS driver" because WebLogic Server uses the WebLogic JTS Driver internally to support the feature. For more information about the WebLogic JTS Driver, see Using the WebLogic JTS Driver in Developing JDBC Applications for Oracle WebLogic Server.

Parent topic: JDBC Data Source Transaction Options

Limitations and Risks When Emulating Two-Phase Commit Using a Non-XA Driver

WebLogic Server supports the participation of non-XA JDBC resources in global transactions with the Emulate Two-Phase Commit data source transaction option, but there are limitations that you must consider when designing applications to use such resources. Because a non-XA driver does not adhere to the XA/2PC contracts and only supports one-phase commit and rollback operations, WebLogic Server (through the JTS driver) has to make compromises to allow the resource to participate in a transaction controlled by the Transaction Manager.

Consider the following limitations and risks before using the Emulate Two-Phase Commit for non-XA Driver option.

Parent topic: Understanding the Emulate Two-Phase Commit Transaction Option

Heuristic Completions and Data Inconsistency

When Emulate Two-Phase Commit is selected for a non-XA resource, (enableTwoPhaseCommit = true), the prepare phase of the transaction for the non-XA resource always succeeds. Therefore, the non-XA resource does not truly participate in the two-phase commit (2PC) protocol and is susceptible to failures. If a failure occurs in the non-XA resource after the prepare phase, the non-XA resource is likely to roll back the transaction while XA transaction participants will commit the transaction, resulting in a heuristic completion and data inconsistencies.

Because of the data integrity risks, the Emulate Two-Phase Commit option should only be used in applications that can tolerate heuristic conditions.

Cannot Recover Pending Transactions

Because a non-XA driver manipulates local database transactions only, there is no concept of a transaction pending state in the database with regard to an external transaction manager. When XAResource.recover() is called on the non-XA resource, it always returns an empty set of Xids (transaction IDs), even though there may be transactions that need to be committed or rolled back. Therefore, applications that use a non-XA resource in a global transaction cannot recover from a system failure and maintain data integrity.

Possible Performance Loss with Non-XA Resources in Multi-Server Configurations

Because WebLogic Server relies on the database local transaction associated with a particular JDBC connection to support non-XA resource participation in a global transaction, when the same JDBC data source is accessed by an application with a global transaction context on multiple WebLogic Server instances, the JTS driver will always route to the first connection established by the application in the transaction.

For example, if an application starts a transaction on one server, accesses a non-XA JDBC resource, then makes a remote method invocation (RMI) call to another server and accesses a data source that uses the same underlying JDBC driver, the JTS driver recognizes that the resource has a connection associated with the transaction on another server and sets up an RMI redirection to the actual connection on the first server. All operations on the connection are made on the one connection that was established on the first server. This behavior can result in a performance loss due to the overhead associated with setting up these remote connections and making the RMI calls to the one physical connection.

Multiple Non-XA Participants

If you use more than one non-XA resource in a global transaction, it is possible to see JTA SystemExceptions in the event of a non-atomic outcome. The chance for non-atomic outcomes and SystemExceptions tends to increase with the number of two-phase-emulated data source participants.

Note:

The use of a two-phase-emulated data source in a JTA transaction across domains of different versions is not supported.

Local Transaction Completion when Closing a Connection

For a non-XA connection, the setAutoCommit(true) method is called if the connection is currently in auto-commit false state when a connection is closed. Per the Java EE JDBC specification, this method automatically commits any outstanding local transaction.

There are some drivers (Oracle 10.x and 11.x driver) that do not commit the local transaction. If the application does not complete (commit or rollback) the local transaction before closing the connection, a connection is returned to the pool with outstanding work and that work may never be completed or it may be committed or rolled back by the next reservation of that connection. To prevent that situation from happening, a WebLogic data source calls commit on the connection when returning it to the pool, if running with the Oracle 10.x or 11.x driver. If an explicit commit is desired on close, then set the system property weblogic.datasource.endLocalTxOnNonXaConWithCommit=true .

Some users may want an abandoned local transaction to rollback instead of commit on close. Setting the following properties will cause local transactions to be rolled back instead of committed if abandoned:

-Dweblogic.datasource.endLocalTxOnNonXaConWithCommit=false
-Dweblogic.datasource.endLocalTxOnNonXaConWithRollback=true

Note:

It is not a good programming practice to leave abandoned transactions. It is recommended that applications explicitly commit or rollback local transactions.

For an XA connection, WebLogic data sources have always rolled back any local transaction when closing the connection. The transaction can be committed instead of rolled back by setting the system property weblogic.datasource.endLocalTxOnXaConWithCommit=true.

For an XA connection, WebLogic data sources have always rolled back any local transaction when closing the connection. The transaction can be committed instead of rolled back by setting the system property weblogic.datasource.endLocalTxOnXaConWithCommit=true.

Parent topic: JDBC Data Source Transaction Options