I am considering implementing pessimistic locking in Appian. However, I have heard that pessimistic locking can potentially degrade performance because it restricts concurrency.
In practice, how much impact does pessimistic locking typically have on performance? Also, from a best practice perspective in Appian, what kind of design or implementation approaches should be considered?
Specifically, I would appreciate guidance on topics such as:
Discussion posts and replies are publicly visible
Sorry for the delayed follow-up, and thank you very much for your responses.
Thank you for the detailed explanations so far — they are very helpful.
Let me add more context about my actual use case, as it seems quite relevant to the DB-backed task example you mentioned.
In our business scenario, multiple users work together to review and correct already-registered voucher (transaction) records in Appian.The workflow is not task-based; instead, users pick records from a shared pool and work on them one by one.
The intended behavior is:- When a user starts working on a voucher, its status is updated to “In Progress”.- The voucher that was previously being handled by the user is set back to “Accepted” (or a completed state).- We need to ensure that the same voucher cannot be picked by multiple users at the same time.
In a traditional database design, this would typically be handled using something like `SELECT FOR UPDATE` to atomically claim the next voucher.However, as far as I understand, Appian does not provide a direct equivalent of `SELECT FOR UPDATE`, which means multiple users may end up selecting the same “next” voucher concurrently.
Because of this limitation, we are considering introducing a dedicated object (or table) to manage voucher assignment and status, and using pessimistic locking to ensure exclusive ownership while a voucher is being edited.
My concern is scale: in peak periods, several thousand users may perform this operation at roughly the same time.That raises the question of whether pessimistic locking on such an object could become a bottleneck or cause noticeable performance degradation.
Given this context, I would appreciate guidance on:- Whether this type of “first-claim-wins” record assignment is a reasonable use case for pessimistic locking in Appian- Any design patterns or architectural techniques to minimize contention and performance impact (for example, batching, sharding, or alternative locking approaches)- Or whether there is a more Appian-native pattern that would be preferable for this kind of scenario
Any insights would be greatly appreciated.
MM said:Appian does not provide a direct equivalent of `SELECT FOR UPDATE`, which means multiple users may end up selecting the same “next” voucher concurrently.
If you implement a process that quickly (in successive nodes) does this check-then-write, structured like I described in my prior comment, then users will not be able to do this. Not accidentally, and not even if they're testing to try to break the system you have set up.
MM said:whether pessimistic locking on such an object could become a bottleneck or cause noticeable performance degradation.
whether pessimistic locking on such an object could become a bottleneck or cause noticeable performance degradation.
If your system can already handle several thousand users working concurrently, a system like the one I described should make little to no performance difference.
MM said:Any insights would be greatly appreciated
Just design something and test it. Keep the lead-up sequence tight, and pay attention to the ordering of the nodes so as to handle the different use cases (e.g. when two users both click the same task within a few seconds of each other from their task lists).