Programming the Perl DBI

Transactions, Locking, and Isolation

The final topic in this chapter deals with the important (and hair-raising!) topic of transaction handling.

Transaction handling is a feature of the more powerful database systems in which SQL statements can be grouped into logical chunks. Each chunk is known as a transaction, and the operations it performs are guaranteed to be atomic for the purposes of recovery. According to the ANSI/ISO SQL standard, a transaction begins with the first executable SQL statement and ends when it is explicitly committed or rolled back.

The process of committing data writes it into the database tables and make it visible to other concurrent users. Rolling back discards any changes that have been made to any tables since the beginning of the current transaction.

The standard example to explain transactions is a bank transfer in which a customer transfers $1000 from one bank account to another. The bank transfer consists of three distinct stages:

1. Decrease the source account by the required amount.

2. Increase the target account by the required amount.

3. Write a journal entry recording the transfer.

When viewed as three separate stages, the possibility of disaster is quite obvious. Suppose there’s a power outage between stages 1 and 2. The hapless customer is now $1000 poorer, as the money has neither reached the target account nor been logged in the transfer journal. The bank is now $1000 richer.^[60]

Of course, if the power outage occurred between stages 2 and 3, the customer ...