1. Introduction

Transactions, especially those featured in business systems, are an important part of modern computing. Transactional systems are an obvious part of banking applications, e-commerce systems, etc. But even in simpler internet systems such as content management systems and discussion forums, it can be advantageous to use a transactional approach, because transactions allow us to keep our data consistent. There is nothing worse than trying to fix inconsistent data, especially since there is no guarantee of success! In other words, by writing systems transactionally, we guarantee ourselves a certain level of peace.

Before we move on to the specifics of transactional systems in OneWebSQL™, let's review a couple of the most important terms pertaining to transactional systems.

Transaction - a set of operations on data, treated as a single entity, and possessing the following properties:

The properties listed above are abbreviated as ACID. The transaction itself is also frequently called a unit of work (UOW).

Transactional system - a system in which all operations on data are grouped in transactions.

Transactional resource - a system (or subsystem) which allows operating on data in a transactional way (e.g., a database).

Transaction commit – an operation in which changes that have been made to data since the start of the transaction are made permanent.

Transaction rollback - operations that revert all changes that have been made to data since the start of the transaction.

Transaction scope - an area in which a program has been operating from the moment the transaction started until it was committed or rolled back.

Transaction management differs depending on the type of the application we're writing and our environment. However, in each case OneWebSQL™ provides tools that make transaction management easy. Below we'll discuss three cases of transaction management:

2. Transactions in stand-alone applications

The JDBC API provides transaction management functionality through the class java.sql.Connection with the methods setAutoCommit(boolean), commit(), and rollback(). OneWebSQL™ components operate on a slightly higher level of database access, through the javax.sql.DataSource interface. Because of this, programmers do not directly use the methods from the java.sql.Connection class. Instead, you can use the class com.onewebsql.util.transaction.TransactionalDataSource, which expands the standard javax.sql.DataSource interface to include transaction management functionality. This class is in fact a simple transaction manager. Moreover, OneWebSQL™ includes the utility class com.onewebsql.util.transaction.TransactionUtil and the interface com.onewebsql.util.transaction.TransactionOp to provide wrappers for transactional operations and exception handling.

The relationships between the JDBC interfaces, OneWebSQL™ transaction utitilies, and your application code are shown in the diagram below.

The following sections show how to use the class TransactionalDataSource and some of its helper classes to manage transactions.

2.1. Basic example

We'll start our description of the transactional management methods with a simple example. In this example the objective is, in one transaction, to add a book and its author to a database. We will assume that we have classes generated for a data model as shown in the diagram below.

Here's a sample program that carries out this transaction:

// create and configure your data source (e.g. PostgreSQL data source)
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("transactiondemo");
ds.setUser("transactiondemo");
ds.setPassword("transactiondemo");

// create our transactional data source wrapper
TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);

// create DAOs
BookDAO bookDAO = new BookDAOImpl(transactionalDs, new PostgresDBAdapter());
AuthorDAO authorDAO = new AuthorDAOImpl(transactionalDs, new PostgresDBAdapter());

// start transaction
transactionalDs.beginTransaction();

// create author and book
Author author = new Author();
author.setName("John Smith");
authorDAO.insert(author);

Book book = new Book();
book.setTitle("Mr. Foo and Mr. Bar");
book.setAuthorId(author.getId());
bookDAO.insert(book);

// commit transaction
transactionalDs.commitTransaction();

These steps describe a basic transaction management scenario with TransactionalDataSource:

  1. Create and initialize an object of the type DataSource that provides access to our database. We will need a concrete DataSource class for each database, or a JDBC driver (a PostgreSQL example: org.postgresql.ds.PGSimpleDataSource).
  2. Create an object of the type TransactionalDataSource and initialize it with the previously created data source.
  3. Create DAOs (data access objects) and initialize them with the previously created TransactionalDataSource object.
  4. Start the transaction by calling the method beginTransaction().
  5. Carry out database operations for the transaction.
  6. Commit or roll back the transaction using the methods commitTransaction() or rollbackTransaction(), respectively.

