12.3 Using Transactions

There's one important database feature we have not discussed yet. In the examples in this chapter, only one SQL statement is needed to complete all database modifications for each request. This statement either succeeds or fails. However, sometimes you need to execute two or more SQL statements in sequence to update the database. A typical example is transferring money between two accounts; one statement removes some amount from the first account, and another statement adds the same amount to the second account. If the first statement is successful, but the second one fails, you have performed a disappearance act your customers aren't likely to applaud.

The solution to this problem is to group all related SQL statements into what is called a transaction. A transaction is an atomic operation, so if one statement fails, they all fail; otherwise, they all succeed. This is referred to as committing (if it succeeds) or rolling back (if it fails) the transaction. If there's a problem in the middle of a money transfer, for instance, the database makes sure the money is returned to the first account by rolling back the transaction. If no problems are encountered, the transaction is committed, permanently storing the changes in the database.

There's a JSTL database action to handle transactions, described in Table 12-9.

Table 12-9. Attributes for JSTL <sql:transaction>

Attribute name	Java type	Dynamic value accepted	Description
dataSource	javax.sql.DataSource or String	Yes	Optional. The DataSource to use.
isolation	String	Yes	Optional. One of read_committed, read_uncommitted, repeatable_read, or serializable.

We will use it for real in Chapter 13, but let's take a quick look at how it could be used in this fictitious example:

<sql:transaction>
  
  <sql:update>
    UPDATE Account SET Balance = Balance - 1000
      WHERE AccountNumber = 1234
  </sql:update>
  <sql:update>
    UPDATE Account SET Balance = Balance + 1000
      WHERE AccountNumber = 5678
  </sql:update>
  
</sql:transaction>

SQL actions that make up a transaction are placed in the body of a <sql:transaction> action element. This action tells the nested elements which database to use, so if you need to specify the database with the dataSource attribute, you must specify it for the <sql:transaction> action.

The isolation attribute can specify special transaction features. When the DataSource is made available to the application through JNDI or by another application component, it's typically already configured with an appropriate isolation level. This attribute is therefore rarely used. The details of the different isolation levels are beyond the scope of this book. If you believe you need to specify this value, you can read up on the differences in the JDBC API documents or in the documentation for your database. You should also be aware that some databases and JDBC drivers don't support all transaction isolation levels.

The <sql:transaction> action gets a connection from the data source and makes it available to all database actions within its body. If one action fails, the transaction is rolled back; otherwise the transaction is committed at the end of the <sql:transaction> body.