Take the 2-minute tour ×
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. It's 100% free, no registration required.

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!

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

1 Answer

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
add comment

Your Answer

 
discard

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.