Page 713
When referring to a distributed transaction and the two-phased commit, there is a complexity that may seem overwhelming. There is one primary idea to keep in mind: Oracle7 and Oracle8 manage the distributed transaction automatically. Most DBAs will not have the opportunity to actually see a two-phased commit because they are trigger-based processes. Figure 28.2 shows an example of a two-phased commit.
BWC has a specifications table (SPEC_TABLE) that depends upon the regulations table within the U.S. (US_REG). When new data is inserted or updated in the US_REG table, the application updates and inserts data into SPEC_TABLE at sites in Germany, Italy, and Detroit.
FIG. 28.2
A two-phased commit.
A two-phased commit occurs with all data manipulation language (DML), and as the name implies, it is made up of two distinct phases. In order to ensure consistency in the explanation, the DML used in this section will be an update.
The first phase of a two-phased commit is called the prepare phase. The initiating database is also known as the global coordinator. The global coordinator sends a message to all the other databases informing them that an update is about to occur. This message does not have the actual update, just a small amount that enables the other databases to know what is to be updated. The receiving databases will respond to the global coordinator if the update can occur. The receiving databases can respond in three ways:
NOTE |
The global coordinator will determine the order of the databases to be updated.n |
Page 714
The receiving databases must also perform some system checks, including sending a prepared statement to any of its own coordinating databases. This includes placing locks (if not read-only) on the row to be updated. After all the receiving databases have responded, the global coordinator then places the transaction in-doubt. If all the children respond with prepared or read-only, the global coordinator will determine the commit point site.
The DBA will determine which database should be a commit point site by the stability of the databases. For example, a database on a UNIX server will be more stable than a database on a PC. The primary goal is to pick a database that will not create a lock due to an in-doubt transaction. A transaction that is rolled neither back nor committed is referred to as an in-doubt transaction.
The DBA assigns commit point sites by the value COMMIT_POINT_STRENGTH in the parameter file. The higher value in the distributed tree determines the commit point strength. If the system is highly distributed, more than one database should be assigned as the commit point site. This is also applicable if transactions affect different databases. The commit point site is the first database to be committed. Then it keeps up with the status of the transaction until all transactions have been completed.
The second phase, or the commit phase, is simply the process of committing the data. After all the receiving databases have successfully committed and communicated their success to the global coordinator, the global coordinator sends a message to the commit point site. The commit point site removes the status as in-doubt. Each of the children will commit and release the row-level lock on the record.
When the child responds with an abort message, the entire transaction is rolled back. The transaction is an all-or-nothing process. In order to ensure data consistency, all the children must either commit or roll back the master transaction. The DBA must be able to identify and correct an unsuccessful commit to effectively manage this type of distributed system.
NOTE |
When a commit is unsuccessful, the program or person performing the update must be notified that an error has occurred. This notification should occur by design.n |
NOTE |
If a transaction fails to commit on one of the nodes of a distributed system, the transaction is rolled back on all nodes. When creating rollback segments, all of the hosts (nodes) in the distributed system must be able to accommodate the largest transaction anticipated on the distributed system.n |
Network problems are the most likely cause of in-doubt transactions. Just as in the management of the two-phased commit, Oracle7 and Oracle8 automatically manage any problems detected during a network failure. As it is easier to allow the automatic recover in the disaster recovery process, Oracle recommends that the DBA allow the automatic recover of an in-doubt transaction. There are some exceptions to this recommendation. Problems may occur due to row-level locks placed on in-doubt transactions during the network failure. This can make data
Page 715
inaccessible for other users trying to access the rows in question during the failure. A DBA may need to manually manage the transaction in order to remove these locks. The process of managing the transactions is referred to as forcing a transaction.
In the event of a network loss, it will most likely be evident to not only the DBA, but also the entire user community affected by that network. Other users with databases not affected by the downed network will continue to work. Users will notice a problem when they try to perform DDL on the inaccessible database. The problem that a DBA will have to address is the lock. DBAs with experience on earlier versions of SQL*Net are familiar with the daemon locks that occurred when a connection was dropped. This is the same type of scenario that occurs when the network drops during a distributed transaction. The primary difference is that if left alone, the distributed transaction will correct itself when the network is back up. If a network is going to be down for a long period of time, the DBA should be prepared to force any outstanding transactions that are causing locks.
The actual process of forcing the transaction to either rollback or commit is simple. The problem is that the DBA or DBAs must force the transaction locally. This can mean that if one DBA is responsible for managing several databases, each transaction will have to be manually investigated. For example, a user in Detroit has updated a row in a table that is to update the same table in Milan and Frankfurt. If the transaction failed prior to writing to any system, the DBA should log on to each computer and force the transaction at each site. The force can be in either the form of a commit or a rollback. Although cumbersome, this should not be something that will have to be done frequently.
Great care should be taken when manually forcing a transaction. This should be done as an all-or-nothing solution. When performed, the DBA should either coordinate with other DBAs to ensure that the modification matches the other environments or validate the consistency on all databases by hand. This means querying each database affected and ensuring that the data is exactly the same on each site. If this is not done, inconsistencies of the data can occur. The steps to manually force a transaction are as follows:
COMMIT COMMENT `transaction name';
NOTE |
The single quotes are required and the transaction name is case sensitive.n |