Search code examples
javamysqlinnodbdeadlock

java mysql transaction deadlock

I have a scenario like this (semi-pseudocode)

con.setAutoCommit(false);
try {
   SELECT cash FROM bank_1 WHERE user = 1;


   long money = rs.getLong("cash")
   ... if money >= the amount we want then...

   UPDATE bank_1 SET cash=cash-money WHERE user = 1;
   UPDATE bank_2 SET cash=cash+money WHERE user = 2;

   con.commit();

} catch(Exception e1) { 
   con.rollBack(); 
} finally { 
    con.setAutoCommit(true);
    try { con.close(); } catch(Exception e) { }
}

then I have to do the opposite in another program concurrently to transfer from the second bank to the first one, so how do I avoid deadlock in this situation?

Solution

  • Add FOR UPDATE on the end of any SELECTs in the transaction.

    SELECT cash FROM bank_1 WHERE user = 1 FOR UPDATE;

    This locks the row(s) mentioned there so that other connections will not mess with the row before this transaction is finished.

    (There are cases where FOR UPDATE is not needed, but that's a longer discussion.)