Skip Headers

Oracle® Containers for J2EE Enterprise JavaBeans Developer's Guide 10g Release 3 (10.1.3) B14428-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Transaction Best Practices

This section describes the preferred approach to using transactions in an EJB application, including:

- Using Container Managed Transactions with Datasource Connections

- Using a Rollback Strategy

## Using Container Managed Transactions with Datasource Connections

If you are using container-managed transactions, and you use a data source connection, bear in mind that the connection is not released until the transaction commits. This is particularly important if you are using the data source connection in a loop: in this case, you should acquire and release the connection outside of the loop to avoid inadvertently exhausting your connection pool.

Consider a session bean that you configure for container-managed transactions. This session bean has method `runQueryConnectionEveryTime` as Example 21-1 shows. When this method is called, a container-managed transaction is opened. In each iteration of the `for` loop, a connection is acquired and closed. However, the closed connection is not released until the method returns and the container-managed transaction commits. Depending on the number of iterations, this design can exhaust your connection pool.

To avoid this problem, you should acquire and close the connection outside of the loop as Example 21-2 shows. By doing so, you guarantee that only one connection will be held until the container-managed transaction commits.

Example 21-1 Incorrect: count Number of Connections Held Until Commit

    public static long runQueryConnectionEveryTime (int count)
    {
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");

        for (int i = 0; i < count; i++)
        {
            Connection con = ds.getConnection();     //connection created inside loop

            PreparedStatement ps = con.prepareStatement(
                "select AAA_ID, AAA_A FROM AAA_TABLE where AAA_ID = ? "
            );

            OracleStatement os = (OracleStatement)ps;
            os.defineColumnType(1, Types.BIGINT);
            ps.setLong(1, i);
            ResultSet rs = ps.executeQuery();
            rs.close();
            ps.close();

            con.close(); //connection closed inside loop
        }
    }

Example 21-2 Correct: Only One Connection Held Until Commit

    public static long runQueryConnectionEveryTime (int count)
    {
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");

        Connection con = ds.getConnection();    //connection created outside loop

        for (int i = 0; i < count; i++)
        {
            PreparedStatement ps = con.prepareStatement(
                "select AAA_ID, AAA_A FROM AAA_TABLE where AAA_ID = ? "
            );

            OracleStatement os = (OracleStatement)ps;
            os.defineColumnType(1, Types.BIGINT);
            ps.setLong(1, i);
            ResultSet rs = ps.executeQuery();
            rs.close();
            ps.close();
        }

        con.close(); //connection closed outside loop
    }

## Using a Rollback Strategy

An enterprise bean with container-managed transaction demarcation can use the `setRollbackOnly` method of its `javax.ejb.EJBContext` object to mark the transaction such that the transaction can never commit.

Typically, you would do this to protect data integrity before throwing an application exception when the application exception does not automatically cause the container to rollback the transaction.

For example, an `AccountTransfer` bean which debits one account and credits another account could mark a transaction for rollback if it successfully performs the debit, but fails during the credit operation.

For more information, see:

- "What is EJB Context?"

- "Accessing an EJB 3.0 EJBContext"

- "Accessing an EJB 2.1 EJBContext"