Oracle transaction "release" is not returning a promise

[cjbland]

Environment

Knex version: 0.21.5
Database + version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64 bit Production
OS: CentOS 7.8

Select applicable template from below.
If issue is about oracledb support, tag @atiertant.

Bug

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

We are trying to execute a large transaction and it is failing after the first SAVEPOINT is executed. We are using feathers-knex in front of knex as the driver to Oracle. After the second transaction finishes, the release is called but it is not returning a promise. At this point feathers-knex fails because the promise chain is broken. If I understand the code correctly, the base transaction release function IS returning a promise. The specific line in question is here:

https://github.com/knex/knex/blob/master/lib/dialects/oracledb/transaction.js#L21

2. Error message

The error message is specific to feathers-knex, not knex itself, but appears to be caused by knex. The specific error message is:

TypeError: Cannot read property 'then' of undefined
    at Object.<anonymous> (../node_modules/feathers-knex/lib/hooks.js:60)

If I modify the knex source and edit lib/dialects/oracledb/transaction.js to add async in front of the release function, then everything works as expected.

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

This one is a little challenging for me to provide as our code is ridiculous. I can, however, provide the debug output as a start and can provide more details as needed:

  knex:tx trx10: Starting top level transaction +0ms
  knex:query select count(...) from mytable where ... trx10 +41s
  knex:bindings [...] trx10 +41s
  knex:query select * from mytable where ... trx10 +4s
  knex:bindings [...] trx10 +4s
  knex:tx trx11: Starting nested transaction +10s
  knex:query SAVEPOINT trx11 trx11 +1s
  knex:bindings [] trx11 +1s
  knex:query select * from anothertable where ... trx11 +80ms
  knex:bindings [...] trx11 +80ms
  knex:tx trx11: releasing connection +0ms
TypeError: Cannot read property 'then' of undefined

[elhigu]

Sounds like an issue that really should be checked out. There has been problems with oracledb transactions and some race conditions and them failing strangely time to time. This could be related to it.

Though I don't remember at all what for release method existst (releases the connection back to the pool?)...

[msudhayaraj]

Hi,

Is this issue resolved?

I had faced a similar issue like earlier and raised a ticket for this.
https://github.com/knex/knex/issues/3819

I guess the following two scenarios

1. The bridge between knex (via oracle dialect) and oracle db is vanished
   before oracledb completes its works and returns back. Hence oracle is waiting for commit signal while locking the object meantime.

2. Connection from the knex pool is not properly released. Hence the oracle db is waiting for the handshake to be properly closed.

Note: In my sample i was trying to update the same record in table with concurrent calls.
My PostgresSQL dialect handles this scenario very nicely. It queues things very nicely even though it updates the same record.

Even with one of my sample(oracle) program(to make an insert in a table), the table is getting locked with concurrent calls. I couldnt guess how a table can be locked in an insert operation.

Kindly advice me on this. Thanks.

[aidenfoxx]

@msudhayaraj Did you ever find a solution to the concurrency issue? I'm currently facing the same thing with a knex in a CMS I'm testing out. It tries to make concurrent updates to a row, but the table gets locked and knex hangs.

Debugging the CMS I can see where it creates the concurrent queries. Each query is linked to a different HTTP request:

  knex:client acquired connection from pool: __knexUid2 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 trx7 +3ms
  knex:bindings [ 2021-06-12T14:03:41.866Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] trx7 +3ms
  knex:client releasing connection to pool: __knexUid1 +1ms
  knex:client acquired connection from pool: __knexUid1 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +0ms
  knex:bindings [ 2021-06-12T14:03:41.867Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid5 +1ms
  knex:client acquired connection from pool: __knexUid5 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.868Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +0ms
  knex:client releasing connection to pool: __knexUid4 +1ms
  knex:client acquired connection from pool: __knexUid4 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.869Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid6 +1ms
  knex:client acquired connection from pool: __knexUid6 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.870Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid3 +1ms
  knex:client acquired connection from pool: __knexUid3 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.871Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +2ms

In the database the table will have multiple locks on it. One for each query. The locks remain until the knex instance is destroyed.

Unfortunately destroying the connection isn't an option since the CMS runs as a server.

[kibertoad]

@aidenfoxx Unfortunately, I don't have access to an Oracle DB right now, and setting up one locally is very tricky. If you could investigate this issue further and propose a fix, that would be appreciated very much.

[aidenfoxx]

@kibertoad If you want an easy OracleDB instance, I recently set up knex-schema-inspector with a container for integration testing. I've been using that when I want to easily spin up and tear down an instance. Nevermind. Looks like knex also has a container, so that works too. 😄

But I will continue digging into this at some point.

[msudhayaraj]

@msudhayaraj Did you ever find a solution to the concurrency issue? I'm currently facing the same thing with a knex in a CMS I'm testing out. It tries to make concurrent updates to a row, but the table gets locked and knex hangs.

Debugging the CMS I can see where it creates the concurrent queries. Each query is linked to a different HTTP request:

  knex:client acquired connection from pool: __knexUid2 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 trx7 +3ms
  knex:bindings [ 2021-06-12T14:03:41.866Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] trx7 +3ms
  knex:client releasing connection to pool: __knexUid1 +1ms
  knex:client acquired connection from pool: __knexUid1 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +0ms
  knex:bindings [ 2021-06-12T14:03:41.867Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid5 +1ms
  knex:client acquired connection from pool: __knexUid5 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.868Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +0ms
  knex:client releasing connection to pool: __knexUid4 +1ms
  knex:client acquired connection from pool: __knexUid4 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.869Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid6 +1ms
  knex:client acquired connection from pool: __knexUid6 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.870Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +1ms
  knex:client releasing connection to pool: __knexUid3 +1ms
  knex:client acquired connection from pool: __knexUid3 +0ms
  knex:query update "directus_users" set "last_access" = :1 where "id" = :2 undefined +1ms
  knex:bindings [ 2021-06-12T14:03:41.871Z, '51454f6b-c08f-4c2d-a59f-244411ed276c' ] undefined +2ms

In the database the table will have multiple locks on it. One for each query. The locks remain until the knex instance is destroyed.

Unfortunately destroying the connection isn't an option since the CMS runs as a server.

can you pls refer this link.