This chapter covers
- The concept of distributed transactions
- The Two-Phase Commit protocol in the absence of failure
- The Two-Phase Commit protocol in the presence of failure
- Possible improvements of the Two-Phase Commit protocol
Distributed transactions span changes across multiple systems. The participants in a distributed transaction are often referred to as resource managers (RMs). The term resource manager encompasses not only database systems but also other systems, such as message queuing systems, that can participate in a distributed transaction. From here on, I will use the terms resource manager and database system interchangeably.
6.1 Atomic commitment: From a single RM to multiple RMs
Let’s revisit the example money transfer discussed in chapter 5. The following listing illustrates a transaction that executes on a single RM. It transfers money from the source account to the target account regardless of overdraft.
Listing 6.1 Money transfer on one RM executing in one transaction
BEGIN UPDATE accounts SET balance = balance - $1 WHERE id = $2; UPDATE accounts SET balance = balance + $1 WHERE id = $3; COMMIT
If the source and target accounts are hosted on two separate resource managers, how do we guarantee completeness? The next listing illustrates two transactions that execute on two RMs, RM1 and RM2, to transfer money from the source account hosted on RM1 to the target account hosted on RM2.