As we can see, managing transactions with TransactionalDataSource is not conceptually different from the approach used with JDBC or JTA (Java Transaction) APIs.

Exceptions during the operations commitTransaction() and rollbackTransaction()
While a transaction is being committed or rolled back, two sorts of exception may occur:

  • A RuntimeTransactionException can be thrown if the transaction has not started or is marked for rollback. The exception occurs during the transaction commitment or on closing the underlying database connection.
  • A RuntimeDoubleException is thrown if an exception occurs during the transaction commitment and then again on closing the underlying database connection.

2.2. Handling transactions with TransactionUtil

In real applications transaction handling is slightly more complicated than what was shown in the basic example. To start with, we have to handle possible exceptions thrown by database operations while a transaction is in progress. Additionally, we have to handle exceptions that can occur while a transaction is being committed or rolled back. You may also want to mark a transaction for rollback without breaking its execution. In such a case, the transaction should be always rolled back when it finishes, and any attempt to commit it should fail.

Normally, managing a transaction that correctly handles the above cases requires a lot of attention and a significant amount of code. To make things easier, we have prepared a special utility class, com.onewebsql.util.transaction.TransactionUtil, that takes care of all these scenarios. Here's an example using the TransactionUtil class to handle a transaction just like the one from the basic example earlier:

// create and configure your data source (e.g. PostgreSQL data source)
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("transactiondemo");
ds.setUser("transactiondemo");
ds.setPassword("transactiondemo");

// create transactional data source wrapper and transaction util 
TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);
TransactionUtil transactionUtil = new TransactionUtil(transactionalDs);

// run your operations within transaction
transactionUtil.withTransaction(new TransactionOp<Object>() {
    public Object invoke(TransactionalDataSource transactionalDataSource) {
        // create DAOs
        BookDAO bookDAO = new BookDAOImpl(transactionalDataSource, new PostgresDBAdapter());
        AuthorDAO authorDAO = new AuthorDAOImpl(transactionalDataSource, new PostgresDBAdapter());

        // create author and book
        Author author = new Author();
        author.setName("John Smith");
        authorDAO.insert(author);

        Book book = new Book();
        book.setTitle("Mr. Foo and Mr. Bar");
        book.setAuthorId(author.getId());
        bookDAO.insert(book);

        return null;
    }
});

As you can see, wrapping your transaction components with TransactionUtil is fairly simple and consists of the following steps:

  1. Create an object of the type TransactionUtil and initialize it with a previously created transactional data source (TransactionalDataSource).
  2. Perform operations on the database with the interface TransactionOp<T>. Usually the most convenient way of doing this is with an anonymous class.
  3. Pass your own implementation of the TransactionOp<T> interface as an argument of the method call withTransaction(TransactionOp<T>) from the previously created object TransactionUtil.

When you implement your own database operation in the form of the class TransactionOp<T>, it is additionally possible to return its result. When implementing the TransactionOp<T> interface, you have to define a type for the return value (T), and the value itself may be returned in the implementation of the method <T> invoke(TransactionalDataSource). If you don't want to return anything (as in the example above), you can set the type of the return value to java.lang.Object and return null in the method Object invoke(TransactionalDataSource).

Exceptions while using the method TransactionUtil#withTransaction(TransactionalDataSource)
When the transaction is committed or rolled back, the following exceptions may occur:

  • A RuntimeException is thrown if the operation throws an exception during its execution.
  • A RuntimeTransactionException is thrown if the transaction has not started or is marked for rollback, and an exception occurs during the transaction commitment or on closing the underlying database connection.
  • A RuntimeDoubleException is thrown if an exception occurs during the transaction commitment and again on closing the underlying database connection.

2.3. Independent transaction handling

