Queuing - Changing Order of Execution

[matus_b]

Hi,

we have editable ListGrid which sends updates via saveAllEdits().
We can see that DSRequests are ordered in the queue in respect to sort order of ListGrid records.

Recently we faced following concurrency issue:
User A sends queue with update request from ListGrid sorted ascendantly.
User B sends queue with update request from ListGrid sorted descendantly.

Queue updates are encapsulated in one transaction (business requirement).
Transaction A is updating records from 1 to N, transaction B is updating records from N to 1.
At some point they are going to bump against each others DB locks resulting in deadlock.

We consider to solve this issue by applying rule of Ordered locks.
Is it possible, before execution, to reorder DSRequests in the queue on the server side by comparing value of the primaryKey field?

Regards, Matus

[Isomorphic]

This kind of situation is very, very ordinary in web applications, so if it deadlocks your DB, you should be looking hard at your database configuration. It sounds like you may have turned-on row-level locking, which has dozens of similar deadlock situations.

No, there is no way to reorder a queue server-side - an API like that would present dozens of issues with respect to transaction boundaries, resource freeing, the Transaction Chaining system, differing order of client vs server execution (including error handling implications), and several other issues making it infeasible.

However, you can of course affect the order of submission client-side. So if you can't solve this at the DB level, you could do something like put the grid into a reference sort order before calling saveAllEdits(), then restore the original sort order after the save. Or, you could extract the edits, save them in whatever order you like, then clear the pending edits from the grid after the successful save.

Finally, you might be able to solve this on the server-side by acquiring locks to all the rows that you will be updating *up front* so that no other request thread ends up with locks to some of those rows.

[matus_b]

Thank you, I have found your answer helpful.

I was thinking about what you suggested last - acquiring locks to all rows *up front*.

Does the framework offer some support for pessimistic locking?
I imagine some setting on the OperationBinding or DSRequest, which will cause to generate SELECT...FOR UPDATE query for the SQL DataSource.
And maybe some special Response Status if the fetch for update can't be executed due to lock's already being acquired by other user.

Regards, Matus

[Isomorphic]

There isn't built-in support for this, but if you wanted to do this, you could execute it yourself as part of the first DSRequest, using the approach shown in the "Transaction User Operations" sample.