Learning SQL, 3rd Edition

Chapter 12. Transactions

All of the examples thus far in this book have been individual, independent SQL statements. While this may be the norm for ad hoc reporting or data maintenance scripts, application logic will frequently include multiple SQL statements that need to execute together as a logical unit of work. This chapter explores transactions, which are the mechanism used to group a set of SQL statements together such that either all or none of the statements succeed.

Multiuser Databases

Database management systems allow a single user to query and modify data, but in today’s world there may be thousands of people making changes to a database simultaneously. If every user is only executing queries, such as might be the case with a data warehouse during normal business hours, then there are very few issues for the database server to deal with. If some of the users are adding and/or modifying data, however, the server must handle quite a bit more bookkeeping.

Let’s say, for example, that you are running a report that sums up the current week’s film rental activity. At the same time you are running the report, however, the following activities are occurring:

• A customer rents a film.

• A customer returns a film after the due date and pays a late fee.

• Five new films are added to inventory.

While your report is running, therefore, multiple users are modifying the underlying data, so what numbers should appear on the report? The answer depends somewhat on how your server ...