Databases Reference
In-Depth Information
3.
Depending on the transaction isolation settings, the database locks the
affected tables (or individual rows) for update.
4.
The databases send messages that they are ready to commit.
5.
Transaction process control enters the commit phase and issues a
commit instruction to each affected database.
This process requires
well-connected
servers, meaning that there is a full-
time, reliable, high-bandwidth connection. If there is a problem anywhere at any
step in the process or if the connection to any involved server is lost, the trans-
action is rolled back on all servers.
Microsoft has a component (separate from SQL Server) that is designed
specifically to manage distributed transactions, called the
Microsoft Distributed
Transaction Coordinator (MS DTC).
Because it is separate from SQL Server,
MS DTC can manage distributed transactions when using data sources in addi-
tion to SQL Server (in a heterogeneous environment), or even when processing
transactions that don't include SQL Server as a data source.
When initiating an explicit distributed transaction through SQL Server, you
run BEGIN DISTRIBUTED TRAN to identify it as such. To complete the trans-
action, run COMMIT TRAN or ROLLBACK TRAN from the command line,
script, or application. When you execute BEGIN DISTRIBUTED TRAN, the MS
DTC takes over management of the process, enlisting the servers involved and
ensuring that all servers are ready before committing the transactions.
One reason that the process is similar for many other manufacturers is
because they also use calls to MS DTC to manage distributed transactions when
the database server is deployed in a Windows networking environment. Those
manufacturers who don't often embed a distributed transaction management
component into the database server. Watch carefully, though, if you know that
you'll be deploying in a distributed data environment. Some manufacturers pro-
vide no direct support for distributed transactions, leaving it up to you to build
these controls into your data application.
Managing Distributed Queries
A
distributed query
is one that retrieves data from different databases. In most
cases, it includes a
distributed join,
which is one that joins data from the dif-
ferent databases. This doesn't necessarily mean a physically distributed environ-
ment, because the process of joining data between different databases hosted on
the same database server is also considered a distributed join. As with distrib-
uted transactions, methods for handling distributed queries vary between differ-
ent manufacturers, but there are similarities. The easiest way to understand the
process is through an example. We'll use SQL Server 2005 as our sample data-
base server, but even when initiating the join from a SQL Server database server,
the joined tables can include tables hosted on other DBMSs.
