to implement the pessimistic locking how to identify/know the data base table is locked or not?
Discussion posts and replies are publicly visible
What do you want to achieve? Protecting a record from multiple executions of related actions?
Yes! This makes me realize exactly how to phrase what I intend to phrase. Thank you, Stefan.
Every single implementation of pessimistic locking has to be unique to the particulars of the thing you're trying to pessimistically lock, and both what and who you're trying to lock it from. They all have to be different based on your specific goals.
My solution: A. Is protected by a Non Disclosure Agreement B. Is too highly technical for me to go into absolutely all the particulars here if I could C. Even if I could remember all of them precisely D. Would therefore be more difficult than making your own even if I could tell you every detail, and finally E. Wouldn't even work for your situation if you could copy it exactly.
Appian is pretty good in keeping track of process activity. Using a process report, you can easily detect any other active instances. Display that to the user and disable the submit button.
Then you do not need to implement any locking in DB which has its drawbacks as check-lock and set-lock is not a single atomic transaction. Two processes checking at the same time will collide.
This is how I originally implemented pessimistic locking and for the most part this works...except...there is a small window of opportunity where two users can run the same related action because they hit it at the same time and the data hasn't yet been replicated/stored where the process report accesses it. In short: it's not a fool-proof method.
One of my colleagues has written a utility that closes this gap and delivers a robust pessimistic locking solution.