DataStoreSequencer and db isolation

Home Forums kbmMW DataStoreSequencer and db isolation

This topic contains 2 replies, has 2 voices, and was last updated by  mrluigi2017 8 months, 1 week ago.

  • Author
    Posts
  • #53424

    mrluigi2017
    Participant

    Hi,

    I have an issue with the datastore sequencer. It seems that if  kbmMWDataStoreSequencer.Value[‘some_seq’] is called from multiple threads the value of some_seq is not guaranteed increased after calling kbmMWDataStoreSequencer.Value. This makes sense because my database (postgres) has a standard isolation level of read-commit (which I think is good). The result is that I get duplicate primary keys, which is not good 🙁

    Am I doing something wrong here?

    Thanks in advance!

  • #53427

    kimbomadsen
    Keymaster

    I suppose you are using the generic SQL metadata. It basically translates the get value to an SQL UPDATE followed by a SELECT. For this to work, its run within a transaction, but it require that the transaction mode of the database actually blocks others completely, otherwise you will not get correct unique values.

    PostgreSQL supports a special syntax UPDATE… RETURNING …. which I suppose would be a much better solution, but require a PostgreSQL specific Metadata component.
    It can be done fairly easily by descending from TkbmMWCustomSQLMetaData or TkbmMWGenericSQLMetaData, and overwrite GenerateValueSequenceSQL method.

    Or, probably even better, but not directly backwards compatible with your existing database since it currently use a pivot table, is to create a PostgreSQL Metadata component, which use the sequencer features built into PostgreSQL. You could look at kbmMWOracleMetaData.pas to see how that is done. I may have a go at it at some point, but Im pretty busy at the moment.

  • #53429

    mrluigi2017
    Participant

    How about ORM and Postgres? Will it work if I create a generator with a sequence or will the problem be the same behind the scenes?

You must be logged in to reply to this topic.