Java Server Pages

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 required for a function. This statement either succeeds or fails. However, it’s very common that 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 fails, you have performed a disappearing act that your customers are not 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 custom action in the book tag library to handle transactions, described in Table 9.7.

Table 9-7. Attributes for <ora:sqlTransaction>

Attribute Name	Java Type	Request-Time Value Accepted	Description