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.
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.
If every user of a database uses the technique of selecting a row
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
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 ...