The db_checkpoint(), db_commit() and db_update() functions are implemented in so that, as far as possible, if updates have been made to the database on more than one machine, these updates are either all committed together or all fail together.
Commit operations are normally atomic. When more than one server needs to be updated commit becomes a two phase process. In the first phase all servers (including the local machine) are asked if the commit should succeed. If any server reports that the commit should fail (for example because a record posted only to that server causes a contention error, or violates a database constraint), then the transaction will be rolled back on all servers. If all servers report that the commit should succeed then the second phase of the commit is invoked on all the servers to update all the databases. It may happen that a server goes offline between the two phases of a commit operation. A commit is considered to have succeeded if all "vital" servers are still online at the end of the commit operation, or, in the case where there are no vital servers, if at least one server is still online.
Transactions that update multiple servers are more expensive than single server transactions because of the overhead of synchronisation between the servers. Therefore they should be avoided where possible. Prior to release 4.520 the implementation of split server transactions was unreliable and could cause various internal errors to be generated, or even violate the normal serialisation rules, (which should prevent a process seeing partial transactions of other processes). This is no longer the case, and split transactions can be used where necessary and appropriate.With the exception described in Error Handling Options for Local Database Resilience, parent and orphan checking and child deletion are carried individually by each database server. No cross-database checking or child deletion is done.
In a resilient network configuration, additional commit errors may be encountered as described here.