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

This question already has an answer here:

This is a interview problem.

There is a perl program that updates the database, and it could run in different processes. One process may execute a transaction like: update row A -> update row B -> commit. The other process may execute a transaction like: update row B -> update row A -> commit. The rows need to be updated is selected before or in the transaction.

I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).

They want me to propose at least 3 different methods. What I know is to use "select .. for update" when selecting row A and B. Can anyone help to suggest some other methods?

share|improve this question

migrated from Mar 4 '13 at 10:07

This question came from our site for professional and enthusiast programmers.

marked as duplicate by ypercubeᵀᴹ, dezso, Marian, JNK May 3 '13 at 12:26

This question was marked as an exact duplicate of an existing question.

This is a better fit for Database Administrators – Jim Garrison Mar 4 '13 at 5:44
I would suggest changing the transaction logic. – Petah Mar 4 '13 at 6:50

First of all

You have two transaction one is Insert and second is update if you avoid deadlock most sufficient way is threading

of there is any transaction with jdbc put in thread one

if any transaction come with jdbc put in second ,and it is wait until first is complete or realease resource.

there is so many transaction with jdbc put all in stack as per base on "first come first out" and handle all using wait and notify method to prevent deadlock in transaction.

share|improve this answer
sorry for incomplete introduction. The program is in perl and has nothing to do with jdbc. – Terry Mar 4 '13 at 6:42
sorry for answer in java – Rohit R.K. Mar 4 '13 at 8:14
@Rohit There is no reason to be sorry. Just delete the answer, if you think it's wrong or does not apply to the question. – ypercubeᵀᴹ May 3 '13 at 11:35

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