What is the relationship between lock, lock, and isolation levels?

Question

I understand a little about Oracle blocking - how updates block other updates until the transaction is completed, how writers do not block readers, etc.

I understand the concept of pessimistic and optimized blocking and typical examples of tutorials on the loss of lost updates, etc.

I also understand the JDBC transaction isolation levels, where we could say, for example, we are glad to see uncommitted data.

I am a little vague, but about how these concepts are related and interact. For example:

• Does Oracle have a pessimistic or optimistic default lock (it just blocks a separate update based on experiments in two TOADs.)
• If, as I suspect, these are application-level concepts, why am I trying to implement when I can enable update database transaction synchronization anyway?
• How transaction isolation levels (which I set in the connection) change the database behavior when other clients, other than my application, gain access with different isolation levels.

Any words to clarify these topics will really be appreciated!

Answer 1

• Oracle allows you to use any type of lock - how you create an application dictates what is used. In retrospect, this is not a database solution.

• Basically, an Oracle lock is sufficient to connect to a state database. In stateless applications, such as web applications, you cannot use it. You should use application-level locking in such situations, since the lock applies to the session.

• Usually you do not need to worry about this. In Oracle, readers never block writers, and writers never block readers. Oracle behavior does not change with different ANSI isolation levels. For example, there is no such thing as dirty reading in Oracle. Tom Keith points out that the spirit of allowing dirty reads is to avoid reading locks, which is not a problem in Oracle.

I would highly recommend reading Tom Keith’s wonderful book, Oracle Database Expert Database, which addresses these and other topics quite clearly.

Answer 2

Optimistic locking is basically "I will lock the data only when the data changes, not when reading." The end result is that if you don’t lock the data right away, someone else can change it before you do it, and you look at the old news (and you can blindly overwrite the changes that happened between you when you read the data and update them.)

Pessimistic locking blocks data when you read it, so you are sure that no one has changed it if you decide to update it.

This is a solution for the application, not an Oracle solution like:

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

will not block matching entries but

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

will be. Therefore, you need to decide whether you agree with the upbeat lock.

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 rewrite the change made by someone else in the meantime)

or should 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 are sure that no one has changed the data since you requested it.)

Check here 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 block the record when it is updated (and you can also press locks to delete and insert if there is a key involved). You can use SELECT .... FOR UPDATE to increase the pessimistic locking strategy.

Indeed, any database / storage engine that works transactionally should do some form of locking.

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

Because stateless federated / stateless applications are becoming widespread, especially in systems with heavy user activity, having a database session for an extended period of time can be a bad strategy. Optimistic locking is preferred, and later versions of Oracle support this with the ORA_ROWSCN and ROWDEPENDENCIES elements. Basically, they make viewing easier if the entry has been changed since you originally / for the last time looked at it.

Since this one-to-one relationship between the database session and the user session has become inherited, the application layer has retained more of the “user session” state and therefore has become more responsible for verifying that the user did five / ten minutes ago still valid (for example, the book is all still in stock or someone else buys it).