6 Distributed transactions

 

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.

6.1.1 Transaction on a single RM

6.1.2 Transaction on multiple RMs

6.1.3 Blocking and nonblocking

6.2 The essence of distributed transactions

6.3 Two-Phase Commit protocol

6.3.1 In the absence of failure

6.3.2 In the presence of failure

6.3.3 Improvement

Summary