Chapter 7. Transactions

 

This chapter covers

  • Introduction to transactions
  • Automatic, local, and global transactions
  • Custom transactions
  • Transaction demarcation

Transactions are one of the most important concepts to understand when working with a relational database. Few decisions you make will have a greater impact on stability, performance, and data integrity. Knowing how to identify and demarcate the transactions in the system you are building is imperative. In this chapter, we’ll discuss what transactions are and how to work with them.

7.1. What is a transaction?

In the simplest terms, a transaction is a unit of work, usually involving a number of steps that must succeed or fail as a group. Should any step in the transaction fail, all steps are rolled back so that the data is left in a consistent state. The easiest way to explain a transaction is with an example.

7.1.1. A simple banking example

A common example of why transactions are important is a bank funds transfer. Consider two bank accounts owned by Alice and Bob, as shown in table 7.1.

Table 7.1. Starting balances

Alice’s account

Bob’s account
Balance $5,000.00 Balance $10,000.00

Now consider a transfer of $1,000.00 from Alice to Bob (table 7.2).

Table 7.2. Desired transaction

Alice’s account

Bob’s account
Balance $5,000.00 Balance $10,000.00
Withdrawal $1,000.00    
    Deposit $1,000.00
Balance $4,000.00 Balance $11,000.00

7.2. Automatic transactions

7.3. Local transactions

7.4. Global transactions

7.5. Custom transactions

7.6. Demarcating transactions

7.7. Summary