Hi Bill,
> Sorry Georg, I don't what planet I was on when I made the "option A with
> optimistic locking" comment.
Oh, might be, you had multiple instances with commit option A in
mind? Marc assumed that and seems to be about implementing that.
I have to rethink that scenario before commenting on it.
I assumed option A <=> single instance, because the EJB spec 1.1
section 9.1.11 seems to imply this, but then, they tell us:
'These strategies are illustrative, not prescriptive.'
> Sort of related, I did a simple test with Oracle 8.1.7 in 2 separate
> SQL*PLUS windows. With autocommit off, it seems that if you try to update
> the same row in 2 different windows, one window waits until the other
> commits.
Yes, this is expected behaviour with Oracle when both clients
actually try to update the same bean/row. With Oracle reading
rows without FOR UPDATE ever reads the last committed state
without doing ANY locking. When one TX updates, Oracle keeps a
modified row copy for that TX, others still may read without
locking still seeing the last commited state, but Oracle puts a
write lock on the row. When the second TX tries to update, even
with my suggested optimistic locking where clause, the update
hits that very same row (as the old value for TX2 hasn't changed
yet) and must wait for the write lock to be placed.
You even can get deadlock situations (I just tested it with
SQL*Plus) where Oracle immediately detects it and throws an error
to one of the offending TX (which is a good thing, so the TX at
least can rollback and let others do their work).
I said it before, my suggestion only 'mimickes' optimistic
behaviour, real optimistic locking must be done by the DB.
To clarify, when my suggested OL where clause is usefull and when not:
TX 1 TX 2
------------ ------------
read row A
update A
read row A <-- still gets old state
commit
update A <-- will fail with 'too late'
TX 1 TX 2
------------ ------------
read row A
read row A
update A
update A <-- will wait for lock
time passes .........
commit
^
+---------- will fail with 'too late'
TX 1 TX 2
------------ ------------
read row A
read row B
update A
update B
read row B <-----------------+- will get old state as seen
| by other TX before the update
read row A <-+
update A <---- will wait for lock
update B <-------------------- Oracle will immediately detect dead lock
and break it by SQL error: ORA-00060:
deadlock detected while waiting for
resource
to one of the TX
> Wonder if this will happen with JDBC as well, I'll let you
> know....
For sure!
> Anybody know the behaviour on this with other DBs? Is this common
> locking behaviour? If so, great!
There seem to be DBs out, that can do real optimistic locking, someone
mentioned that.
But for many others (i.e. Informix) it depends on the isolation
level, Marc hinted us already twice (please note, my Informix
knowledge is somewhat oldfashioned, I kicked it out years ago
for this (and other) stupid behaviour):
There are 4 Informix isolation levels:
low dirty read <=> ANSI read uncommitted
medium committed read <=> ANSI read committed
medium cursor stability
high repeatable read <=> ANSI repeatable read, ANSI serializable
With isolation level set to 'dirty read' Informix doesn't set
locks when reading, but it shows UNCOMMITTED changes done by
other TXs to you (phantom rows)!! It simply ignores locks at all.
This isolation level is useless for multi TX serious work
altogether.
'committed read' is documented as not setting locks when reading
rows and only retrieving committed rows. So this sounds to be the
right isolation to use for the OL approach.
But when I remember right, it actually BLOCKS reading rows that
are updated, but uncommitted by another TX. Some Informix guru
here to correct me? If I'm right, this still remains the level
to use, but with havy loaded Server/DB the concurrency will
drop to only let the updating TX proceed, blocking out most read
only access.
'cursor stability' is very similar to 'committed read' except
that it places a shared lock (preventing others from updating) on
the row under the cursor, not very usefull for our scenario,
where the JDBC driver processes the query completely or in pages
shifting that lock from row to row with <read-ahead>; with commit
option B/C and no read ahead this at best resembles to
pessimistic locking or is no difference to 'committed read'.
'repeatable read' places shared locks on ALL rows read,
preventing every modification to them. Lacks the same concurrency
problem when there are uncommited changed rows to read. For the
OL suggestion we won't want this isolation.
It remains, the CMP code must enable setting the isolation
level somehow to something other then the DB default.
> All in all, I think JBOSS should delegate synching and locking to the DB
> wherever it can because the DB is usually more efficient at this.
Not sure how efficiently the DB really does it, but i.e. with
Informix the DB locking is somewhat brain damaged (assuming I'm
right with the above).
> Also,
> IMHO, this is really the best way to synch multiple instances of JBoss.
Agreed, as long as JBoss doesn't do synching between several
instances (no current plans to do that, as far as I know).
> Regards,
> Bill
Sorry for being verbose again
regards
Georg
___ ___
| + | |__ Georg Rehfeld Woltmanstr. 12 20097 Hamburg
|_|_\ |___ [EMAIL PROTECTED] +49 (40) 23 53 27 10
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
