0

I am using Knex npm as my query builder. version 0.20.11. Node version is 12.16.1

Pool size

Min -3 Max -500

My services is do some long running process, do insert and updates more than 2 tables finally commit the transaction.

i give the concurrent call more than 150 to 200, to my service. The table is locked.service is hanged.Not able to give even a single thread after the table is locked.

i am using knex.transaction, i commit and destroyed my connection after end of the service.

pls suggest me how to solve this,if any one faced this kind of issues.

Note:Working fine in Minimum number of threads

M.S.Udhaya Raj
  • 150
  • 1
  • 12

1 Answers1

1

If you've increased the connection pool size, then increase UV_THREADPOOL_SIZE, see the node-oracledb documentation Connections, Threads, and Parallelism. As shown, make sure this is set before the Node.js threadpool starts. On Windows it must be set before Node.js itself is executed. So the best way is to set the value as an environment variable. For example, on Linux your package.json may have a script like:

"scripts": {
    "start": "export UV_THREADPOOL_SIZE=10 && node index.js"
  },
. . .

Or, on Windows:

"scripts": {
    "start": "SET UV_THREADPOOL_SIZE=10 && node index.js"
  },
. . .

Knex currently tries to set the value internally: this appears to be too late, and also won't work on Windows.

Also make sure you are closing connections after use, in all places including error routines.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Thanks for your answer. But i had the same error.What is the max UV_THREADPOOL_SIZE value.. – M.S.Udhaya Raj Oct 23 '20 at 04:40
  • inside knex npm oracle dialect had the code to automatically increase the UV_THREADPOOL_SIZE. – M.S.Udhaya Raj Oct 23 '20 at 06:45
  • Using jmeter I gave 100 concurrent threads to one of our api which will insert a record in a table. 100 sessions are created in oracle (4 active & 96 inactive). But nothing happens. Threads are just waiting.Still i have no idea to overcome this scenario. – M.S.Udhaya Raj Oct 23 '20 at 07:59
  • I would be suspicious of any setting inside the code. Unless `UV_THREADPOOL_SIZE `is set before the thread pool starts, the default size of 4 will still be used. This means it really needs to be the very first thing in the code base. Also setting `process.env.UV_THREADPOOL_SIZE` in Node.js apps when running on Windows is ignored. The fact you saw 4 active sessions is a very strong clue. So try setting the environment variable before starting the app, such as in your package.json. See the node-oracledb doc https://oracle.github.io/node-oracledb/doc/api.html#numberofthreads – Christopher Jones Oct 23 '20 at 22:28
  • 1
    I logged this against Knex: https://github.com/knex/knex/issues/4076 – Christopher Jones Oct 23 '20 at 23:34
  • @Christoper Jones Thanks for your response. That gives some hope, I am testing set UV_THREADPOOL_SIZE and give concurrent test. – M.S.Udhaya Raj Nov 02 '20 at 07:07