Proper design to avoid Oracle deadlocks?

Question

The usual advice when it comes to avoiding deadlocks is to always lock resources in the same order. But how would you implement this with regards to row locks in highly contented Oracle database?

To see what I mean, consider the following example. A very simple DAO to handle 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 execute a transfer between two accounts, there is some sort of 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
    }

Normally this works fine. But let's say that we have two people initiating transfers at the same time. Let's say that Anne wants to transfer 100 bucks to Bob at the same time that Bob wants to transfer 50 to Anne. So in one thread Anne calls transfer("Anne", "Bob", 100), and in another Bob calls transfer("Bob", "Anne", 50). This code is susceptible to dead locks, if the executing 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 hadn't considered this at all before I started to see dead locks in a real application. My naive view was that the transaction isolation sort of took care of this automatically. Oracle says that this is due to poor application design. But what is a good design in this case? Do I need to select for update everything I plan to update? What if this is a huge transaction involving updates several tables? Should I design so that dead locks are impossible or just minimize them and accept that they are a fact of life?

Answer 1

I think it's a fact of life (and one that should really only happen with high concurrency and hotspot data).

If you wanted to implement lock ordering, then yes, you'd need to rewrite your code to lock or update the accounts in pre-determined order (first Anne, then Bob). But that won't be feasible with complex transactions. If it only happens with a few hotspot rows, maybe you can use lock ordering for just those (and leave the rest as is) and get by with that.

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

In your case, you can just retry the aborted transaction. And if it happens too often, it does seem like you have some problem with your application design.

Here is a link for a two-phase commit protocol for bank account transfers. It is from the MongoDB wiki, i.e. from people who do not even have the luxury of row locks and transactions in the first place, but one could implement that on an RDBMS as well in order to avoid lock contention. That would of course be a rather radical application redesign. I'd try everything else first (retries, coarse locks, artificially reduced concurrency level, batch processing).