A distributed DBMS must preserve the all-or-nothing quality of a SQL transaction if it is to provide distributed transactions. The user of the distributed DBMS expects that a committed transaction will be committed on all of the systems where data resides, and that a rolled back transaction will be rolled back on all of the systems as well. Further, failures in a network connection or in one of the systems should cause the DBMS to abort a transaction and roll it back, rather than leaving the transaction in a partially committed state.
All commercial DBMS systems that support distributed transactions use a technique called two-phase commit to provide that support. You don’t have to understand the two- phase commit scheme to use distributed transactions. In fact, the whole point of the scheme is to support distributed transactions without your knowing it. However, understanding the mechanics of a two-phase commit can help you plan efficient database access.
To understand why a special two-phase commit protocol is needed, consider the database in Figure 23-13. The user, located on System A, has updated a table on System B and a table on System C and now wants to commit the transaction. Suppose the DBMS software on System A tried to commit the transaction by simply sending a COMMIT message to System B and System C, and then waiting for their affirmative replies. This strategy works as long as Systems B and C can both successfully commit their part of the transaction.
But what happens if a problem such as a disk failure or a deadlock condition prevents System C from committing as requested? System B will commit its part of the transaction and send back an acknowledgment, System C will roll back its part of the transaction because of the error and send back an error message, and the user ends up with a partially committed, partially rolled back transaction. Note that System A can’t change its mind at this point and ask System B to roll back the transaction. The transaction on System B has been committed, and other users may already have modified the data on System B based on the changes made by the transaction.
The two-phase commit protocol eliminates the problems of the simple strategy shown in Figure 23-13. Figure 23-14 illustrates the steps involved in a two-phase commit:
- The program on System A issues a COMMIT for the current (distributed) transaction, which has updated tables on System B and System C. System A will act as the coordinator of the commit process, coordinating the activities of the DBMS software on Systems B and C.
- System A sends a GET READY message to both System B and System C and notes the message in its own transaction log.
- When the DBMS on System B or C receives the GET READY message, it must prepare to either commit or roll back the current transaction. If the DBMS can get into this “ready to commit” state, it replies YES to System A and notes that fact in its local transaction log; if it cannot get into this state, it replies NO.
- System A waits for replies to its GET READY message. If all of the replies are YES, System A sends a COMMIT message to both System B and System C, and notes the decision in its transaction log. If any of the replies is NO, or if all of the replies are not received within some timeout period, System A sends a ROLLBACK message to both systems and notes that decision in its transaction log.
- When the DBMS on System B or C receives the COMMIT or ROLLBACK message, it must do as it is told. The DBMS gave up the capability to decide the transaction’s fate autonomously when it replied YES to the GET READY message in Step 3. The DBMS commits or rolls back its part of the transaction as requested, writes the COMMIT or ROLLBACK message in its transaction log, and returns an OK message to System A.
- When System A has received all the OK messages, it knows the transaction has been committed or rolled back and returns the appropriate SQLCODE value to the program.
This protocol protects the distributed transaction against any single failure in System B, System C, or the communications network. These two examples illustrate how the protocol permits recovery from failures:
- Suppose a failure occurs on System C before it sends a YES message in Step 3. System A will not receive a YES reply and will broadcast a ROLLBACK message, causing System B to roll back the transaction. The recovery program on System C will not find the YES message or a COMMIT message in the local transaction log, and it will roll back the transaction on System C as part of the recovery process. All parts of the transaction will have b64een rolled back at this point.
- Suppose a failure occurs on System C after it sends a YES message in Step 3. System A will decide whether to commit or roll back the distributed transaction based on the reply from System B. The recovery program on System C will find the YES message in the local transaction log but will not find a COMMIT or ROLLBACK message to mark the end of the transaction. The recovery program then asks the coordinator (System A) what the final disposition of the transaction was and acts accordingly. Note that System A must maintain a record of its decision to commit or roll back the transaction until it receives the final OK from all of the participants, so that it can respond to the recovery program in case of failure.
The two-phase commit protocol guarantees the integrity of distributed transactions, but it generates a great deal of network traffic. If there are n systems involved in the transaction, the coordinator must send and receive a total of (4 * n) messages to successfully commit the transaction. Note that these messages are in addition to the messages that actually carry the SQL statements and query results among the systems. However, there’s no way to avoid the message traffic if a distributed transaction is to provide database integrity in the face of system failures.
Because of their heavy network overhead, distributed transactions can have a serious negative effect on database performance. For this reason, distributed databases must be carefully designed so that frequently accessed (or at least frequently updated) data is on a local system or on a single remote system. If possible, transactions that update two or more remote systems should be relatively rare.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.