I realize just a little about Oracle obstructing - how updates block other updates up until the transaction completes, how authors don't block visitors etc.

I realize the idea of pessimistic and optimisic securing, and also the typical banking textbook good examples about losing lost updates etc.

I additionally comprehend the JDBC transaction isolation levels where we may say, for example, we're pleased with seeing uncommitted data.

I am a bit fuzzy however about how exactly these concepts are associated and interact. For example:

  • Is Oracle supplying pessimistic or positive securing automatically (it just appears to bar the seperate update according to experiments in 2 TOAD periods.)
  • If, when i suspect, they are application level concepts, why would I visit the trouble of applying a securing strategy after i allow the database synchronise transaction updates anyway?
  • How can transaction isolation levels (that we set around the connection) affect the database behavior when other clients besides my application be being able to access with various isolation levels.

Any words to explain these subjects could be really appreciated!

  • Oracle enables for either kind of securing - the way you construct your application dictates what's used. Looking back, it's not just a database decision.

  • Mostly, Oracle's securing is enough inside a stateful link with the database. In non-stateful applications, e.g., web applications, you can't utilize it. Make use of application level securing such situations because securing is applicable to some session.

  • Usually you don't have to be worried about it. In Oracle, visitors never block authors, and authors never block visitors. Oracle's behavior doesn't change using the various ANSI isolation levels. For instance, there's no such factor like a "dirty read" in Oracle. Tom Kyte highlights the spirit of permitting dirty reads would be to avoid obstructing reads, which isn't an problem in Oracle.

I'd highly recommend reading through Tom Kyte's excellent book "Expert Oracle Database Architecture", by which these along with other subjects are addressed quite clearly.

Positive securing is essentially "I'll only lock the information after i customize the data, not after i see clearlyInch. The gotcha is when you do not lock the information immediately, another person can alter it before you decide to do and you are searching at old news (and may blindly overwrite changes which have happened between while you're reading the information and up-to-date it.)

Pessimistic securing is securing the information while you're reading it to ensure that you'll make sure that nobody has transformed it should you choose choose to update it.

It is really an application decision, no Oracle decision as:

Choose x, y, z FROM table1 In which a = 2

won't lock the matching records but

Choose x, y, z FROM table1 In which a = 2 FOR UPDATE

will. So you've to determine if you are comfortable with positive securing

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

...the year progresses...

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

(you might have overwrote a big change another person made meanwhile)

or have to be pessimistic:

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

...the year progresses...

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

(you are certain nobody has transformed the information because you queried it.)

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