MySQL: Deadlock found when trying to get lock

1
2
3
4
5

-- トランザクションAとBで同じ順序
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

async function executeWithRetry(fn, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await fn();
    } catch (error) {
      if (error.code === 'ER_LOCK_DEADLOCK' && i < maxRetries - 1) {
        await sleep(100 * Math.pow(2, i));
        continue;
      }
      throw error;
    }
  }
}

1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

1

SHOW ENGINE INNODB STATUS;