Search code examples
mysqlconcurrencytransactionsdeadlockisolation-level

Read committed and transaction Error 1213: Deadlock

I have MySql 5.7 and following golang code that may be run concurrently:

tx := s.db.Begin()

if err := tx.Exec(fmt.Sprintf("DELETE FROM related_table WHERE id = %d " item.ID)).Error; err != nil {
    tx.Rollback()
}

// Save is ORM method, it make inserts into 'related_table' from the first query 
if err := tx.Save(&item).Error; err != nil {
    tx.Rollback()
}

I catch error during tx.Save(&item)

Error 1213: Deadlock found when trying to get lock; try restarting transaction

The question is:

How it possible that mysql transaction is not protected against deadlocks? Don't transactions run sequentially?

Solution

  • select ... for update is setting exclusive locks on the rows until the end of transaction

    Just run following at the beginning of transaction: 

    tx.Exec("SELECT * FROM %s WHERE coupon_id = ? FOR UPDATE", item.ID))