Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
"Datbase system locking up, for no know reason.". You want an answer with a list of unknown unknowns? – Phil Mar 21 '13 at 10:14
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.