Page 1 of 1

Debea SQLIdFetcher and concurrent DB INSERTs

Posted: Mon Apr 08, 2013 11:17 am
by maxbld
Dear all,

The more I deepen the knowledge of debea library, the more I like it. Now I was considering the new records insertion topic and I found debea proposes a generic implementation of the SQLIdFetcher base class which requires a dedicated table to store the actual ID count reached by the whole DB. As you may have seen, the approach here is to count all insertions, unrelated from the single tables, and thus provide unique IDs for inserted records. Nothing bad in that and if the programmer wants something different, he's free to make his own implementation of SQLIdFetcher and have it working as he pleases.

But I was wondering what happens if you use this approach with transactions (managed in the dba::Transaction class): are we sure things don't get messed? I.e. is the library able not to generate duplicates when many transactions are open? I mean any SQL insert would have to read the actual count and generate one SQL update to add 1 to counting table. This process makes me feel uneasy, are we sure any insert would read the right ID? And what about rollbacks? Eventually in production, under heavy load, having many customers inserting records massively in one single master DB serving let's say one hundred concurrent users...

Is there anybody in this forum who experienced debea behavior let's say on MSW and connected with MySQL through ODBC under such load? Wouldn't it be better to rearrange the SQLOStream::store method so that it relies on the autoincrement property offered in this case by MySQL?

Of course I understand this topic is heavily DB engine dependent, so if someone has experience on how MySQL deals with concurrent inserts, even without debea involvment, his advice would be precious to me.

Thanks a lot for any comment, BR,
Max.