Transactions

The database operations you have seen so far involve only a single table. Even when you have been inserting related rows in separate tables, each operation is essentially independent. Frequently, though, in real database systems a single task may involve updating several tables, and all updates must be successful in order for the overall task to succeed.

As an example, consider a database that supports an e-commerce Web site. The company needs to maintain a stock of products, and when a sale is made, the quantity of product available needs to be reduced. A customer may buy more than one product during a single visit to the site, and a confirmation receipt is sent by email when the sale is complete. The ER diagram below shows the tables needed to represent this information.

To explain the diagram a little: one customer can make many purchases, and each purchase is represented by a record in the invoice table. Each invoice may have several lines, and each line represents a product that the customer is buying. The line indicates how many units of the product that the customer is buying, and maintains a record of the unit price. This is needed because the price of the product may change over time, but the line price must remain the same. VAT is calculated for the whole invoice, and therefore the total cost of the purchase including tax is held on the invoice record. The product record shows how many units are available in the prod_quantity column, and this value is reduced whenever some units are sold.

When a purchase is made, several updates must be made:

An invoice record must be inserted with a new inv_id
A line record must be inserted for each different product using the same inv_id
Each product record must be updated to reduce the prod_quantity
Once all the lines have been inserted, the invoice record must be updated with the total value including tax

If any one of these steps fails for some reason, then the overall task of recording the sale is incomplete and can cause problems. For example, if the prod_quantity value is not updated on the product record, it will look as if there is more of a product available than there actually is. If the final step is missed, the customer will not be charged the VAT.

These operations must either all succeed of all fail to leave the database in a consistent state. They therefore represent a logical unit of work which includes a set of database operations, and that is the definition of a database transaction.

Another way of saying that a transaction cannot be split into smaller parts would be to say it is atomic.

Transactions

Further reading