As you can see, updates A and B get posted to the database. If the SQL code indicates that the transactions have completed successfully, the system will issue COMMIT statements to A and B. The point of exposure occurs when a failure happens after the COMMIT of A and before the COMMIT of B. Of course, the exposure can only happen when the failure occurs exactly between the commit of A and the commit of B. However, it is an exposure that has the potential to cause a major loss of integrity within the federation, and an exposure for which there is no automated recovery. The only remedy is to notify the DBA that the transaction has terminated, and manually roll back the updates to A and B. This type of corruption has a very small probability, and most databases do not worry about this exposure.

Understanding The Two-Phase Commit

Following the receipt of a message from each remote server stating that the transaction was successful, the initiating Oracle instance begins the two-phase commit. The commit itself consists of several steps: the prepare phase, the commit phase, and the forget phase.

To demonstrate, here is an SQL query that simultaneously updates all rows in a horizontally partitioned table residing in London and Paris. Assume that the SQL originates from a remote database in Denver, and that the Denver database will manage the two-phase commit:

UPDATE  employee@london,
        employee@paris
SET salary = salary * 1.1;

The initiating database in Denver is elected to manage the transaction, and will direct all stages of the two-phase commit. Most two-phase commit mechanisms have three phases:

1.  Prepare phase—The prepare phase ensures that none of the remote databases will inadvertently issue a COMMIT or a ROLLBACK unless the initiating transaction directs the remote database to do so. In Oracle, the remote databases “promise” to allow the initiating request to govern the transaction at the remote database. The initiating request then converts its own locks to “in-doubt” locks that prevent either read or write operations against the data in question.

2.  Commit phase—The initiating database commits and then instructs each remote database to commit. Each remote database then informs the initiating database that its commit transaction has been executed.

3.  Forget phase—After all remote databases have committed, the initiating database “forgets” about the transaction, releasing all in-doubt locks. Each remote database then, in turn, releases its transaction locks.

Some of the more sophisticated databases allow the database administrator to manually recover any in-doubt transactions that remain after a failure in a distributed database network. For example, a common reason that distributed database transactions fail is from problems with the communications lines. These failures may cause an update against several remote databases to fail during any point of the prepare, commit, or forget phases. The database will detect the lost connection, and depending upon the state of the transaction, it will direct the online component to either COMMIT or ROLLBACK its part of the update. The remaining transaction piece, since it cannot be accessed due to the line failure, will be posted to an in-doubt system table. When the remote database returns online, the database will direct the failed node to either COMMIT or ROLLBACK. In the meantime, the DBA may wish to manually direct the operation of the in-doubt transactions.

Measuring Oracle Locks

While Oracle locking mechanisms appear to be very complex on the surface, some useful scripts are available that can be quickly run to identify lock resources. In Oracle, once a lock has been issued, only one of the following can be used to release the lock:

•  Ask the HOLDER to commit or rollback.

•  Kill the session which holds the lock.

•  ALTER SESSION KILL SESSION sid, serial#.

•  Use KILL USER SESSION menu found in the sqldba form.

•  Kill the Unix shadow process, which is not recommended. When killing the Unix shadow process, please be careful of shared servers in a multithreaded environment.

•  ROLLBACK FORCE or COMMIT FORCE if it is a two-phase commit transaction.

While many tools can be used to measure Oracle locks, Listing 5.2 will show all locking activity.

Listing 5.2 A locking script.

locks.sql - shows all locks in the database.
SET LINESIZE 132
SET PAGESIZE 60
COLUMN OBJECT HEADING 'Database|Object' FORMAT a15 truncate
COLUMN lock_type HEADING 'Lock|Type'    FORMAT a4 truncate
COLUMN mode_held HEADING 'Mode|Held'    FORMAT a15 truncate
COLUMN mode_requested HEADING 'Mode|Requested' FORMAT a15 truncate
COLUMN sid HEADING 'Session|ID'
COLUMN username HEADING 'Username'             FORMAT a20 truncate
COLUMN image HEADING 'Active Image'            FORMAT a20 truncate

SPOOL /tmp/locks
SELECT
        c.sid,
        substr(object_name,1,20) OBJECT,
        c.username,
        substr(c.program,length(c.program)-20,length(c.program)) image,
        DECODE(b.type,
                'MR', 'Media Recovery',
                'RT', 'Redo Thread',
                'UN', 'User Name',
                'TX', 'Transaction',
                'TM', 'DML',
                'UL', 'PL/SQL User Lock',
                'DX', 'Distributed Xaction',
                'CF', 'Control File',
                'IS', 'Instance State',
                'FS', 'File Set',
                'IR', 'Instance Recovery',
                'ST', 'Disk Space Transaction',
                'TS', 'Temp Segment',
                'IV', 'Library Cache Invalidation',
                'LS', 'Log Start or Switch',
                'RW', 'Row Wait',
                'SQ', 'Sequence Number',
                'TE', 'Extend Table',
                'TT', 'Temp Table',
                b.type) lock_type,
        DECODE(b.lmode,
                0, 'None',                /* Mon Lock equivalent */
                1, 'Null',                /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',          /* R */
                4, 'Share',               /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',           /* X */
                to_char(b.lmode)) mode_held,
         DECODE(b.request,
                0, 'None',                /* Mon Lock equivalent */
                1, 'Null',                /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',          /* R */
                4, 'Share',               /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',           /* X */
                to_char(b.request)) mode_requested
FROM sys.dba_objects a, sys.v_$lock b, sys.v_$session c WHERE
a.object_id = b.id1 AND b.sid = c.sid AND OWNER NOT IN ('SYS','SYSTEM');