You should not have any data loss.

The sequence is: Optimistic lock, several operations forming one transaction, unlock, a message that the transaction wasnt finished. Which means none of your operations is in the database.

At that point you should still know the purpose of each operation, figure out if these operations are still possible, and start all over again. Repeat until the transaction goes through or your checks show it cannot be performed (for example if you have six tyres two people can order four tyres, one transaction fails, and then you have only two tyres and nobody can buy four).

Assuming the transaction started with some user interface you may want to re-display the user interface.

This is a scheduling problem.

What each of your nodes wants to do is exclusively reserve an object without permanently locking the object, nor preventing it from participating in reads while some data-manipulation is being prepared behind the scenes.

The solution is a queue, but we don't have to off-shore the data-manipulation.

Make two tables. One is going to be the queue table and the other is going to be an ownership table.

The queue table is going to contain a task id, any task details, a node id, and the id of the object graph (top rows id?).

The ownership table is going to be a task id, the object graph id, a timestamp for expected task end.

There will need to be a constraint that a task is unique in the ownership table.

each node is going to write its own task into the task table. Depending on how the node architects it on that thread, or in a seperate thread pool we are going to poll for a task. The polling is going to start a transaction in tsql:

1. it select uncommitted from the ownership table

• this picks up even in process transaction locks reducing contention
• you'll have to ignore records whose timestamp is too old which removes perma locking from a node failure.

2. it selects with skipping locked pages from the task table so that we see all tasks without contention

• the ownership select is used to exclude owned tasks, or tasks that share a object graph with one.
• we will also filter out any task that can't be run on this node

3. we pick one of the tasks

• we check the ownership table and delete any stale record for it
• we insert a fresh record into the ownership table claiming the task and object graph.

Even though the transaction technically locks data in the database by doing it this way the only contestable lock is adding a new row to the ownership table that might be rejected if another poller is trying to pick up the same task (by a constraint between the tables), or by deleting a stale record (which should be uncontested as its very stale).

When we added the ownership record we set a timestamp that operates as a sunset on our right to modify. We should make sure that we don't take longer than this to process (or periodically update it when processing - like a heart beat).

At this point we own the task, and by extension the right to modify the data (for an amount of time). We process and update the graph. Once done we mark the task as complete (or delete it from the table) and delete the ownership (or update it to a new task).

If the task fails due to an optimistic lock issue, or something else it is fine. The task record still exists, it will become available once the ownership expires for reprocessing. So will the object graph we have locked will become available for updates. You might want to add some logging/optics to this so you can address repetitive failures, or if an object is receiving way more action than expected.