Instead of using TransactionUtil, you may choose to independently handle all the corner cases for the transactional behavior of your application. If you decide to do this, please keep in mind the following requirements:

An example of independent transaction handling is shown below, although we recommend using the class TransactionUtil (previous section) to make things easier.

// create and configure your data source (e.g., a PostgreSQL data source)
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("transactiondemo");
ds.setUser("transactiondemo");
ds.setPassword("transactiondemo");

// create our transactional data source wrapper
TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);

// create DAOs
BookDAO bookDAO = new BookDAOImpl(transactionalDs, new PostgresDBAdapter());
AuthorDAO authorDAO = new AuthorDAOImpl(transactionalDs, new PostgresDBAdapter());

// start transaction
transactionalDs.beginTransaction();

RuntimeException exception = null;
boolean noExceptionThrown = false;
try {
    try {
        // do your database operation (e.g. create author and book)
        Author author = new Author();
        author.setName("John Smith");
        authorDAO.insert(author);

        Book book = new Book();
        book.setTitle("Mr. Foo and Mr. Bar");
        book.setAuthorId(author.getId());
        bookDAO.insert(book);
                
        // ...
                
        noExceptionThrown = true;
    } catch (RuntimeException e) {
        exception = e;
    } catch (Exception e) {
        exception = new RuntimeException(e);
    }
} finally {
    RuntimeException cleanupException = null;
            
    if (noExceptionThrown && (transactionalDs.getTransactionStatus() != TransactionStatus.MARKED_FOR_ROLLBACK)) {
        try {
            // commit transaction if there is no exception and transaction is not marked for rollback
            transactionalDs.commitTransaction();
        } catch (RuntimeTransactionException e) {
            cleanupException = e;
        } 
    } else {
        try {
            // or do the rollback
            transactionalDs.rollbackTransaction();
        } catch (RuntimeTransactionException e) {
            cleanupException = e;
        }
    }
            
    if (cleanupException != null) {
        if (exception != null) {
            exception = new RuntimeDoubleException(exception, cleanupException);
        } else {
            exception = cleanupException;
        }
    }
}
        
if (exception != null) {
    throw exception;
}

2.4. Transaction statuses

Operations carried out on an object of the type TransactionalDataSource cause a change in the transaction's status. Acceptable transaction status values are defined in the enumeration TransactionalDataSource#TransactionStatus. Possible transaction status changes are shown in the diagram below.

At any time, we can query a TransactionalDataSource object about the current transaction status:

TransactionalDataSource transactionalDs = new TransactionalDataSource(dataSource);
...

TransactionStatus status = transactionalDs.getTransactionStatus();

2.5. Managing transaction isolation levels

For implementation reasons, we moved the management of transaction isolation levels from the level of the object Connection to the level of the object TransactionalDataSource.

You should set the desired isolation level before starting a transaction, using the method setTransactionIsolation(int):

TransactionalDataSource transactionalDs = new TransactionalDataSource(dataSource);

transactionalDs.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

transactionalDs.beginTransaction();
...
transactionalDs.commitTransaction();

You can get the current transaction isolation level using the method getTransactionIsolation():

TransactionalDataSource transactionalDs = new TransactionalDataSource(dataSource);

transactionalDs.getTransactionIsolation();

Default transaction isolation level
When an object of the type TransactionalDataSource is created, a default transaction isolation level of TRANSACTION_READ_COMMITTED is set.

Do not change the isolation level while a transaction is running
Setting the isolation level while a transaction is being executed has an undetermined result. It is dependent on the implementation of the database engine and the JDBC driver. Even if it does work correctly in some databases, the solution is not portable and may cause unpleasant suprises.

Not every database supports all transaction isolation levels
Not all databases support all of the four transaction isolation levels. Setting an isolation level that is not supported by the database in question usually results in setting a higher level. This behavior is fully compliant with the JDBC specification.

2.6. Using DAOs and direct JDBC operations in a single transaction

