We are using Java EE and are making an application where in the worst case, a lot of message queue messages will come form the same User.
Therefore, we are looking on Pessimistic, SELECT FOR UPDATE style locking. Which in theory and first tests solves our problems.
Yet, we are afraid of deadlocks. Not the classical ones: User X locks A, User Y locks But more of scenarios like: system crash, network problems,… and so on. Database 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 problems to expect?
Thanks in advance!
Best Answer
Pessimistic locking is a good thing used well.
The big thing though is that your issues are all going to be concurrency issues and things which interfere with concurrency will interfere with performance. For example if your network switch starts dropping 10% of packets randomly, this will make a network connection take a lot longer, and your locks are going to suffer. These will not be deadlocks per se and will only make a problem that would be painful under the best of scenarios a good deal worse.
System crashes should not cause deadlocks. If it is the client, the network connection will time out so you may have a sudden issue with locks that goes away right away. If the client is middleware however, this is likely to be a non-issue because all the other connections will be broken too. If it is the server, the transactions will abort.
The big issues are developer short-sightedness. For example, suppose you write a stored procedure that calls a web service and the network connections there are timing out. Or suppose you make a connection back to the client computer to request input on whether to continue a stored procedure and the user never responds. In general when you pause a transaction to wait for something outside the database, bad things happen.