Haha, nope we are definitely not following a smart path with myIsaam and
sequence tables in general. I think it came about to support multiple
environments and avoiding clashing ids when replicating between them. The
plan is to be off of it by the end of the month, but for the sake of
supporting it now...
Looks like I may want to go with an ExecuteListener then, and check for
inserts with a null primary key value.
I think I more meant to say, concurrency safety on the db side vs thread
safety. i.e. if I read the current sequence value, then update it, but
another application read it in between reading and updating, I'm in
trouble. I can't SELECT FOR UDPATE because it's not innoDB and there are
no transactions. Seems that there has to be a way since JPA supports it on
myisaam and we haven't seen any issues. This seems to be less of a jooq
question though and more just mysql, but throwing it out there in case you
had an idea.
On Wednesday, October 5, 2016 at 12:06:28 PM UTC-4, Lukas Eder wrote:
>
> Hi,
>
> That's an interesting approach. I'd worry very much about data integrity
> and race conditions in your case. Are you sure that's the best way to
> generate IDs in your system? I'd say a UUID based approach might be safer
> (and easier)...
>
> A RecordListener is invoked only when you use any of (Updatable)Record's
> insert(), update(), store(), refresh(), delete() methods. It is not
> invoked, for instance, when you run ordinary INSERT statements. Just to
> make sure you're aware of that.
>
> The insertStart() method receives a RecordContext argument, which is a
> Scope, which contains a Configuration. From that, you can access all of
> your SPIs (such as ConnectionProvider), or simply wrap that Configuration
> in a new DSLContext using:
>
> @Override
> public void insertStart(RecordContext ctx) {
> DSL.using(ctx.configuration()).execute("...");
> }
>
>
> This approach is thread safe, after all, you're in the same thread as the
> Record.insert() operation. But it would also be possible to design your
> SPIs in a thread safe way, otherwise.
>
> But again, I'm not quite convinced that you're following a good path here,
> specifically on MyISAM
> Hope this helps,
> Lukas
>
> 2016-10-05 16:01 GMT+02:00 <[email protected] <javascript:>>:
>
>> Our legacy mysql database uses a table to store the next id for each
>> table. We have been using spring JPA's TableGenerator strategy in the
>> past, and would like to figure out how to use jooq with this. It looks
>> like RecordListeners are the way to go, but how would I actually execute
>> queries from within an insertStart method. There is no dsl context
>> available, and also, I'm not sure how to ensure that it is thread safe. I
>> was thinking using select for update would be a good way to go, but we are
>> using myisaam (at least for the next few weeks until the upgrade *crossed
>> fingers*) which doesn't support transactions. Makes me curious how
>> hibernate does it without id collisions.
>>
>> I guess, it would be really handy to see a code example of how to support
>> table based id generation. Looking forward to getting on auto increment
>> soon, but for now, and for the sake of having a reference on the internet...
>>
>> Thanks a ton.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
