Locks aren't being released when an error is thrown inside a transaction!

[OultimoCoder]

Originally, I thought this was an issue with kysely-planetscale I raised an issue on their github and we have narrowed it down to an issue with the core @planetscale/database package.

Essentially, when you have a simple transaction and an error is raised and you try do another operation such as delete on the table you will get a 20 second timeout because the locks aren't being released.

Reproduction here I created using kysely:

https://github.com/OultimoCoder/kysely-planetscale-issue-20

Reproduction @jacobwgillespie did using raw queries:

https://github.com/jacobwgillespie/kysely-planetscale-issue-20

Please let me know if we are doing something wrong, been struggling with this for a while! Thank you.

[jacobwgillespie]

On ^, the minimal subset of the reproduction code at https://github.com/jacobwgillespie/kysely-planetscale-issue-20 is this:

  const conn = connect(config.database)
  try {
    await conn.transaction(async (trx) => {
      const userId = await trx.execute(
        'insert into `user` (`name`, `email`, `is_email_verified`, `password`, `role`) values (?, ?, ?, ?, ?)',
        [name, email, true, null, 'user']
      )
      await trx.execute(
        'insert into `authorisations` (`user_id`, `provider_type`, `provider_user_id`) values (?, ?, ?)',
        [Number(userId.insertId), providerType, providerUserId]
      )
      return userId
    })
  } catch (error) {
    await conn.execute('delete from `user` where `user`.`email` = ?', [email])
    throw new Error('User already exists!')
  }

The first insert into 'user' fails as it's a duplicate of an existing row's email value - this throws an error, but then the delete from 'user' in the catch block is stuck on a lock.

[mattrobenolt]

@jacobwgillespie just out of curiosity if you don't mind, inside of the catch block, if you do await conn.execute("rollback") does that allow things to continue correctly?

In theory we should be doing it here automatically https://github.com/planetscale/database-js/blob/main/src/index.ts#L189

Just not 100% sure without digging in why that wouldn't be working.

[jacobwgillespie]

@mattrobenolt Adding await conn.execute('ROLLBACK') did not free the lock — the rollback returns without an error, but then the delete from 'user' still times out due to the lock.

[jacobwgillespie]

I also tried just issuing the transaction commands directly, since I know transaction() is creating a second connection, and that also fails with the lock timeout:

  try {
      await conn.execute('BEGIN')
      const userId = await conn.execute(
        'insert into `user` (`name`, `email`, `is_email_verified`, `password`, `role`) values (?, ?, ?, ?, ?)',
        [name, email, true, null, 'user']
      )
      await conn.execute('COMMIT')
  } catch (error) {
    await conn.execute('ROLLBACK')
    await conn.execute('delete from `user` where `user`.`email` = ?', [email])
    throw new Error('User already exists!')
  }

[iheanyi]

Hey there @jacobwgillespie and @OultimoCoder . We've put this on the radar and hope to get around to patching it ASAP. Thank you for opening this ticket and making a repo with a reproduction, it's greatly appreciated.

[iheanyi]

Okay, figured out this bug. We've cut a release of v1.10.0 which fixes this error. Thanks for the report, your reproduction repo was really helpful in debugging it!

[OultimoCoder]

Upgraded the package in all my repos that use this and tests all pass (had tests failing due to this error before). Thank you!