Indeed I have some thoughts on this. After my original post on
optimistic locking, I actually also came to a conclusion that
versioning must be addressed as well. Here is some high-level ideas.
1. Locking and Versioning
DbAttribute will have 2 extra properties: "usedForOptimisticLocking"
and "version". This would allow both locking and versioning
("versioned" attributes will be a subset of attributes used for
locking). Cayenne can automatically perform versioning on timestamp and
numeric attributes. To update fields like "user id", I guess you can
use DataContext events, implementing logic outside of Cayenne.
2. Optimistic Lock Failures.
Like you said it is very important to be able to know the details of
the lock failure. I suggest the following approach:
a. Each update/delete statement will contain both PK and Lock columns
in its WHERE clause.
b. If update count is zero (lock failure), Cayenne does a rollback
and throws an OptimisticLockException (maybe a subclass of
CayenneRuntimeException)
c. OptimisticLockException will contain the information about the
failed row (a snapshot from the batch query row being executed)
d. To analyze who, when, and how, we may introduce a helper class (or
a method on OptimisticLockException) that will do a select from the
database for the failed row PK, returning a snapshot of the current DB
row state. If no snapshot is returned, this is an indication that the
row was deleted, otherwise a row was updated. Such method may be
explicitly invoked by the code that caught the original exception, to
build an intelligent error message
d.1 An alternative to d. would be to get all the info about failed row
right on the spot, storing it in OptimisticLockException, and then pass
the exception to OperationObserver delegate, asking it how to proceed,
with options being - rollback and throw, skip update on the row,
attempt to merge, overwrite the row, etc..(?)
> 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
No, you can do it in the batch:
http://java.sun.com/j2se/1.4.1/docs/api/java/sql/
Statement.html#executeBatch()
executeBatch returns an int[] that can be analyzed.
> - 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
I kind of like that :-)
Andrus
On Saturday, May 24, 2003, at 05:27 AM, Arndt Brenschede wrote:
> 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 - 14:15:54 EDT