pessimistic locking

Question

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: 
 
 In which scenarios does pessimistic locking have a significant performance impact? 
 When should pessimistic locking be used, and when should it be avoided? 
 If pessimistic locking is required, are there any design techniques or best practices to minimize its performance impact?

Mike Schmitt · Answer

This is entirely context dependent. What are you hoping to implement this on? 
 For instance, I've implemented this before to do database-backed faux-group-tasks - as in, when a user sees a "task list" that's really queried from a DB where you control who sees the "task names" via whatever databse-backed controls you devise, and clicking the link (instead of actually opening a running task) launches a process that then freshly invokes the actual task. After clicking, a quick DB query establishes that nobody else has opened it yet, and if not, a quick write is made to soft-assign it to that user. If someone else had opened it, the user is routed to a "fallback" task telling them someone has opened it already. 
 As long as this is well thought out, and conscientiously designed, the "performance impact" is essentially nothing. It works so quickly that users wouldn't notice any difference even under stress testing conditions, IIRC. 
 But if your use case is something far removed from this (which I have no way of knowing since you didn't really specify), then I can't really speak to it.

Mathieu Drouin · Answer

This article provides a good roundup of the trade offs as well as a App Market implementation 
 https://community.appian.com/success/w/article/3213/data-locking-strategies

Mike Schmitt · Answer

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. 
 
 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).