Andrus Adamchik wrote:
>Locking a row in the database ("pessimistic locking") for an extended
>period of time is a bad idea in most cases (especially in the web
>application). Good solution would've been to use "optimistic locking" -
>doing an update, including the value for the column marked as "locked" in
>the data map in the WHERE clause:
>
>update bids set highest_price = 101.00
>where highest_price = 100.00
>
>If update count is zero, it means that somebody else have already updated
>the price, so the application would throw an exception.
>
>Unfortunately Cayenne will probably support optimistic locking no earlier
>than 1.1 (unless there are volunteers to implement it).
>
Hi,
I might be a volunteer, cause I have to get the locking right
either on application or cayenne level - but I would
prefer to have it transparently in cayenne.
What I'm thinking of is close to the optimistic approach
you described - except for the fact that I want to do the
additional where-clause on a version/timestamp field
instead of the data-fields.
Let me just describe some thoughts/problems in the hope
that some of you already thought deeper about these
problems and can give me comments/hints to prevent
me from doing mistakes.
- version/timestamp
what I have in mind is really a mixture of a version and
a timestamp, which means, it's a timestamp in the first
place, but it's becoming a version number in case the
time difference drops below a millisecond or the database
is accessed by multiple systems with a clock-skew:
newTimeStamp = currentTime
if ( newTimeStamp <= oldTimeStamp )
newTimestamp = oldTimeStamp + 1ms
the reason is that this timestamp is an added value
that we need anyhow, while a plain version number
would be just useless
- optimistic locking versus "SELECT FOR UPDATE"
at least oracle has the pessimistic lock build in
as the "select for update" qualifier, so I have to
give good reasons not to use it, and use the optimistic
strategy.
One good reason you mentioned above:
it's that for "long" transactions (including user interaction)
the pessimistic lock is a real bad idea,
and then I can say: if I have an optimistic lock
for the long transactions anyhow, why not
use it for the short transactions as well.
I don't have enough experience to know
exacly how the "for update" lock behaves
on Oracle, but I have weak memories that
there are 2 possible modes to handle a conflict:
- either the the second connection blocks
on a "select" for a locked row until
the first connection is either committed
or rolled back
- or the second connection will get
an SQL-Exception
So I have 2 more arguments not to use
"for update":
- if even within Oracle the behaviour
is not well defined, it's close to
impossible that you can build a
database-independent solution
on that
- the blocking-mode is likely to
attract deadlocks
- handling of conflicts in the optimistic strategy
as you said, conflicts are detected by looking
at the update count. With the new context commit
these are (always?) batch updates, so it's not really
looking at update count 0, but looking at an update
count less then the number of updates in the batch
2 more thoughts on that:
- the update count missmatch is possibly not the only
way how an update conflict can express itself,
there's also the case that the problem shows
up not until the database commit
(or is there a way to configure oracle
to always get the conflict in the update count?)
from a users perspective it shouldn't make any
difference how the problem shows up
- but what would be important is to get the
information out what caused the conflict
in a batch update with an update count missmatch,
you don't know which row caused the trouble, but
you would like to know WHO updated WHEN
WHICH row to cause the conflict.
(The WHO is special for our application because
we put a timestamp+userid in the technical object header)
I've no clear idea how to do that, one possibility
would be to do the update again with single updates,
but more reasonable seems the other way round,
to run a query after the rollback to fetch
all the rows that are to be updated and run the
comparison in memory
Anyhow, this is just loud thinking, but your comments
would be appreciated
with best regards,
Arndt
This archive was generated by hypermail 2.0.0 : Sat May 24 2003 - 05:27:30 EDT