Chuck, I used FrontBase Manager with 2 connections with these setting and discrete commits. set transaction isolation level read committed, locking optimistic, read write;
I tried different rollback scenarios but the sequence was always correct. I used FB 5.2.14 on OS X with a database created on the same version. Samuel > Le 2015-05-14 à 16:21, Chuck Hill <ch...@gevityinc.com> a écrit : > > Hi Samuel, > > What did you do to test FrontBase? I tried this in FrontBaseManager with > “discrete commit” and rolling back the transaction, caused the sequence > numbers generated to be generated again in the next transaction. If you > commit (or auto commit) then it behaves as you describe. It also does that > for EOF which does a commit after the “SELECT UNIQUE FROM <table> > > > Chuck > > > On 2015-05-14, 5:30 AM, "Samuel Pelletier" wrote: > > In FB, they used to be inside the transaction (If I remember correctly) and > with Read Committed locking optimistic, the server to return the same value > to both connections if the second select overlaps the first. > > I just tested with my local FB 5.2.14 and it behave like oracle, the current > transaction state or setting does not affect the unique sequence, it always > increments and return the next value. > > OC, I suggest you upgrade your FB version, it is very easy, just update the > binaries, no need to migrate the data. > > Samuel > >> Le 2015-05-13 à 20:22, Chuck Hill <ch...@gevityinc.com >> <mailto:ch...@gevityinc.com>> a écrit : >> >> It depends on the database. The Oracle sequence generation is outside of >> the ACID transaction and is not affected by transactions or commits. Once >> Oracle has returned a number from a sequence it won’t do so again* >> regardless of any transactions getting rolled back or committed. >> >> * assuming that the sequence is not configured to CYCLE. >> >> FrontBase will “return” the sequence number if the transaction is rolled >> back, but I am pretty sure that EOF does a commit immediately after >> selecting for a PK. >> >> It is possible that somehow the commit after the PK select failed and the >> exception got eaten, I suppose. That seems a bit far fetched. >> >> Chuck >> >> On 2015-05-13, 5:12 PM, "Samuel Pelletier" wrote: >> >> OC, >> >> I think your problem is with the locking. Optimistic locking does not lock >> anything it check on commit if things have changed. >> >> I think that switching to pessimistic locking will help this situation for a >> multiple instance setup, 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. >> >> This apply to all database to my knowledge, I just googgled and ir seems >> Oracle behave the same way. >> >> Samuel >> >> >> Le 2015-05-13 à 13:05, OC <o...@ocs.cz <mailto:o...@ocs.cz>> a écrit : >> Samuel, >> On 12. 5. 2015, at 23:49, Samuel Pelletier <sam...@samkar.com >> <mailto: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 >> <mailto:ch...@gevityinc.com>> a écrit : >> You really do come up with the absolute best problems! :-) >> www.youtube.com/watch?v=otCpCn0l4Wo >> <http://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 >> <mailto:Webobjects-dev@lists.apple.com>) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com >> <https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com> >> This email sent to ch...@gevityinc.com <mailto:ch...@gevityinc.com> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com >> <mailto:Webobjects-dev@lists.apple.com>) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com >> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com> >> This email sent to sam...@samkar.com <mailto: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
