This single statement may update many rows, and the concurrency manager must check for contention (i.e., shared locks). If any other tasks are viewing any other rows in the database, the engine will set as many exclusive locks as possible, and put the statement into a wait state until the shared locks from other tasks have been released. Only after all the desired rows are free will the transaction be completed.

Alternative Locking Mechanisms For Oracle

The problems of lock pool resources and database deadlocks have lead to some creative alternatives to Oracle’s shared and exclusive locks. Locking can be turned off in Oracle by issuing a COMMIT statement immediately after the SELECT. Without long-term shared locks, lock pool utilization is reduced and the potential for database deadlocks is eliminated. But we still must deal with the problem of ensuring that updates are not overlaid. Consider Figure 5.5, which is an example of updates without locking.

Figure 5.5  Accidental overlaying of data.

Both tasks have selected Burleson’s employee record and issued COMMIT statements to release the locks. Task B now changes Burleson’s performance rating to a 12 and issues an UPDATE that writes the column back to the database. Task B, which is now looking at an obsolete copy of Burleson’s performance_flag, changes the salary to $21,000, improperly assigning Burleson’s raise. This is a type of “logical” corruption, whereby a user may rely on outdated values in other rows, updating the target column based upon the obsolete information.

Some databases such as SQL/DS and Oracle allow SELECT FOR UPDATE commands. With SELECT FOR UPDATE, the before image of the row is compared with the current image of the row before the update is allowed.

If you must communicate with non-Oracle databases that do not support SELECT FOR UPDATE, several clever techniques are available to release locks while still maintaining database integrity.

The WHERE Clause

For example, if Burleson’s row contains the fields in Figure 5.6, each of the fields will be specified in the update command, even if the user has not changed the item. If any of the values have changed since the row was initially retrieved, the database will reject the transaction with a “not found” SQL code. The application could interpret this code, re-retrieving the updated record and presenting it to the user with its new value.

Figure 5.6  A solution to the update anomaly.

---

NOTE:  It is very important to run the EXPLAIN utility with this type of update statement to be sure that the SQL optimizer uses the employee index to locate the row. With more than two items in the WHERE clause, some optimizers may become confused and service the request by using a full-table scan, causing significant performance delays. With Oracle SQL, the programmer can specify the index that will be used to service the query, although other implementations of SQL require that the indexed field be specified first in the WHERE clause. Regardless, it is important to run an EXPLAIN PLAN on the SQL to be sure that the additions to the WHERE clause do not impede performance.

---

As demonstrated earlier, Oracle has SQL extensions that can explicitly “turn off” unwanted indexes. For example, indexes can be turned off by altering the key field that is used in the WHERE clause.

Assume that the emp_table has the following definition:

CREATE TABLE emp_table (
    emp_name             char(20),
    sex                  char(1),
    performance_flag     number,
    salary               decimal(8,2) );

Let’s further assume that emp_name has a unique index—and that the field’s sex, performance_flag, and salary have nonunique indexes. To ensure that the index on emp_name is used, the index key fields will be “altered” by concatenating a null string to the end of the char columns and adding a 0 to the end of each numeric column.

Hence, the following SQL update would read:

UPDATE emp_table
    SET salary             = salary*1.05
WHERE
    emp_name               = 'Burleson'
AND
    performance_flag+0     = 9
AND
    sex || ''              = 'M'
AND
    salary+0               = 20000;

Another method can guarantee the use of the emp_name index. We could alter the emp_name index to include the nonunique fields of performance_flag, sex, and salary, creating a large concatenated index on every column in the table. A slight index overhead will occur, but you can be assured that all index updates perform efficiently without relying on the SQL programmers to alter their SQL.

The Date-Time Stamp Solution

This solution requires that a date-time stamp column be added to each table that may be updated. All applications are required to select this column and include it in the WHERE clause when issuing any UPDATE SQL. Of course, if the row has changed, the date-time stamps will not match, and the transaction will fail with a “not found” SQL code.

Locking And Distributed Databases

Whether the manager chooses to purchase an Application Programming Interface (API) or to create a custom API, it is very important to realize that the nature of the data access can tremendously impact the complexity of the processing. For read-only databases, API processing is relatively simple. However, processing problems increase exponentially for systems that require cohesive updating. In fact, it is impossible to implement any cohesive updating scheme with 100 percent reliability.

Distributed databases have an inherent updating problem that is common to all systems. This exposure occurs when a federated database attempts to simultaneously update two distributed databases. Commonly known as the two-phase commit (2PC), the procedure is illustrated in the following example:

APPLY UPDATE A
APPLY UPDATE B
IF A-OK And B-OK
    COMMIT A
            < ====  "Here is the deadly exposure"
    COMMIT B
  ELSE
    ROLLBACK A
    ROLLBACK B