Proper design to avoid Oracle deadlocks?

Question

The usual advice when it comes to avoiding deadlocks is to always block resources in the same order. But how would you apply this to row locks in a highly documented Oracle database?

To understand what I mean, consider the following example. A very simple DAO to process bank accounts:

@Component public class AccountDao { @Resource private DataSource dataSource; public void withdraw(String account, int amount) { modifyBalance(account, -amount); } public void deposit(String account, int amount) { modifyBalance(account, amount); } private void modifyBalance(String account, int amount) { try { Connection connection = DataSourceUtils.getConnection(dataSource); PreparedStatement statement = connection .prepareStatement("update account set balance = balance + ? where holder = ?"); statement.setInt(1, amount); statement.setString(2, account); statement.execute(); } catch (SQLException e) { throw new RuntimeException(e); } } } 

To transfer between two accounts, there is an InternalBankTransfer class that has a transfer method:

  public void transfer(String from, String to, int amount) { // start transaction accountDao.withDraw(from, amount); accountDao.deposit(to, amount); // commit transaction } 

This usually works fine. But let me say that we have two people who simultaneously initiate transfers. Let's say Ann wants to give 100 bucks to Bob at the same time that Bob wants to give 50 Ann. So, in one thread, Anna calls transfer("Anne", "Bob", 100) , and in another Bob calls transfer("Bob", "Anne", 50) . This code is susceptible to dead locks if the execution order is as follows:

 T1: accountDao.withDraw("Anne", 100); T2: accountDao.withDraw("Bob", 50); T1: accountDao.deposit("Bob", 100); T2: accountDao.deposit("Anne", 50); // BAM! ORA-00060: deadlock detected while waiting for resource 

I admit that I did not consider this at all before I began to see dead locks in a real application. My naive point of view was that the difference in transactions took care of this automatically. Oracle says this is due to poor application design. But what's good in this case? Do I need select for update all that I plan to update? What if this is a huge transaction with updating multiple tables? Should I design so that deadlocks are impossible or just minimize them and accept that they are a fact of life?

Answer 1

I think this is a fact of life (and one that really only needs to happen with high concurrency and hotspot data).

If you want to implement blocking, then yes, you will need to rewrite your code to block or update accounts in a predetermined order (first Anne, then Bob). But this is not possible for complex transactions. If this happens with only a few hotspot points, you can probably use the lock for the same ones (and leave the rest as they are) and go through with it.

Or use less granular locks, but that will kill your concurrency.

In your case, you can simply retry the canceled transaction. And if this happens too often, it looks like you have problems designing your application.

Here is a link for a two-phase commit protocol for bank transfers. This is from the MongoDB wiki, that is, from people who don’t even have the luxury of blocking rows and transactions in the first place, but they can also be implemented on an RDBMS to avoid blocking. Of course, this will be a rather radical redesign. First I'll try the rest (retries, gross locks, artificially reduced concurrency, batch processing).

Answer 2

There are several design issues above.

Even if you ask about dead ends, I feel the need to write also about other problems that are wrong IMHO, and they can save you from some trouble in the future.

In your design, the first problem that I see is the separation of methods: in order to make changes to the balance, you have a way to withdraw money and a method of making a deposit. In each case, you use a call to the same "modifyBalance" method to execute the action. and there are several problems in how this is done:

1- the modifyBalance method requests a connection each time it is called 2 - the connection will most likely turn on the automatic commit mode, since you did not disable the automatic commit.

why is this problematic? the logic you do must be one unit. suppose you take 50 off Bob and it succeeds. you have an automatic commit, and the changes are final. Now you are trying to make a deposit in Ann, and he fails. according to the code above, Ann won’t get 50, but Bob has already lost them !!! therefore, in this case, you need to call up the deposit for the bean again and return it to 50, hoping that this will not fail or else ... endless processing. therefore, these actions must be in the same transaction. either the withdrawal or the deposit succeeds, and they are completed, or they fail, and everything is rolled back.

this is also problematic, because in the automatic commit mode, the commit occurs after the completion of the instruction or the next execution. if for some reason the commit did not happen, then since you are not closing the connection (and this is another problem, since it does not return to the pool), and no commit can lead to a deadlock if another update is released on the line blocked in first transaction.

therefore, I suggest you do the following: either request a connection in your transfer method, or combine the output and deposit methods in the method to change the balance yourself.

Since it seems to me that you liked the idea that I have two methods, I will use the demo version of the first option, which I mentioned :)

 public class AccountDao { @Resource private DataSource dataSource; public void withdraw(String account, int amount,Connection connection) throws SQLException{ modifyBalance(account, -amount); } public void deposit(String account, int amount,Connection connection) throws SQLException{ modifyBalance(account, amount); } private void modifyBalance(String account, int amount,Connection connection) throws SQLException { PreparedStatement statement = connection.prepareStatement("update account set balance = balance + ? where holder = ?"); statement.setInt(1, amount); statement.setString(2, account); statement.execute(); } } 

and the transmission method will be:

 public void transfer(String from, String to, int amount) { try { Connection connection = DataSourceUtils.getConnection(dataSource); connection.setAutoCommit(false); accountDao.withDraw(from, amount,connection); accountDao.deposit(to, amount,connection); } catch (SQLException e) { if (connection!=null) connection.rollback(); throw new RuntimeException(e); } finally { if (connection!=null){ connection.commit(); connection.close(); } } } 

Now, either both actions will succeed, or both will return back. In addition, when an update is issued in a row, other transactions trying to update the row will wait for completion before they can continue. rollback or commit releases the row level lock.

Now the above explanation of the best design to keep logical actions and data correct. but it won’t solve your blocking problems !!!! here is an example of what might happen:

suppose thread one is trying to exit bob.

status: line bob locked t1

at this time, the second thread departs from anne

status: line anne is blocked by thread 2

now thread 1 wants to snooze on anne

status: thread 1 sees that the line anne is locked, so it sits and waits for the lock to be released so that it can do the update: thread 1 really waits in the tweo fo thread to release the update and commit or roll back the lock

now stream two wants to contribute to bob

status: bob string is locked, so the second thread is waiting for its release

DEADLOCK !!!!!

two threads await each other.

so how do we solve it? Please see the Answers posted (I saw them by typing this) and please do not accept this answer, but accept the one you are actually using to prevent deadlocks. I just wanted to talk about other issues like me, and I'm sorry for taking so long.

Answer 3

You can use SELECT FOR UPDATE NOWAIT in a row before trying to update it. If the row is already locked, you will receive an error message (ORA-00054). Either wait a bit, or try again (*) or throw an exception.

You should never run into dead ends, as they are easy to prevent.

---

(*), in this case you will have to repeat the entire transaction (after the rollback) to prevent a deadlock situation.

Answer 4

Assuming that withdrawals and deposits are part of a single database transaction, it should be relatively easy to avoid deadlocks by simply working with the accounts in order. If your application carried out the transfer by debit or credit of the lower account number first, and then wrote off or credited the higher account number, you can never slow down by issuing several parallel transfers. It does not matter from the point of view of preventing a deadlock which order you are executing (although this may be important for application performance) if you agree to ensure compliance with this order.