Deal with deadlocks in MySQL

[miguelenguica]

Hi.
During some heavy update update operations made by a separate application, occasionally I get an Error "#40001 Deadlock found when trying to get lock; try restarting transaction". Having read MySQL tips to cope with this is to retry the transaction. My question is to know the best way to do this in Delphi. I'm doing this:

Code: Select all

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?

Any help is welcome. Thanks.

[DemetrionQ]

Hello.

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/ ... locks.html

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

Code: Select all

  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;

[miguelenguica]

Hi. Thank you for your answer. I understand how to deal with deadlocks inside the same application. The problem is that this happens with two different applications writing to the same database, and sometimes to the same table.

If I check the "innodb_lock_waits" table inside the information_schema db and wait until it's empty to start a new transaction, would this be a valid way to avoid deadlocks completely?

Thanks.

[DemetrionQ]

To avoid a deadlock, you can check if the table is locked before locking it. You can do this using the 'SHOW OPEN TABLES' SQL command ( http://dev.mysql.com/doc/refman/5.1/en/ ... ables.html ), for example:

Code: Select all

SHOW OPEN TABLES WHERE in_use <> 0 AND `Table` = 'YourTable' AND `Database` = 'YourDatabase';