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 are transactions that span changes across multiple systems instead of a single system. 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 out, we 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 of a money transfer, discussed in Chapter 5. Listing 6.1 illustrates a transaction that executes on a single resource manager. It transfers money from the source account to the target account, regardless of overdraft.

Listing 6.1 Money transfer on one resource manager executing in one transaction
BEGIN
  UPDATE accounts SET balance = balance - $1 WHERE id = $2;
  UPDATE accounts SET balance = balance + $1 WHERE id = $3;
COMMIT

However, if the source and target account are hosted on two separate resource managers, how do we guarantee completeness?

6.1.1 Transaction on a single RM

6.1.2 Transaction on multiple RMs

6.1.3 Blocking and non-blocking

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

6.4 Summary