Chapter 10: Transactions

Overview

Although it's generally considered to be an advanced topic, the concept of transactions is easy to understand because it models natural processes that happen in our everyday lives.

Transactions aren't necessarily related to financial operations, but this is the simplest way to look at them. For example, every time you buy something, a transaction happens—you pay an amount of money and then receive in return the product for which you paid. The two operations (paying the money and receiving the product) form the transaction.

Imagine that a customer called Sally wants to transfer $1,000 from her checking account to her savings account. From Sally's point of view, this operation is a single operation (one money transfer operation), but in fact inside the database something like this happens:

UPDATE Checking
   SET Balance = Balance - 1000
   WHERE Account = 'Sally';
UPDATE Savings
   SET Balance = Balance + 1000
   WHERE Account = 'Sally';

Of course, this piece of SQL code is a simplified version of what really happens inside a production database, but it demonstrates how a set of simple operations can form a single larger operation. In this example you have only two operations, but in reality there are likely to be many more. When you perform such a complex operation, you need to be assured it really executes as a single, atomic operation.

This isn't as easy as it sounds because you need to make sure all the constituent operations execute successfully—if any of them don't (and this can happen because of a wide area of reasons such as operating system or database software crashes, computer viruses, or hardware crashes), the larger operation fails.

In the bank scenario, two database operations need to happen in order to correctly transfer the money—if one of the two operations fails (say, a hardware failure happens after the first UPDATE), Sally would lose $1,000. You need to find a way to ensure the two UPDATE statements both either execute successfully or don't execute at all.

Fortunately, modern databases have the technology to protect transactions for you—you just need to tell the database about the statements that form a transaction, and the database will do everything for you.