DISTRIBUTED DATABASE SYSTEMS - Database Design and Development: An Essential Guide for IT Professionals - page 574

Optimization Considerations From our discussion in the previous subsection,
you realize how complex query optimization would be in a distributed database
environment. The more intricate the data distribution, the more complicated
query optimization turns out to be. You have seen how, even in a very simple
example of EMPLOYEE, PROJECT, and ASSIGNMENT relations distributed
across two sites, several plausible options exist for query processing. Can
you imagine how these would be multiplied in a real-life distributed database
environment?
We discussed earlier the issues and factors involved in optimizing queries in
a centralized database system. We need to include the challenges caused by the
following additional factors in a distributed database environment:
• Scope, extent, and composition of the data fragmentation scheme
• Data replication scheme requirement decisions on which copies to use
• Options for performing primitive operations on the data fragments at differ-
ent sites
• Creation and execution of subqueries
• Consolidation of results of subqueries
• Transmission of final result to the requesting user
• Network configuration and data transmission costs
• Necessity for the DDBMS to preserve the autonomy of local DBMSs
Transaction Processing
Consider a transaction in a centralized database system. Each transaction performs
database operations as an atomic unit of work. In its execution, a transaction may
perform operations on several database objects. Even as a transaction proceeds
through its execution reading, updating, inserting, and deleting database items, it
executes in a manner such that its ACID properties are maintained.
In a distributed database system, the parameters of transaction execution do
change. A transaction must execute in such a manner as to preserve its ACID prop-
erties. Either all the updates the transaction makes to database items take place, or
none at all. Still, as in the case of a centralized environment, a transaction must leave
the overall distributed database in a correct and consistent state whether it commits
or aborts. A transaction in a distributed environment is expected to perform as effi-
ciently as in a centralized environment.
One major difference, however, is that all the data objects a transaction operates
on may not be at a single site in a distributed environment. In fact, in almost all
cases of transactions, they operate on database objects dispersed across different
physical locations. Figure 18-17 provides a transaction processing example.
In this example, a transaction has to perform updates to employee records stored
at three sites. The DDBMS must coordinate the execution of the transaction in
cooperation with the local DBMSs at the three sites. Let us trace the execution of
the transaction.
• Transaction requests locks or whatever other means available in the local
DBMSs to gain shared or exclusive control over the database objects accessed.