query concurrency problem - Table is Locked

[msudhayaraj]

Environment

Knex version: 0.20.11
Database + version: ORACLE 11g
OS: Ubuntu 16.04 LTS

I try to run a update query with concurrent call.
The result is table locked. How to handle this issue. Is this correct way.
how to handle the concurrency .

The old conversion of the issue click here. Please help solve this. Thanks in advance.

The sample code is below

var oracledb = require('oracledb');
var strConnection = {
user: 'USR',
password: 'PWD',
connectString: "DB"
};
oracledb.poolMin = 3;
oracledb.poolMax = 7;
oracledb.poolIncrement = 1;
oracledb.poolPingInterval = 60; // seconds
oracledb.poolTimeout = 60;

oracledb.createPool(strConnection).then(pool => {
oraclePool = pool;

let sql = "update transaction_set set status='OK' where ts_id = 10693";

let manyQueries = [];
for (let i = 0; i < 200; i++) {
manyQueries.push(
pool.getConnection().then(conn => {
return conn.execute(sql);
}));
}
Promise.all(manyQueries).then(() => {
console.log('All queries done!');
});
});

[cjbj]

Acquaint yourself with the documentation Connections, Threads, and Parallelism. One sentence sticks out:

Structure your code to avoid parallel operations on a single connection. For example, do not use Promise.all.

Fix that.

I would take a wild guess that you haven't bumped UV_THREADPOOL_SIZE. If not, fix that too.

[msudhayaraj]

Acquaint yourself with the documentation Connections, Threads, and Parallelism. One sentence sticks out:

Structure your code to avoid parallel operations on a single connection. For example, do not use Promise.all.

Fix that.

I would take a wild guess that you haven't bumped UV_THREADPOOL_SIZE. If not, fix that too.

Thanks for you kind response.

i just tried for the below code. i used for loop to simulate the concurrency call.

var oracledb = require('oracledb');
process.env.UV_THREADPOOL_SIZE = 100;
var strConnection = {
user: 'USER',
password: 'PWD',
connectString: "connectionstring"
};
oracledb.poolMin = 3;
oracledb.poolMax = 100;
oracledb.poolIncrement = 1;
oracledb.poolPingInterval = 60; // seconds
oracledb.poolTimeout = 60;

oracledb.createPool(strConnection).then(pool => {
let sql = "update transaction_set set status='OK' where ts_id = 10696";

for (let i = 0; i < 100; i++) {
pool.getConnection().then(conn => {
conn.execute(sql).then(function (res) {
console.log(res);
});
});
}
});

