Distributed Transactions

A distributed transaction is a transaction that spans more than one local database. To the Ingres Star user, a distributed transaction looks like a transaction on a single local database. The user issues a single commit or rollback statement to end the distributed transaction. But a distributed transaction may actually involve multiple local transactions. If it does, Ingres Star coordinates these local transactions so that they behave like a single transaction. Therefore, either all of the data in a distributed transaction is committed or none of it is. Ingres Star uses a protocol known as a two-phase commit to ensure that this is what happens.

Two-Phase Commit

The term two-phase commit comes from the fact that there are two phases to committing a distributed transaction where two or more databases are updated. The two phases consist of:

• Agreement between all sites to commit
• Committing the updates

Star manages these two phases in the following way:

• Phase 1

  Phase 1 begins when the user issues a commit statement. Ingres Star sends a prepare-to-commit notice to each database involved in the distributed transaction. If all databases indicate that they are prepared to commit, Ingres Star makes the decision to commit the transaction. The local databases remain in the prepare-to-commit state and wait for Ingres Star's instruction to commit.

• Phase 2

  Ingres Star sends a commit to all sites involved in the transaction. Ingres Star guarantees that all sites will commit.

  If the connection to a local database is lost between the time that Ingres Star decides to commit and the time the local database actually obeys that instruction, Ingres Star keeps trying to complete the transaction until the connection is restored and the commit is made. Ingres Star does not return control to the end user until all nodes have committed.

Notes:

• If any part of Phase 1 fails, for example, if Ingres Star loses a network connection to a node before all databases are prepared to commit, Ingres Star rolls back the transaction at all sites, including those that are already prepared to commit.
• If any part of Phase 2 fails, Ingres Star still eventually commits the transaction.

When Two-Phase Commit Is Not Used

Not all distributed transactions require two-phase commit. For example, a transaction that does not update, or that updates only one database, requires no coordination between databases. In this case, Ingres Star uses a single-phase commit that consists of sending commit messages to each database.

Sometimes a distributed transaction cannot use the two-phase commit protocol because one of the databases involved does not support it. For example, the following do not support two-phase commit:

• Some databases accessed using Enterprise Access products
• Ingres databases on Ingres cluster nodes

Such databases may still participate in a distributed transaction if their data is not updated, or if the databases are at the only site that is updated in a transaction. If only one site not capable of two-phase commit is involved in a multi-site update, Ingres Star will simulate two-phase commit using the protocol described in the Simulated Two-Phase Commit section.

Simulated Two-Phase Commit

In the case where an update is performed to a site that is not capable of two-phase commit and where a multi-site update transaction is required, Ingres Star simulates two-phase commit. It does this by first sending a prepare-to-commit to all sites that are capable of that protocol, then sending a commit to the single site not capable of two-phase commit, and finally sending commits to all other prepared sites. Note that Ingres Star only supports simulated two-phase commit when a single site not capable of two-phase commit is involved in a multi-site update. If more than one site not capable of two-phase commit is updated, Ingres Star refuses the attempted update.