Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

oracledb not releasing connection from pool after disconnect #3605

Closed
jakecoffman opened this issue Jan 2, 2020 · 3 comments
Closed

oracledb not releasing connection from pool after disconnect #3605

jakecoffman opened this issue Jan 2, 2020 · 3 comments

Comments

@jakecoffman
Copy link
Contributor

@jakecoffman jakecoffman commented Jan 2, 2020

Environment

Knex version: 0.20.6
Database + version: Oracle 12c
OS: Linux & OSX

@atiertant

Bug

  1. Explain what kind of behaviour you are getting and how you think it should do:

When oracledb falls of the network or the application can't connect and there's an open transaction, the pool doesn't empty. After the pool is full we see Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? and the application cannot be used until we restart it.

  1. Error message

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

  1. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real
    database connection to MySQL or PostgreSQL, then single file example which initializes
    needed data and demonstrates the problem.

See https://github.com/jakecoffman/knex-oracle-bug

The crux is:

  const trx = await knex.transaction()
  console.log("Started transaction")

  await sleep(5000)
  console.log("Committing")

  try {
    await trx.commit()
  } catch (e) {
    console.log("Error committing:", e)
    await knex.destroy()
    return
  }

  console.log("Ok")

If you disconnect during the sleep then the commit() still finishes immediately (I expected it would block and throw on reject but it does not) and after a minute you see an Unhandled rejection and the connection isn't freed from the pool.

@elhigu

This comment has been minimized.

Copy link
Member

@elhigu elhigu commented Jan 3, 2020

Connection should not be removed from pool at that point yet. However it should be marked to be disposed next time when it is tried to be aqcuired from pool.

Your example is also using transactions in an invalid way, that might be the reason why you are getting unhandled rejection. Check transaction provider from latest docs and set rejectioin handlers properly.

It is not documented well, but trx.commit() probably never rejects (I haven't checked it from code lately though). Related issue opened about this from few weeks ago:
#3583

Lets reopen if this seems to work wrong after you have fixed your transcation code.

@elhigu elhigu closed this Jan 3, 2020
@jakecoffman

This comment has been minimized.

Copy link
Contributor Author

@jakecoffman jakecoffman commented Jan 3, 2020

I updated my example in the linked repo: https://github.com/jakecoffman/knex-oracle-bug/blob/68ea525f0ea2887f50e29c1ef3a7ba0fb9b6786e/index.js

I changed it to match how you use transactions in the issue you linked. I removed the call to commit as I think that's a separate issue.

I'm still seeing it not release the connection at the end of the script running. Here's the output when I pull the network cable during the sleep:

> DEBUG=* node index

  knex:tx trx1: Starting top level transaction +0ms
  knex:client acquired connection from pool: __knexUid2 +0ms
  knex:query insert into "COFFMANJ"."TABLE1" ("COLUMN1") values (?) trx1 +0ms
  knex:bindings [ 'hello' ] trx1 +0ms
Sleeping
Committing
  knex:tx trx1: releasing connection +0ms
No errors
Unhandled rejection Error: ORA-03113: end-of-file on communication channel
Process ID: 37950
Session ID: 1858 Serial number: 4295027021

I've tracked down what is happening:

  1. It outputs releasing connection before it calls the driver's commit.
  2. Then it calls commit, which is going to fail because I pulled the network cable.
  3. After a bit it is rejected
  4. There is no rejection handler in the calling code so it is unhandled and never frees the pool.

And actually looks like this code is invalid since this refers to the function and not the transaction instance.

I can put a PR up for this.

jakecoffman added a commit to jakecoffman/knex that referenced this issue Jan 3, 2020
@elhigu elhigu reopened this Jan 4, 2020
@kibertoad kibertoad closed this in 0799727 Jan 4, 2020
@kibertoad

This comment has been minimized.

Copy link
Collaborator

@kibertoad kibertoad commented Jan 7, 2020

Released in knex@0.20.7

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.