The fact that optimistic indexing and checkpoint indexing will partially reuse the same tables results in a few race conditions that we need to handle.

In particular the common tables are objects and display tables.

The possible failure scenarios are as follows:

• objects write - after writing results of optimistic transaction to objects table, it is possible that a checkpoint will arrive and be indexed that contains older version of given object, effectively overwriting more recent object data with older data.
• objects delete - similar to the case above, we may optimistically delete an object, and later on receive checkpoint with some older update of the object that happened before deletion, which will make us insert the object into the DB again. Or vice-versa, we could optimistically re-create an object (on unwrap operation) and later receive checkpoint that deletes this object (on previous wrap operation) which will make us delete an object that is currently existing
• display update - as above but with display table

The 'display' table can be protected by a simple version check in the SQL UPDATE statement.
For 'objects' table it is more complicated since we may remove rows from that table, which makes the simple check in SQL UPDATE statement insufficient (basically, we need the row present to be able to check object version).

Solutions:

One possible approach to fix this would be to allow removed objects to stay in 'objects' table with some flag set telling us that they are deleted.

Another solution would be to introduce transaction-level locking along with transaction dependency checks, meaning that:

• we will hold an execution lock when indexing given transaction, ensuring that only one process at a time is indexing it
• we will ensure that all transaction dependencies are indexed before the given transaction

Such approach should guarantee us that we will only increment object versions (and never write old data), and we will do it only once.

Conceptually it will looks as follows:

Optimistic Indexing Side (single TX being executed):

• Check if TX dependencies are satisfied, if not, wait for normal execution through checkpoint
• Create execution status row (if missing) for TX, and lock it FOR UPDATE, we are now the only writer for this TX
• Check if execution status is set to not executed, otherwise release lock and do nothing
• Index the TX
• Set execution status to executed, and release the lock

Checkpoint Indexing Side (multiple TXs being executed):

• Dependency check is skipped, not needed for checkpoint execution
• Create execution status rows (if missing) for all TXs, and lock them FOR UPDATE, we are now the only writer for those TXs
• Check execution statuses of all TXs, for all Txs that are already marked as executed remove objects written by them from objects batch that we are about to commit
• Commit batch to the DB
• Set execution status to executed for all TXs, and release the lock

Considerations:

• how to handle failures, especially in checkpoint indexing:
  • we are committing data in many DB transactions, one or more transactions per DB table
  • if some transaction fails, only that transaction is retried
  • transaction that holds execution lock is a separate transaction
  • what if there is a failure in transaction that holds the execution lock?
    • it may mean that we lost execution lock
    • seems that we should retry the whole batch from scratch, starting from acquiring the lock again
• will it be performant enough?
• how to handle transactions executed before indexing status was added?