does MySQL executes single statement atomically?-CodePudding

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;

CodePudding user response：

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.