i increased the max pool size also.I checked Db sessions. It takes 100 sessions.
some threads returned, but most of the threads were not returned.Process not fully completed.:(

[cjbj]

• You are very unlikely to want a pool size of 100 in almost any app. At some size, the pool could become less efficient.

• Use markdown formatting for code, i.e. use three backticks.

• Try using async/await instead of promises. Look at the examples.

• Open two terminals and start two SQL*Plus connections to the DB. Then run update transaction_set set status='OK' where ts_id = 10696 in both. Notice how the second one hangs because the row is locked by the first.

• Review your application design and determine how you want to handle committing. Over-comitting affects transactional consistency and performance, but obviously you need to commit at some time.

• Review Batch Statement Execution and Bulk Loading because you may find executeMany() is what you really need.

• Give us an actual, runnable test case that includes the SQL to create the table & initial data.

[msudhayaraj]

• You are very unlikely to want a pool size of 100 in almost any app. At some size, the pool could become less efficient.
• Use markdown formatting for code, i.e. use three backticks.
• Try using async/await instead of promises. Look at the examples.
• Open two terminals and start two SQL*Plus connections to the DB. Then run update transaction_set set status='OK' where ts_id = 10696 in both. Notice how the second one hangs because the row is locked by the first.
• Review your application design and determine how you want to handle committing. Over-comitting affects transactional consistency and performance, but obviously you need to commit at some time.
• Review Batch Statement Execution and Bulk Loading because you may find executeMany() is what you really need.
• Give us an actual, runnable test case that includes the SQL to create the table & initial data.

Thanks for your response.

Please consider the following code

var reqExpress = require('express');
var router = reqExpress.Router();
var oracledb = require('oracledb');
var strConnection = {
    user: 'USER',
    password: 'PWD',
    connectString: "DB"
};
oracledb.poolMin = 3;
oracledb.poolMax = 10;
oracledb.poolIncrement = 1;
oracledb.poolPingInterval = 60; // seconds
oracledb.poolTimeout = 60;
var dbPool = '';
oracledb.createPool(strConnection)
    .then(
        function (pool) {
            dbPool = pool;
        });
router.post('/Ping', function (appRequest, appResponse) {
    try {
        dbPool.getConnection()
            .then(function (dbConnection) {
                var query = "update transaction_set set status='OK' where trn_id = 24";
                dbConnection.execute(query)
                    .then(function (result) {
                        console.log(result);
                        appResponse.send("SUCCESS");
                    })
                    .catch(function (err) {
                        console.error(err);
                        appResponse.send(err);
                    });
            });
    } catch (error) {
        console.log(error, '------------' + error);
    }
});
module.exports;

Table script

create table TRANSACTION_SET
(
TS_ID NUMBER(9) not null,
PARENT_TS_ID NUMBER(9),
TRN_ID NUMBER(9),
DT_CODE VARCHAR2(128),
DTT_CODE VARCHAR2(128),
PROCESS_STATUS VARCHAR2(32),
STATUS VARCHAR2(32)
);



insert into TRANSACTION_SET (ts_id,parent_ts_id,trn_id,dt_code,dtt_code,process_status,status)
values (1,0,24,'dt_45777','dtt_45678','CREATED',CREATED)
insert into TRANSACTION_SET (ts_id,parent_ts_id,trn_id,dt_code,dtt_code,process_status,status)
values (2,1,25,'dt_457778','dtt_456789','CREATED',CREATED)

Here i have declared the instance at the app.js
During each api call, a connection will be obtained from the pool.
using that connection query will be executed.

500 concurrent users trying to update the first record of the table.
This is going to be my production scenario where concurrent call will be initiated from different users(browsers) .
How can i manage this locking issue?
please advice me on this

[cjbj]

You should determine what sequence of statement executions constitutes a 'transaction' for each user, and then your app should commit or rollback at the end of that transaction. If other users are blocked, then they are blocked by design - two people can't update the same record at a time. As the manual puts it:

The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users. For example, a DML lock can prevent multiple customers from buying the last copy of a book available from an online bookseller

Your app will need to detect errors and give users useful information.

Some of the content at https://asktom.oracle.com/ would also be good reading.

[cjbj]

PS that latest iteration of code jas poolMax of 10 but didn't set process.env.UV_THREADPOOL_SIZE, so Node.js could be prone to thread starvation, leading to another kind of deadlock.

[msudhayaraj]

I could understand the importance of UV_THREADPOOL_SIZE. I will implement it.

But still i need some clarifications regarding concurrent calls.
My understanding is that,

1. concurrent calls hit the api,
2. Each api call would obtain a connection from the pool (declared globally)
3. Query execution method will be called by the Oracle DB NPM
4. Record will be locked by oracle to update the record
5. Once the update is completed it will be returned back to the code and waiting for the command from the connection either to COMMIT or ROLLBACK. In the case of autocommit scenario the connection will automatically raise the commit followed by the Update query. Otherwise user should explicitly commit/rollback the transaction.
6. Other threads will wait for the same record to be released by above connection.
   They should be waiting. Not that they are hang.

Exception handing can be done only in the scenario where threads return. (and is not possible in the case of hang)

In my scenario (autocommit), i expect oracle connection to be returned either successfully or with errors. But nothing happens to me. All threads just hang.

This not the case when i try using the pg NPM for the same PoC concept . It seems that it properly returns the threads even for 1000 concurrent calls. (with default UV_THREADPOOL_SIZE value)

Same Query --> Same API --> Concurrent Calls (more than 500 calls). This is my requirement.

Please advise me in this regard.

[msudhayaraj]

And how can i identify that if a record is being locked by another connection?
My second connection doesnt notify me about this. (During the jmeter testing with more number of threads)
I expect either the remaining connections should wait and do the work once the resource is released or they should get returned with errors mentioning that the record is being locked. So that it is possible for me to do exception handling.

Thanks in advance for your comments.

[dmcghan]

And how can i identify that if a record is being locked by another connection?

You'll need to first lock the rows you want to work with using a select for update. Once your session has obtained the row-level locks you can execute the updates on them safely. With for update, you have the option to use nowait (which will return an ORA-00054) and skip locked depending on your needs.

You can use a PL/SQL block to avoid additional round trips with two statements and you can use a cursor so the update can avoid a second index lookup.

Given the following table:

create table t (
  c  number primary key,
  c2 varchar2(10)
);

insert into t (
  c,
  c2
) values (
  1,
  'test 1'
);

insert into t (
  c,
  c2
) values (
  2,
  'test 2'
);

insert into t (
  c,
  c2
) values (
  3,
  'test 3'
);

commit; 

You could do the following:

declare

  cursor t_cur
  is
    select *
    from t
    where c = :bind_in
    for update nowait;
    
  l_rec t_cur%rowtype;
  
  resource_busy exception;
  pragma exception_init(resource_busy, -54);

begin

  open t_cur();
  fetch t_cur into l_rec;
  
  update t
  set c2 = 'new value'
  where current of t_cur;
  
  close t_cur;
  
exception

  when resource_busy
  then
    null; --handle as needed here
  
end;

[msudhayaraj]

And how can i identify that if a record is being locked by another connection?

You'll need to first lock the rows you want to work with using a select for update. Once your session has obtained the row-level locks you can execute the updates on them safely. With for update, you have the option to use nowait (which will return an ORA-00054) and skip locked depending on your needs.

You can use a PL/SQL block to avoid additional round trips with two statements and you can use a cursor so the update can avoid a second index lookup.

Given the following table:

create table t (
  c  number primary key,
  c2 varchar2(10)
);

insert into t (
  c,
  c2
) values (
  1,
  'test 1'
);

insert into t (
  c,
  c2
) values (
  2,
  'test 2'
);

insert into t (
  c,
  c2
) values (
  3,
  'test 3'
);

commit; 

You could do the following:

declare

  cursor t_cur
  is
    select *
    from t
    where c = :bind_in
    for update nowait;
    
  l_rec t_cur%rowtype;
  
  resource_busy exception;
  pragma exception_init(resource_busy, -54);

begin

  open t_cur();
  fetch t_cur into l_rec;
  
  update t
  set c2 = 'new value'
  where current of t_cur;
  
  close t_cur;
  
exception

  when resource_busy
  then
    null; --handle as needed here
  
end;

Thanks for your response and sorry for the late replay.
I tried the same concurrency test as you suggested (select for update) but the result is same (Table is locked).

I tired the below code.

var oracledb = require('oracledb');
process.env.UV_THREADPOOL_SIZE = 100;
var strConnection = {
  user: 'USER',
  password: 'PWD',
  connectString: "DB"
};
oracledb.poolMin = 3;
oracledb.poolMax = 100;
oracledb.poolIncrement = 1;
oracledb.poolPingInterval = 60; // seconds
oracledb.poolTimeout = 60;

oracledb.createPool(strConnection).then(pool => {
// select for update
  var selectQry = "select * from transaction_set where ts_id = 10691 for update nowait";
  pool.getConnection().then(conn => {
    conn.execute(selectQry).then(function (res) {
      // update qry
      let updateQry = "update transaction_set set status='OK' where ts_id = 10691";
      for (let i = 0; i < 1; i++) {
        pool.getConnection().then(conn => {
          conn.execute(updateQry).then(function (res) {
            console.log(res);
          });
        });
      }
    });
  });
});

The same code with works fine in postgresql . why oracledb npm not handling the same?
I am not giving any process.env setup for pg npm.
But it internally queue process execute the query and return the connection to pool.
But oracledb npm not does it?

[cjbj]

The same code with works fine in postgresql . why oracledb npm not handling the same?

LOL !!! I suggest you do some reading into the locking models available in the databases.

[dmcghan]

Why does your example have a single iteration loop? I’m seeing several issues, but I want to make sure I understand what you’re trying to do before commenting.

[dmcghan]

You didn't respond about the loop, so I'll just comment on the other stuff. As Chris hinted at, not all databases work the same with things like transactions, consistency, etc. It's your job as a developer to understand the databases you're working with so you can use them correctly. For Oracle, the chapter on transactions in the Database Concepts guide might be a good place to start.

[dmcghan]

Also, you should learn to use async/await. The code you have could be rewritten as follows, which is much easier to read (I removed the loop which didn't do anything):

var oracledb = require('oracledb');
process.env.UV_THREADPOOL_SIZE = 100;
var strConnection = {
  user: 'USER',
  password: 'PWD',
  connectString: "DB"
};
oracledb.poolMin = 3;
oracledb.poolMax = 100;
oracledb.poolIncrement = 1;
oracledb.poolPingInterval = 60; // seconds
oracledb.poolTimeout = 60;

async function test() {
  let pool = await oracledb.createPool(strConnection);
  
  let conn = await pool.getConnection();

  let selectQry = "select * from transaction_set where ts_id = 10691 for update nowait";

  let res = await conn.execute(selectQry);

  let conn2 = await pool.getConnection();

  let updateQry = "update transaction_set set status='OK' where ts_id = 10691";

  let res2 = await conn2.execute(updateQry);

  console.log(res2);
}

test();

See the links at the bottom of this post to learn about the progression of various async patterns, ending with aysnc/await: https://jsao.io/2017/06/how-to-get-use-and-close-a-db-connection-using-various-async-patterns/