Java Programming with Oracle JDBC

Chapter 18. Detection and Locking

As I pointed out in Chapter 17 when discussing Oracle’s implicit locking mechanism during a transaction, just because you lock a resource before updating it does not prevent someone else from corrupting your update with his or her own update. As a matter of fact, database locks unto themselves do not solve the problem of multiuser data access integrity. Instead, you as the programmer are responsible for employing a methodology that will prevent application users from overwriting each other’s data.

In this chapter, we’ll look into the problem of multiuser update integrity and at how you can use locks with detection (a pessimistic approach) or update detection (an optimistic approach) to ensure the integrity of data in a multiuser application. First, we’ll examine the locking options available when utilizing an Oracle database. Then we’ll review the reasons why locks alone don’t solve the update integrity problem. We’ll continue by exploring detection techniques, that is, detecting that a change has taken place outside the current session and transaction. Next, we’ll discuss several pessimistic, high-contention approaches to solving the problem of maintaining data integrity. Finally, we’ll discuss an optimistic approach. Since there’s a popular notion that locking alone ensures data integrity, let’s start by examining Oracle’s locking mechanisms in order to debunk this notion.

Oracle’s Locking Mechanisms

Oracle provides three locking mechanisms. The ...