Samuel,
> On 14 5 2015, at 2:12 am, Samuel Pelletier <sam...@samkar.com> wrote:
> I think your problem is with the locking. Optimistic locking does not lock
> anything it check on commit if things have changed.
Right; but does it potentially mess up PK generation? I thought it should not,
but of course, as so often, I can be wrong.
> I think that switching to pessimistic locking will help this situation
Originally, we have used pessimistic locking, but we have found it gets a bit
slow. Correct me please if I am overlooking something of importance, but far as
I know (and by my testing) it seems when pessimistic, even _reads_ are locked
out and wait till the first transaction is committed.
We sort of need that anybody can read, and that any locking and induced delays
happens only for those who edit, leaving readers unaffected.
> for a multiple instance setup
Actually, the problem did happen in single-instance, but with concurrent
requests on.
> the sequence will be locked for the remaining transaction time. This will
> prevent other instance to obtain primary keys during the remaining of the
> transaction but will keep your primary key generator safe.
Hmmm.... I wonder.
What if I generated all the PKs myself programmatically, using something like a
milli- or even microsecond timestamp?
That should make clashes possible, but extremely improbable. I might even
dedicate some bits to encode the thread number into the PK; in that case the
clashes should be nearly impossible.
For the extremely rare cases when they would happen, I suppose it should be
somewhat hairy: I would have to change the affected PK to the current
timestamp, and go through all the relationships to change the appropriate
FKs... ick, that could get ugly, but I suppose it should happen _extremely_
rarely?
(I know EOF can do better itself, but not with INTEGER PKs.)
That said, it might be better to let EOF do its best with optimistic locking,
and if the PK clash -- again very improbable, but possible -- happens, send to
the DB "SET UNIQUE FOR <offending table>(<its PK>)", and retry?
Thanks a big lot,
OC
>> Le 2015-05-13 à 13:05, OC <o...@ocs.cz> a écrit :
>>
>> Samuel,
>>
>> On 12. 5. 2015, at 23:49, Samuel Pelletier <sam...@samkar.com> wrote:
>>
>>> Sequence generation for concurrent access may be tricky to do right,
>>> especially if the system is tuned for performance. There is a confrontation
>>> between the sequence integrity and the concurrent access. It is easy to use
>>> a sequence table wrong...
>>
>> Definitely, and I am far from sure I am doing it right. Nevertheless it
>> seems to be reasonably well tested.
>>
>> Also, I do not use a separate sequence table; my approach is much simpler:
>> there is a sequential attribute guarded by a UNIQUE constraint, and the
>> saving code simply detects that this constraint failed, and if so,
>> increments the value of the attribute and tries again.
>>
>> That is far from efficient in case there is a lot of clashes, but they
>> happen to be reasonably rare; and it should be pretty fail-proof, or am I
>> overlooking something of importance?
>>
>>> OC, which database are you using
>>
>> FrontBase. Let me see the logs... at the server, there is 5.2.1g, a pretty
>> old one.
>>
>> Other sw versions: Groovy 2.3.8 / WebObjects 5.4.3 / ERExt's 6.1.3-SNAPSHOT
>> / Java 1.6.0_65 / Mac OS X 10.6.8.
>>
>>> with which connection settings for isolation and locking
>>
>> Read-committed, optimistic.
>>
>>> and how your primary key are generated ?
>>
>> Standard untouched EOF approach. All my PKs are INTEGERs.
>>
>> Thanks a lot,
>> OC
>>
>>>> Le 2015-05-12 à 17:09, Chuck Hill <ch...@gevityinc.com> a écrit :
>>>>
>>>> You really do come up with the absolute best problems! :-)
>>>> www.youtube.com/watch?v=otCpCn0l4Wo
>>>>
>>>> My guess is that somehow the database failed to record the update to the
>>>> sequence number. Every time you ran it after that, it generated the used
>>>> one and then failed. When you added logging, something that you added
>>>> caused two to get generated with the first not used. Then everything
>>>> worked again.
>>>>
>>>> Except… sequences should be generated outside of the ACID transition so I
>>>> can’t see how this could happen once, let alone multiple times.
>>>>
>>>> Chuck
>>>>
>>>> On 2015-05-12, 1:56 PM, "OC" wrote:
>>>>
>>>> Hello there,
>>>>
>>>> my application, among others, generates and stores audit records. The
>>>> appropriate code is comparatively straightforward; it boils down to
>>>> something like
>>>>
>>>> ===
>>>> ... ec might contain unsaved objects at this moment ...
>>>> DBAudit audit=new DBAudit()
>>>> ec.insertObject(audit)
>>>> audit.takeValuesFromDictionary(... couple of plain attributes ...)
>>>> for (;;) { // see below the specific situation which causes a retry
>>>> try {
>>>> ec.saveChanges()
>>>> } catch (exception) {
>>>> // EC might contain an object which needs a sequentially numbered
>>>> attribute
>>>> // it should be reliable through all instances
>>>> // there is a DB unique constraint to ensure that
>>>> // the constraint exception is detected and served essentially this way:
>>>> if (exceptionIsNotUniqueConstraint(exception)) throw exception
>>>> SomeClass
>>>> culprit=findTheObjectWhichCausedTheUniqueException(ec,exception)
>>>> culprit.theSequentialNumber++
>>>> // and try again...
>>>> }
>>>> }
>>>> ===
>>>>
>>>> It might be somewhat convoluted way to solve that (though I am afraid I
>>>> can't see any easier), but it worked for many months, about a zillion
>>>> times without the exception, sometimes with the exception and retry,
>>>> always without the slightest glitch.
>>>>
>>>> Then it so happened that
>>>>
>>>> - the EC indeed did contain an object with wrong (already occupied)
>>>> sequential number
>>>> - a DBAudit with PK=1015164 was inserted
>>>> - first time saveChanges did throw and the transaction was rolled back;
>>>> the second time (with incremented sequential number) it saved all right.
>>>>
>>>> So far so good, this did happen before often and never led to problems.
>>>>
>>>> This time though it did. The next time the above code was performed (no
>>>> sequentials, just the audit), the newly created audit was assigned _again_
>>>> PK=1015164! Of course it failed. Well, we thought, perhaps there's some
>>>> ugly mess inside the EO stack; let's restart the application!
>>>>
>>>> After restart, the very first time the above code was called -- which is
>>>> pretty soon -- it happened again: regardless there was properly saved row
>>>> with PK=1015164 in the DB, EOF again assigned the same PK to the newly
>>>> created EO. I've tried it about five times (at first I did not believe my
>>>> eyes), it behaved consistently: restart, first time a DBAudit is created,
>>>> it gets PK=1015164 and saving (naturally) fails.
>>>>
>>>> Then I've prepared a version with extended logs; for start, I've simply
>>>> added a log of audit.permanentGlobalID() just before saveChanges.
>>>>
>>>> It worked without a glitch, assigning (and logging) PK=1015165, and
>>>> (naturally) saving without a problem.
>>>>
>>>> I have immediately stopped the app, returned to the original version --
>>>> the one which used to consistently fail -- and from that moment on, it
>>>> worked all right too, assigning PK=1015166, and then PK=1015167, and so
>>>> forth, as it should. Without a need to log audit.permanentGlobalID() first.
>>>>
>>>> Well. Gremlins?
>>>>
>>>> Might perhaps anyone have the slightest glitch of an idea what the b. h.
>>>> might have been the culprit, and how to prevent the problem to occur again
>>>> in the future?
>>>>
>>>> Thanks a lot,
>>>> OC
>>>>
>>>>
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>>>> Help/Unsubscribe/Update your Subscription:
>>>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com
>>>>
>>>> This email sent to ch...@gevityinc.com
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>>>> Help/Unsubscribe/Update your Subscription:
>>>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>>>>
>>>> This email sent to sam...@samkar.com
>>>
>>
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
