Transaction - Smart management of concurrency

[stombre]

In the current implementation of Transaction for knex, we use the forUpdate operator, that way the table "is lock" and the execution is "protected", the problem of this approach is a performance related, if we do a lot of change per second, the full lock is a bit "big".

There is another way to proceed; Exception & replay

We want to decrease amount of money on an "account".

The business logic summary is we load the account, and we update his value. (Ofc in this case, it's not require to load the account, but it's to make a simple example).

Currently, when we load the account, the table is lock, if another call do the same, it will be blocked, and he will wait for the account to finish;

In the "smart" implementation, we will load the account and track which field we update, and compare to the initial value.

For example; if you have 200€, and two call in concurrence remove both 40€. We load the account at the same time, we know the initial value is 200€, we add this in the where clause; The first one, will apply a ; update 160 if value is 200 The second one, will apply a update 160 if value is 200 (this one will update 0 row, because the value is already changed).

In this case, we will throw an exception if no row is updated, the exception (a TransactionConcurrencyError, will re-trigger the Transaction.run, to rollback the transaction and start again). That way, he will reload the account, this time the amount is 160, and apply the update; update to 120 if value is 160.

The cool thing; the table is never locked for one operation.

Because we know initial state of instance and which field are edited, we could definitely track if instance state evolve, and manage to use less the forUpdate.

This system will be hidden behind a flag, and it will be up to the user to proceed this way or with a forUpdate. This behavior could potentially not working depending of the user code, and have his own defaults.

• [ ] Add TransactonConcurrencyError (ilorm specific error), expose it publicly
• [ ] If TransactionConcurrencyError are catch by Transaction.run rollback the transaction, and make it run again.
• [ ] Add a flag in Transaction.run to disable forUpdate in the whole Transaction and make it work with TransactionConcurrencyErorr instead
• [ ] Add a amountOfRetryConcurrency flag in Transaction.run (set by default to infinite), amount of time we retry in case of a TransactionConcurrencyError