Database Reference
In-Depth Information
Distributed Transactions
One of the really nice features of Oracle is its ability to transparently handle distributed transactions. I can update
data in many different databases in the scope of a single transaction. When I commit, either I commit the updates in
all of the instances or I commit none of them (they will all be rolled back). I need no extra code to achieve this;
I simply “commit.”
A key to distributed transactions in Oracle is the
database link
. A database link is a database object that describes
how to log into another instance from your instance. However, the purpose of this section is not to cover the syntax of
the database link command (it is fully documented in the
Oracle Database SQL Language Reference
manual), but rather
to expose you to its very existence. Once you have a database link set up, accessing remote objects is as easy as this:
select * from T@another_database;
This would select from the table
T
in the database instance defined by the database link
ANOTHER_DATABASE
.
Typically, you would “hide” the fact that
T
is a remote table by creating a view of it, or a synonym. For example, I can
issue the following and then access
T
as if it were a local table:
create synonym T for T@another_database;
Now that I have this database link set up and can read some tables, I am also able to modify them (assuming
I have the appropriate privileges, of course). Performing a distributed transaction is now no different from a local
transaction. All I would do is this:
update local_table set x = 5;
update remote_table@another_database set y = 10;
commit;
That's it. Oracle will commit either in both databases or in neither. It uses a two-phase commit protocol (2PC)
to do this. 2PC is a distributed protocol that allows for a modification that affects many disparate databases to be
committed atomically. It attempts to close the window for distributed failure as much as possible before committing.
In a 2PC between many databases, one of the databases—typically the one the client is logged into initially—will be
the coordinator for the distributed transaction. This one site will ask the other sites if they are ready to commit. In
effect, this site will go to the other sites and ask them to be prepared to commit. Each of the other sites reports back its
“prepared state” as
YES
or
NO
. If any one of the sites votes
NO
, the entire transaction is rolled back. If all sites vote
YES
,
the site coordinator broadcasts a message to make the commit permanent on each of the sites.
This limits the window in which a serious error could occur. Prior to the “voting” on the 2PC, any distributed error
would result in all of the sites rolling back. There would be no doubt as to the outcome of the transaction. After the
order to commit or rollback, there again is no doubt as to the outcome of the distributed transaction. It is only during
the very short window when the coordinator is collecting the votes that the outcome might be in doubt, after a failure.
Assume, for example, we have three sites participating in the transaction with Site 1 being the coordinator. Site
1 has asked Site 2 to prepare to commit, and Site 2 has done so. Site 1 then asks Site 3 to prepare to commit, and
it does so. At this point, Site 1 is the only site that knows the outcome of the transaction, and it is now responsible
for broadcasting the outcome to the other sites. If an error occurs right now—the network fails, Site 1 loses power,
whatever—Sites 2 and 3 will be left hanging. They will have what is known as an
in-doubt distributed transaction
. The
2PC protocol attempts to close the window of error as much as possible, but it can't close it entirely. Sites 2 and 3 must
keep that transaction open, awaiting notification of the outcome from Site 1.
If you recall from the architecture discussion in Chapter 5, it is the function of the
RECO
process to resolve this
issue. This is also where
COMMIT
and
ROLLBACK
with the
FORCE
option come into play. If the cause of the problem was a
network failure between Sites 1, 2, and 3, then the DBAs at Sites 2 and 3 could actually call the DBA at Site 1, ask him
for the outcome, and apply the commit or rollback manually, as appropriate.
