Avoid deadlocks MySQL/UniDAC/Delphi

Question

I'm using UniDAC (Devart) in 2 applications accessing a MySQL database. During some heavy update update operations made by one application, occasionally I get an Error "#40001 Deadlock found when trying to get lock; try restarting transaction" in the other. Having read MySQL tips to cope with this, they say to retry the transaction. My question is to know the best way to do this in Delphi. I'm doing this:

transaction_completed_ok:= False;
repeat
  try
    my_db.StartTransaction;
    (... do the inserts)
    my_db.Commit;
    transaction_completed_ok:= True;
  except
    my_db.Rollback;
    Sleep(1000);
  end;
until transaction_completed_ok;

Doing this to every transaction, on both apps, is a valid way to deal with problem? Can anyone share a best way? Any help is welcome.

Answer 1

Your code for restarting transaction on error cannot solve the problem, since re-execution of one and the same code will cause the same errors. For example, if your code causes uniqueness violation, the application will get stuck. To solve the problem, you should reorganize the application logic, in order to avoid a deadlock. A deadlock occurs when two parallel connections attempt to lock 2 tables in a different order, for example:

connection1 locks tableA;
connection2 locks tableB;
connection1 attempts to lock tableB - waits for connection2 to unlock tableB;
connection2 attempts to lock tableA - waits for connection1 to unlock tableA.

As a result, we get a deadlock. To avoid the deadlock, you should set the same table locking order for both connections, for example:

connection1 locks tableA;
connection2 locks tableA;
connection1 locks tableB;
connection2 locks tableB.

You can read more information about deadlock at http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html

To solve the problem, you can use the following algorithm:

  isLocked := False;

  while not isLocked do
  try
    < explicit lock tableA >
    < explicit lock tableB >
    isLocked := True;
  except
    < explicit unlock tableA >
    < explicit unlock tableB >
  end;

  if isLocked then
  try
    < do inserting to tableA and tableB >
  finally
    < explicit unlock tableA >
    < explicit unlock tableB >
  end;