Single query throwing deadlock error after updating a record

Question

I have a query that is being run across multiple micro-services, and due to that it is possible (expected) at times to deadlock.

const reserveActionForExecution = async (id: number): Promise<boolean> => {
  try {
    const query = `
      UPDATE articles a1
      LEFT JOIN
        articles a2
        ON a1.linkedArticleId = a2._id
      SET a1.status = ?, a1.updated_time = ?
      WHERE a1._id = ?
        AND a1.status = ?
        AND a1.updated_time = ?
        AND (a2.status = 'open' OR a2.status IS NULL)
    `

    const result = await client.query(query, ['pending', Date.now(), id, 'open', originalUpdatedTime])

    return result
  } catch (err: any) {
    if (extra.code === 'ER_LOCK_DEADLOCK')) return false

    throw err
  }
}

in the majority of cases it handles this deadlock as expected by returning false, however there are edge cases that have come up where the query will update the status and throw a deadlock. While the query being run in parallel by another service executes as expected, so it seems that in both cases the lock was acquired? As query A managed to update a record on article A and then throw a deadlock error meaning at some stage it had the lock, whereas query B executed with no errors and updated nothing as based on the join could see article A was already pending.

I'm struggling to understand how this is occurring, is it possible for a query to update and still throw a deadlock error based on the join? Executing it as a transaction seems to solve the issue but still curious on how this can even occur... Any info would be greatly appreciated.

Answer 1

I think it's because you are selecting the same row for a2 as a1, try adding condition which checks a1._id != a2._id