Locks aren't being released when an error is thrown inside a transaction! about database-js

[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!

[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.

[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!

[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!')
  }