Postgres updateOrCreate deadlocking

[skrenek]

I'm seeing an odd error and am hoping someone here sees something I don't. I use updateOrCreate from Lucid in a number of places, but today one method started deadlocking on me. The select for update creates a transaction (as it should), but then the update creates another one, thus deadlocking the update query.

Relevant NPM lib versions
@adonisjs/[email protected]
@adonisjs/[email protected]
[email protected]
[email protected]
[email protected]

Thing's I've tried:

• Passing in my own manually created transaction (Database.transaction())
• Manually creating the updateOrCreate logic by looking up the record and updating or inserting it

Both of those have failed with the same deadlock. I'm working around it for now by manually creating the update or create logic and omitting the forUpdate call on the select, which prevents the second transaction, but I'd rather do things right.

Here is the current code I'm trying that deadlocks:

const someValue = 'x'
const updateData = someObject
const trx = await Database.transaction()
let existing: MyModel | null
try {
  existing = await MyModel.query({ client: trx })
    .forUpdate()
    .where({ someField: someValue })
    .first()
  if (existing) {
    existing.merge(updateData)
    existing = await existing.useTransaction(trx).save()
  } else {
    existing = await MyModel.create(updateData, { client: trx })
  }
  await trx.commit()
} catch (err) {
  await trx.rollback()
}

Here is the output from knex with DEBUG=knex:query turned on:

knex:query BEGIN; trx2 +7ms
knex:query select * from "my_models" where "some_field" = $1 limit $2 for update trx2 +3ms
knex:query BEGIN; trx3 +21ms
knex:query update "my_models" set "updated_at" = $1, "project_id" = $2 where "id" = $3 trx3 +3ms

If anyone sees something I'm missing, I'd greatly appreciate any insights. Thanks!

[thetutlage]

What happens if you try the same thing with Knex directly?

[skrenek]

I figured it out via debugging the save flow. "Someone" overwrote the save function for that model and forced their own transaction in it instead of checking for an existing one first. This was a good lesson in "when things seem wonky, check your model hooks and overrides first". 😅 Thanks for the comment though, as it put me on the right path.