Using an object of the type TransactionalDataSource or TransactionUtil, we can use two types of operations in one transaction - operations with generated DAOs and operations that use the JDBC API directly. An example of such a transaction is shown below.

// create and configure your data source (e.g., a PostgreSQL data source)
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("transactiondemo");
ds.setUser("transactiondemo");
ds.setPassword("transactiondemo");

// create transactional data source wrapper and transaction util 
TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);
TransactionUtil transactionUtil = new TransactionUtil(transactionalDs);

// run your operation within the transaction
transactionUtil.withTransaction(new TransactionOp<Object>() {
    public Object invoke(TransactionalDataSource transactionalDataSource) {
        // create DAOs
        BookDAO bookDAO = new BookDAOImpl(transactionalDataSource, new PostgresDBAdapter());
        AuthorDAO authorDAO = new AuthorDAOImpl(transactionalDataSource, new PostgresDBAdapter());
        
        // create author and book
        Author author = new Author();
        author.setName("John Smith");
        authorDAO.insert(author);
        
        Book book = new Book();
        book.setTitle("Mr. Foo and Mr. Bar");
        book.setAuthorId(author.getId());
        bookDAO.insert(book);
        
        // update book using JDBC API
        try {
            Connection conn = transactionalDataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement("UPDATE book SET title = ?  WHERE id = ?");
            
            ps.setString(1, "New title");
            ps.setInt(2, book.getId());
            ps.execute();
            
            ps.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeSQLException(e);
        }
        
        return null;
    }
});

2.7. Other aspects of programming with TransactionalDataSource

2.7.1. TransactionalDataSource and multithreading

Objects of the class TransactionalDataSource are not thread safe. If you want to access a database using multiple threads at the same time, then each thread should create its own data source object and operate on it.

2.7.2. Limitations of the Connection object

An attempt to carry out one of the operations listed below on a Connection object received from a TransactionDataSource will cause an exception to be thrown:

This is caused by the fact that parts of these operations had to be moved to the data source level, so the other parts didn't make sense anymore (for example, methods related to savepoints).

2.7.3. What happens if we do not end a transaction?

If we do not explicitly end a transaction by calling either commitTransaction() or rollbackTransaction(), then we leave the fate of that transaction to the database engine. Once our program stops running, the connection to the database gets broken. Most database engines handle this by rolling back transactions that were part of the previous connection.

Above all, such unclosed transactions cause performance problems with concurrently accessed database resources. Unclosed transactions usually maintain locks on at least some of the resources on which they were operating. This problem is particularly visible in applications that have a long running time (hours, days).

3. Transactions in JEE web applications without the Java Transaction API (JTA)

When creating applications with OneWebSQL™ that will eventually be deployed in an environment that does not support managing transactions with JTA (for example, Tomcat), we can still manage transactions just like we did in the case of a stand-alone application - we use the classes TransactionalDataSource and TransactionUtil. The only difference will be in accessing the data source object. For web applications, the data source is accessed using JNDI.

In this situation a general transaction scenario looks like this:

  1. Configure a physical data source in the application server, which will represent our database. The configuration varies slightly by server - you'll have to check the documentation of the concrete server.
  2. Define a reference to the logical data source (element resource-ref) in the descriptor file of your web application (web.xml). The application will be able to find the data source in the JNDI tree under the name defined by this reference.
  3. Retrieve the database object from the JNDI tree in your application code.
  4. Carry out operations on the database (as part of a transaction) using the objects TransactionalDataSource and TransactionUtil.

Below we'll show you the steps that have to be taken to set up a data source and manage a transaction in a Tomcat 6.x server. We'll assume that you're using a PostgreSQL database. For a different database type, you'll have to check your server's documentation.

We place the JDBC database driver in the directory $CATALINA_HOME/lib.

In the context file specific to our application (context.xml) we'll define the data source:

