Learning SQL

Chapter 12. Transactions

All of the examples thus far in this book have been individual SQL statements. This chapter explores the need and the infrastructure necessary to execute multiple SQL statements together.

Multiuser Databases

Database management systems allow not only a single user to query and modify data, but multiple people to do so simultaneously. If every user is 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, then there is quite a bit more bookkeeping to be done by the server.

Let’s say, for example, that you are running a report that shows the available balance for all of the checking accounts opened at your branch. At the same time you are running the report, however, the following activities are occurring:

• A teller at your branch is handling a deposit for one of your customers.

• A customer is finishing a withdrawal at the ATM machine in the front lobby.

• The bank’s month-end application is applying interest to the accounts.

While your report is running, therefore, the data is being modified by multiple users, so what numbers should appear on the report? The answer depends somewhat on how your server handles locking , which is the mechanism used to control simultaneous use of data resources. Most database servers use one of two locking strategies:

• Database writers must request and receive from ...