Pessimistic locking worst case

Question

We are using Java EE. And are making an apllication where in the worst case, aloot of message queue messages willl come form the same User.

Therefore we are looking on Pessimistic, SELECT FOR UPDATE style locking. Which in theory and fisrt tests solves our problems.

But we are afraid of deadlocks. Not the classical ones: User X locks A, User Y locks B.But more of scenarious like: system crash, netowrk problems,... and so on. Datbase system locking up, for no know reason. We will use modern databases like: oracle, MS SQL and postgresql.

What we would like to know is pessimistic locking used in production and what practical problmes to expect?

Thanks in advance!

Answer 1

The worst problem I've seen with pessimistic locking is the inevitable bottleneck that the database becomes.

In other words provided that your code is preventing deadlocks (as you say in your post) at the app level, performance becomes the problem - throughput drops as only 1 user at a time can perform an update on a given set of data.

It is possible that a locked table remains locked if a system exception occurs such that the select for update is executed but not committed, but by and large this isn't common (although this obviously depends on your code). I've not seen this happen through an infrastructure related issue though, only through application errors.

You can mitigate the throughput issue somewhat by batching updates if possible, but this really depends on your particular situation.