<?xml version="1.0" encoding="UTF-8"?>
<Context antiResourceLocking="false" privileged="true" useHttpOnly="true">
   <Resource name="jdbc/transactionDemoDS" auth="Container" type="javax.sql.DataSource"
             driverClassName="org.postgresql.Driver"
             url="jdbc:postgresql://127.0.0.1:5432/transactiondemo"
             username="transactiondemo"
             password="transactiondemo"
             maxActive="20" maxIdle="10"
             maxWait="-1"/>
</Context>

We add the reference to the defined data source in the application descriptor file (web.xml).

<resource-ref>
    <description>PostgreSQL Datasource example</description>
    <res-ref-name>jdbc/transactionDemoDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Now, in our program code (e.g. in a servlet), we retrieve the data source from JNDI in order to pass it to an object of the type TransactionDataSource and then to TransactionUtil, as illustrated in the following example:

// obtain data source from JNDI
InitialContext cxt = new InitialContext();
DataSource ds = (DataSource)cxt.lookup("java:/comp/env/jdbc/transactionDemoDS");

// create transactional data source wrapper and transaction util 
TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);
TransactionUtil transactionUtil = new TransactionUtil(transactionalDs);

// run your operations within transaction
transactionUtil.withTransaction(new TransactionOp<Object>() {
    public Object invoke(TransactionalDataSource transactionalDataSource) {
        // create DAOs
        BookDAO bookDAO = new BookDAOImpl(transactionalDataSource, new PostgresDBAdapter());
        AuthorDAO authorDAO = new AuthorDAOImpl(transactionalDataSource, new PostgresDBAdapter());
        
        // create author and book
        Author author = new Author();
        author.setName("John Smith");
        authorDAO.insert(author);
        
        Book book = new Book();
        book.setTitle("Mr. Foo and Mr. Bar from servlet");
        book.setAuthorId(author.getId());
        bookDAO.insert(book);
        
        return null;
    }
});

As you can see, this is quite similar to the example for the stand-alone application. The only difference is in retrieving the data source from JNDI instead of creating it directly in your code.

4. Transactions in JEE web applications with the Java Transaction API (JTA)

When creating applications with OneWebSQL™ that will eventually be deployed in an environment that supports transaction management with the Java Transaction API (JTA), we should use JTA. JTA provides the object UserTransaction which we can use to begin, commit, or roll back transactions. This object is available in the JNDI tree of your application server.

4.1. Handling transactions with UserTransaction

Below we show a basic example of how to access a database using OneWebSQL™ in a single transaction managed with JTA mechanisms.

// obtain user transaction from JNDI
InitialContext ctx = new InitialContext();
UserTransaction ut = (UserTransaction)ctx.lookup("java:comp/UserTransaction");

// start transaction
ut.begin();

// obtain datasource from JNDI
DataSource ds = (DataSource)ctx.lookup("java:/comp/env/jdbc/transactionDemoDS");

// create DAOs
BookDAO bookDAO = new BookDAOImpl(ds, new PostgresDBAdapter());
AuthorDAO authorDAO = new AuthorDAOImpl(ds, new PostgresDBAdapter());

// create author and book
Author author = new Author();
author.setName("John Smith");
authorDAO.insert(author);

Book book = new Book();
book.setTitle("Mr. Foo and Mr. Bar");
book.setAuthorId(author.getId());
bookDAO.insert(book);
        
// commit transaction
ut.commit();

A basic scenario for transaction management with JTA consists of the following steps:

  1. Retrieve an object of the type UserTransaction from JNDI, which we will use to manage the transaction scope.
  2. Start a transaction by calling the method begin() of the object UserTransaction.
  3. Get a data source from JNDI.
  4. Create DAOs (data access objects) and initialize them with the previously obtained data source.
  5. Carry out operations on the database for this transaction.
  6. Commit or roll back the transaction using the methods commit() or rollback(), respectively, called on the object UserTransaction.

