Java Programming with Oracle JDBC

Data Integrity Solutions

Whenever more than one user accesses a database, there is the possibility that one user will inadvertently overwrite another user’s data. As we have seen from this chapter’s earlier discussions, locks alone do not guarantee data integrity. Indeed, some form of change detection is also needed. In this section, we’ll take what we’ve learned about locking and detection and formulate two pessimistic solutions and one optimistic solution to maintaining data integrity.

Pessimistic Data Integrity Solutions

Let’s start our discussion of maintaining data integrity by taking a look at two pessimistic approaches. The first is to use row locking by selecting a row FOR UPDATE NOWAIT before updating it. The second is to use implicit locking and detection.

SELECT FOR UPDATE NOWAIT

If every user of a database uses the technique of selecting a row FOR UPDATE with NOWAIT, then data integrity will be maintained, because a second user will not be able to acquire a lock on the data until the first has committed his changes. But what about detection? Detection is implicit in the fact that an application will get an SQLException with Oracle error “ORA-00054” if it cannot immediately lock the desired row. A row that can’t be locked is one that is already being modified by someone else. Hence, the contention between updates is detected before it even exists.

Although the SELECT FOR UPDATE NOWAIT approach works well, it has major concurrency and coding drawbacks. First, as soon as one ...