How to avoid database deadlocks

views:

321

answers:

+2 Q:

How to avoid database deadlocks

I have a clustered application that is suffering from database deadlocks.

It is a j2ee application using JPA and hibernate. The database is DB2 8.1 on Z/OS is set to page locking (this is a requirement for the company).

The problem is that the primary key are generated as a sequence, and will often deadlock while trying to insert records if the system has any significant load.

Are there any "best practices" to reducing the probability of a deadlock?

+2 A:

here ya go, hope it helps:

http://www.digipedia.pl/man/doc/view/db4.4_deadlock.1.html

http://www.devx.com/gethelpon/10MinuteSolution/16488

James Campbell 2010-01-29 17:50:40

+1 I think the key here is to understand why you run into the deadlock problem. When you understand the the issue, you can start solving the issue. Solutions are that only one process is allowed to write data in the database. or as S.Lott suggested, always lock your resources in a certain order. Something that definitely helps is to lock only when necessary and only as long as necessary.

Peter Schuetze 2010-01-29 19:04:44

+3 A:

"Are there any "best practices" to reducing the probability of a deadlock?"

Deadlock means 2 (or more) processes, 2 (or more) resources and two distinct orders for access.

Process 1 has to get A and B.

Process 2 has to get B and be waiting for A.

If every process would get A first, the incidence of deadlock is reduced.

Since you're deadlocking on an entire page, it's hard to assure that everyone gets the same page to start their transaction.

You can try to reduce conflicts by assuring that rows are widely scrambled among the pages.

You can try to reduce conflicts by introducing a "You Must Get This First" row which will effectively single-thread applications.

S.Lott 2010-01-29 18:12:34

Is there a way to ensure that the rows will be distributed? I'm not sure how the database determines what page to stick the row in (I'm assuming it is based on the key). Would adding another field to the key that is a random number (or UUID) help to distribute the rows?

Jesse 2010-01-29 20:19:33

@Jesse: UUID's are probably random enough. You have random number generators that are pretty random. You can also index by some attribute that's good a distribution different from the one causing the collision. ZIP code, for example.

S.Lott 2010-01-29 20:29:05

ansaurus

tags:

views:

answers:

How to avoid database deadlocks

related questions