4.2. Handling transactions with JtaUtil

Transaction handling in real life applications is slightly more complex than what was shown in the previous example. Above all, we'll need to handle exceptions which may be thrown by database operations while a transaction is running. Additionally, we have to handle exceptions that may occur while committing or rolling back our transaction. You may also want to mark a transaction for rollback without breaking its execution. In such a case, the transaction should always be rolled back when it finishes, and any attempt to commit it should fail.

Managing a transaction that correctly handles these cases requires attention and a significant amount of code. To make things easier, we have prepared a special utility class, com.onewebsql.util.transaction.JtaUtil, that takes care of all these cases. Here's an example of how to use the JtaUtil class to handle a transaction like the one from the previous example:

// create JTA util (this also gets UserTransacation from JNDI)
JtaUtil jtaUtil = new JtaUtil();

// run your operation within JTA transaction
jtaUtil.withUserTransaction(new JtaOp<Object>() {
    @Override
    public Object invoke(UserTransaction ut) {
    	// obtain datasource from JNDI
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup("java:/comp/env/jdbc/transactionDemoDS");
            
        // create DAOs
		BookDAO bookDAO = new BookDAOImpl(ds, new PostgresDBAdapter());
		AuthorDAO authorDAO = new AuthorDAOImpl(ds, new PostgresDBAdapter());
            
        // create author and book
        Author author = new Author();
        author.setName("John Smith");
        authorDAO.insert(author);

        Book book = new Book();
        book.setTitle("Mr. Foo and Mr. Bar");
        book.setAuthorId(author.getId());
        bookDAO.insert(book);
        
        return null;
    }
});

As you can see, wrapping your transaction components with JtaUtil is fairly simple and consists of the following steps:

  1. Create an object of the type JtaUtil.
  2. Perform your operations on the database in the form of the interface JtaOp<String>. Usually the most convienient way of doing this is to use an anonymous class.
  3. Pass your own implementation of JtaOp<String> as an argument to the method withUserTransaction(...) from the previously created object JtaUtil.

When you perform your own operation on a database as a class of the type JtaOp<T>, you are able to return its result. When implementing the JtaOp<T> interface, you have to state a type for the return value (T), and the value itself may be returned in the implementation of the method <T> invoke(UserTransaction). If you do not want to return a result (as in the example above), you can set the type of the return value to java.lang.Object and return null in the method Object invoke(UserTransaction).

5. Using transaction API with MySQL

OneWebSQL™ transaction API uses transaction mechanisms of the underlying database. If the database doesn't support transactions, then OneWebSQL™ database operations are performed as if they were invoked outside of a transaction.

This applies to MySQL, a popular open-source database. MySQL uses several database engines and some of them do not support transactions. Two most popular MySQL engines are:

If you invoke OneWebSQL™ transaction methods with database engine, such as MyISAM, then the code behaves as if each database operation was invoked in a separate transaction with the auto commit property set to true. You will get no errors or warnings indicating that something went wrong.

See an example:

public void testMyISAM {
	// create transactional data source wrapper
	TransactionalDataSource transactionalDs = new TransactionalDataSource(ds);
	BookDAO dao = new BookDAOImpl(transactionalDs, getDbAdapter());

	// transaction #1
	transactionalDs.beginTransaction();

	Book book = new Book();
	book.setId(1);
	book.setTitle("War and Peace");
	dao.insert(pojo);

	// does nothing, the insert is NOT rolled back
	transactionalDs.rollbackTransaction();

	// transaction #2
	transactionalDs.beginTransaction();

	List<Book> l = dao.getBookList();

	// no transactions in MyISAM so the row stays!
	assertEquals(1, l.size());

	//does nothing
	transactionalDs.commitTransaction();
}

The method rollbackTransaction() does not rollback anything. The database operations are not rolled back. Similarly, if an exception occurs, then the operations are not rolled back either.