Search code examples
mysqlsqldatabase-deadlocks

does MySQL executes single statement atomically?

say, i have 2 connections and they are executing following code. can it lead to deadlock?

connection 1-> lock tables A write, B write;
connection 2-> lock tables B write, A write;
Solution

  • You could eliminate the risk of deadlock by using LOCK TABLES A WRITE, B WRITE; in both connections. This is atomic, so the second connection would wait.

    If you don't use LOCK TABLES, there's a risk of deadlock.

    Even a single SQL statement can deadlock in certain cases. We see this frequently at my current workplace, for example, if a table has more than one primary or unique key. For example:

    CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  x INT,
  UNIQUE KEY (x)
);

    MySQL seems to have a design flaw that allows deadlocks because it is not atomic for a statement to acquire locks on multiple unique keys.