A distributed transaction consists of subtransactions that execute at different sites and access local DBMSs at those sites. For example, a distributed transaction that transfers money from a bank account at site A to a bank account at site B executes a subtransaction at A that does the withdrawal and then a subtransaction at B that does the deposit.

Question

Such a distributed transaction must satisfy the ACID properties in a global
sense: it must be globally atomic, isolated, consistent, and durable. The issue is,
if the subtransactions at each site individually satisfy the ACID properties, does
the distributed transaction necessarily satisfy the ACID properties? Certainly if the
subtransactions at each site are individually durable, the distributed transaction is
durable. Give examples of situations in which

a. The subtransactions at each site are atomic, but the distributed transaction is not
atomic.
b. The subtransactions at each site are consistent, but the distributed transaction is
not consistent.
c. The subtransactions at each site are isolated, but the distributed transaction is not isolated.

Answer 1

a. The subtransaction at one site commits and the subtransaction at a different site
aborts. In the above example, the subtransaction to withdraw money commits and
the one to deposit the money aborts—the money is lost
b. A distributed database might have a global integrity constraint that two items
in databases at different sites have the same value (replicated data). The
subtransactions of a distributed transaction might maintain all the local integrity
constraints at their local database, but not maintain the global constraint that the
values of these two items are equal.
c. The DBMS at one site serializes its subtransactions in one order, and the DBMS
at a differents sites serializes its subtransactions in a different order. (The two
sites cannot agree on a serialization order.) In the above example of transferring
funds betweens accounts, a concurrent transaction to add the monthly interest
to all accounts might have a subtransaction that serializes after the withdraw
subtransaction at its site and a subtransaction that serializes before the deposit
subtransaction at its site—those funds do not get interest that month.