Commit and rollback of transactions

At any time, an application process might consist of a single transaction. However the life of an application process can involve many transactions as a result of commit or rollback operations.

A transaction begins when data is read or written. A transaction ends with a COMMIT or ROLLBACK statement or with the end of an application process.

  • The COMMIT statement commits the database changes that were made during the current transaction, making the changes permanent.

    DB2® holds or releases locks that are acquired on behalf of an application process, depending on the isolation level in use and the cause of the lock.

  • The ROLLBACK statement backs out, or cancels, the database changes that are made by the current transaction and restores changed data to the state before the transaction began.

The initiation and termination of a transaction define points of consistency within an application process. A point of consistency is a time when all recoverable data that an application program accesses is consistent with other data. The following figure illustrates these concepts.

Figure 1. A transaction with a commit operation
Begin figure description. An arrow pointing to the right shows a transaction time line. The beginning of the transaction is at the left end. The commit is at the right. End figure description.

When a rollback operation is successful, DB2 backs out uncommitted changes to restore the data consistency that existed when the unit of work was initiated. That is, DB2 undoes the work, as shown in the following figure. If the transaction fails, the rollback operations begins.

Figure 2. Rolling back changes from a transaction
Begin figure description. An arrow pointing to the right shows a time line for a transaction. In the middle of the arrow is a failure in the transaction. End figure description.

An alternative to cancelling a transaction is to roll back changes to a savepoint. A savepoint is a named entity that represents the state of data at a particular point in time during a transaction. You can use the ROLLBACK statement to back out changes only to a savepoint within the transaction without ending the transaction.

Savepoint support simplifies the coding of application logic to control the treatment of a collection of SQL statements within a transaction. Your application can set a savepoint within a transaction. Without affecting the overall outcome of the transaction, application logic can undo the data changes that were made since the application set the savepoint. The use of savepoints makes coding applications more efficient because you don't need to include contingency and what-if logic in your applications.

Now that you understand the commit and rollback process, the need for frequent commits in your program becomes apparent.