What is the relationship between blocking, locking, and isolation levels?

Question

I understand a little about Oracle blocking - how updates block other updates till the transaction completes, how writers don't block readers etc.

I understand the concept of pessimistic and optimisic locking, and the typical banking textbook examples about losing lost updates etc.

I also understand the JDBC transaction isolation levels where we might say, for instance, we are happy with seeing uncommitted data.

I'm a bit fuzzy however about how these concepts are related and interact. For instance:

• Is Oracle providing pessimistic or optimistic locking by default (it just seems to block the seperate update based on experiments in two TOAD sessions.)
• If, as I suspect, these are application level concepts, why would I go to the trouble of implementing a locking strategy when I can let the database synchronise transaction updates anyway?
• How do transaction isolation levels (which I set on the connection) alter the database behaviour when other clients besides my application be accessing with different isolation levels.

Any words to clarify these topics would be really appreciated!

Answer 1

• Oracle allows for either type of locking - how you build your app dictates what is used. In retrospect, it's not really a database decision.

• Mostly, Oracle's locking is sufficient in a stateful connection to the database. In non-stateful apps, e.g., web apps, you cannot use it. You have to use application level locking in such situations because locking applies to a session.

• Usually you don't need to worry about it. In Oracle, readers never block writers, and writers never block readers. Oracle's behavior does not change with the various ANSI isolation levels. For example, there is no such thing as a "dirty read" in Oracle. Tom Kyte points out that the spirit of allowing dirty reads is to avoid blocking reads, which is not an issue in Oracle.

I would strongly recommend reading Tom Kyte's excellent book "Expert Oracle Database Architecture", in which these and other topics are addressed quite clearly.

Answer 2

Optimistic locking is basically "I'll only lock the data when I modify the data, not when I read it". The gotcha is that if you don't lock the data right away, someone else can change it before you do and you're looking at old news (and can blindly overwrite changes that have happened between when you read the data and updated it.)

Pessimistic locking is locking the data when you read it so that you'll be sure that no one has changed it if you do decide to update it.

This is an application decision, not an Oracle decision as:

SELECT x, y, z FROM table1 WHERE a = 2

will not lock the matching records but

SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE

will. So you have to decide if you're ok with optimistic locking

SELECT x, y, z FROM table1 WHERE a = 2

...time passes...

UPDATE table1
   SET x = 1, y = 2, z = 3
 WHERE a = 2

(you could have overwrote a change someone else made in the meantime)

or need to be pessimistic:

SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE

...time passes...

UPDATE table1
   SET x = 1, y = 2, z = 3
 WHERE a = 2

(you're sure that no one has changed the data since you queried it.)

Check here for isolation levels available in Oracle. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621

Answer 3

Oracle ALWAYS handles pessimistic locking. That is, it will lock a record when it is updated (and you can also hit locks for deletes and inserts if there is a key involved). You can use SELECT....FOR UPDATE to augment the pessimistic locking strategy.

Really any database/storage engine that works transactionally must do some form of locking.

The SERIALIZABLE isolation level is much closer to a optimistic locking mechanism. It will throw an exception if the transaction tries to update a record that has been updated since the start of the transaction. However it relies on a one-to-one between the database session and the end user session.

As connection pooling/stateless applications become prevalent, especially in systems with heavy user activity, having a database session tied up for an extended period can be a poor strategy. Optimistic locking is preferred and later versions of Oracle support this with the ORA_ROWSCN and ROWDEPENDENCIES items. Basically they make it easier to see if a record has been changed since you initially/last looked at it.

As that one-to-one relationship between a database session and a user-session has become legacy, the application layer has preserved more of the 'user-session' state and so become more responsible for checking that choices the user made five/ten minutes ago are still valid (eg is the book still in stock, or did someone else buy it).