Beginning SQL

Chapter 11. Transactions

When you move out of the realm of single-user databases, ensuring the validity of the data from the time you start to make modifications to the time you finally finish making modifications becomes critical. Modern databases may host hundreds or even thousands of users and even host programs running autonomously and automatically without human input or intervention. If each of these users and programs can independently make changes to data throughout the database, the probability of collisions between users updating the data becomes almost a certainty.

Even in so-called small databases, it isn't uncommon for the majority of users to all be using a small segment of the data, typically the most recent data. In many cases, older data is used for historical purposes only, examined only for reports about past performance. In many databases, the data entered in the last month or even the last few days is far more likely to be updated and examined than data from a year ago. An insurance company has thousands or even millions of claim records but is actively working on only a few hundred or thousand right now. A manufacturing company may have hundreds of thousands of orders over the last 10 years but only a few hundred that are currently being processed. A hospital has thousands of patient records in their database but only a couple hundred people currently in the hospital and a few hundred more being seen on a daily or weekly basis.

Most users need to see only these ...