Transaction timeline / delay for curation

During the configuration of the MES database, the MES production database is set to Read Committed Snapshot. This ensures that the MES Curator service will only read committed records from the MES production database. However, there might be transactions in process that have not yet been committed; therefore, the MES Curator applies an additional delay when curating data from the MES production database. The TimeDelayForCuration setting in the appsettings.json file defines this delay. It has a default value of 4 minutes. The MES Curator service adds an additional 1 minute to the setting for a total default delay of 5 minutes. A transaction running for 5 minutes may insert, update, or delete several records during the transaction time, but those data changes are not visible to other transactions until the transaction that started those changes is committed at the end.

Notes:
- The TimeDelayForCuration setting with additional 1 minute on top of it is always applied during curation whether it follows the configured frequency cycle or when the on-demand curation is turned on (staging_curator_status.curate_reference_data = true).
- The timeline and transactions described below are just examples for the reader to understand the flow of curation, and not necessarily the typical duration of MES production transaction updates to the records in the database. The total transaction time is 5 minutes in this example, assuming a slow MES production database.

For example, a transaction in the MES production database happens as follows:

• 6:00:00 – The transaction starts (TransactionID:1) on the MES production database.

• 6:00:30 – Update item_prod record. The last_edit_at is updated with 6:00:30.

• 6:02:00 – Insert job_event record. The last_edit_at is updated with 6:02:00.

• 6:03:30 – Update item_inv record. The last_edit_at is updated with 6:03:30.

• 6:04:25 – Insert item_cons record. The last_edit_at is updated with 6:04:25.

• 6:04:30 – The transaction commits.

  Notes:
  - 6:04:30 is when the inserted / updated records from TransactionId:1 will be visible to other transactions, even though they were updated before this time. That is, all the changed data can be queried only after 6:04:30.
  - Every time the curator scans for updates, it only scans for the specific type that gets changed in that frequency cycle even though there could be updates to other tables on that transaction. In the above example, when scanning for item production changes, the curator only scans for changes in the production records and ignores the updates on other tables like job_event, item_inv, or item_cons that are not part of the curator schema. These tables are included in this example for a better understanding of the functionality.

MES Curation for production datatype scanning

The following table describes the scanning behavior of the curator for production datatype using a 5 minute transaction buffer in the production database.

In the example above, a production transaction updated at 06:00:30 was curated at 06:07 in the staging table, with a delay of 6:30s (6 minutes and 30 seconds) from the production update to curated data in the staging table.

Note: This record was curated 2 minutes and 30 seconds from when it was committed.

If the production transaction happened at 5:57:00 precisely, then the production record would have curated at 6:07 in the staging table, with a delay of 10 minutes from production update to curated data in staging table.