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