Kbase P8428: How to maintain consistency across databases that are runnin
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/12/2003 |
|
Status: Unverified
GOAL:
How to maintain consistency across databases that you are running distributed transactions?
GOAL:
What is the difference between using 2-phase commit rather than just a distributed transaction?
FIX:
An example:
DO TRANSACTION:
FIND FIRST sports1.customer.
sports1.customer.credit-limit = sports1.customer.credit-limit - 10.
FIND sports2.customer WHERE sports2.customer.cust-num = sports1.customer.cust-num.
sports2.customer.credit-limit = sports2.customer.credit-limit + 10.
END.
If there is a crash at any time up to the start of the END statement, then the transaction is rolled back from each database, because the updates are contained within the same transaction block. So in this situation database integrity is maintained.
2-phase commit is a step further. It safeguards what goes on during the execution of the END statement. During the execution of the END statement, the changes are committed to the database. What happens if there is a crash during execution of this?
In our example we have two databases on the same machine, suppose we have the bi files on that machine as well (but on a separate disk). No 2-phase commit is running. The procedure runs, gets to the END, the changes are commited to sports1 and then at that moment when they should be committed to sports2, the crash occurs. You restart and crash recovery takes place - but the databases are out of sync, because everything had already been committed to sports1 (the bi notes have been written out), and crash recovery rolls the changes back from sports2. As far as it goes, expected behaviour.
2-phase commit would safeguard against this situation. Although we are talking perhaps about milliseconds in which a crash might occur during the execution of the END statement, and you might have to be unlucky as well, it could happen. In order to ensure the data integrity in this kind of situation, 2-phase commit should be used.