Learning Oracle PL/SQL

9.4. Transaction Control

In the web-based library application that has been growing through the chapters of this book, I have not had much opportunity to explore the idea of database transactions. There is some passing discussion of COMMITs and ROLLBACKs in Chapter 5 and Chapter 8, but in this section I want to explain these statements more directly. Gaining a better understanding of the underlying concepts could be valuable to you as you develop your own applications.

9.4.1. What Is a Transaction?

A transaction, also known as a "logical unit of work," consists of one or more data changes in the database, which must all execute, or all fail, together. That is, a failure in any part of a transaction results in the failure of the entire transaction. The archetypal transaction you will find described in every textbook is a debit and credit operation at the bank: subtract money from your savings account, and add the same amount into your checking account. Given the design of most databases, these would be two separate steps. If the debit step occurs but not the credit step, you're not going to be very happy; if the reverse happens, the bank won't be happy.

What textbooks don't always come out and state is the big assumption behind transactions: they make failure only slightly more tolerable. Yes, failing the entire transaction does leave the database in an "internally consistent" state, but it is still in a state that doesn't match what it is supposed to be. I really did want ...