if can do record lock when update a record

[Benzhucn]

When we try to read a record and than update it, there is a risk that, the record may be updated by some other process before our update. Is there any function to lock the record when we doing update? Your quick reply is highly appreciated!

[krisbaehr]

@Benzhucn I'm not sure if there's anything in the connector but you can store last change timestamps in your tables and use them to perform optimistic lock checking. On the read, you get a last change timestamp for the row. Then on the update, that timestamp can be added to your where clause.... update table set foo = 1 where id = 100 and lastChangeTimestamp = :timestampFromPreviousRead; If the row wasn't found, you can do a bit of checking to know if someone else has updated it.

[kadler]

You might want to look at https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzisocl.htm

You should be able to use SQL_ATTR_AUTOCOMMIT to disable autocommit with setConnAttr. You can change transaction isolation by setting setConnAttr(SQL_ATTR_TXN_ISOLATION, ...), passing in either SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE for the value.

[kadler]

There should probably be convenience functions conn.setAutoCommit(bool) and conn.setIsolationLevel(value) for these things.

[Benzhucn]

@kadler thank you. Another question, when the record locked by RPG or other process, and at the same time, our nodejs process also want to update it, can this driver wait for the lock released? How should we do to handle this? Many thanks.

[dmabupt]

Hello @Benzhucn , I tried to lock a table in process A --

LOCK TABLE xumeng.test IN EXCLUSIVE MODE ALLOW READ;

And then call idb-connector to insert a record into it (process B)--

const { dbconn, dbstmt } = require('idb-connector');
const sql = `INSERT INTO xumeng.test VALUES (1, 'mengxu')`;
const connection = new dbconn();
connection.conn('*LOCAL');
const statement = new dbstmt(connection);
statement.exec(sql, (rs, err) => {
    if(err) console.log(err);
    ...

The Node.js program (process B) will be blocked for a while (about 10 second?) to wait for process A.

• If process A ends in the 10 seconds, the insert operation in process B will be done as normal.
• Otherwise, below error will be reported from process B --

[Error: SQLSTATE=57033 SQLCODE=-913 Row or object TEST in XUMENG type *FILE in use.]

We may catch this error and retry later. I do not know if there are more graceful methods to do that.

[Benzhucn]

There should probably be convenience functions conn.setAutoCommit(bool) and conn.setIsolationLevel(value) for these things. @kadler I tried to do it but fail. Do you have any examples? My case is like below: 1, in the connection, to set autocommit false and set setIsolationLevel=SQL_TXN_READ_COMMITTED; 2, get a statement, use the statement to run the sql like below: 1) SELECT FROM LIB/TABLE FOR UPDATE WITH RS, 2) UPDATE THE RECORD, 3) SELECT FROM LIB/TABLE 4) COMMIT IT

Then only the first SQL run successful, others fail. The error is like: SQLSTATE=HY010 SQLCODE=-99999.

Waiting for your reply.

[dmabupt]

Could any DB2 expert answer the question? @kadler @forstie ?

And @Benzhucn, Would you provide some code snippet? Can these SQL statements run correctly in other tools like ACS?

[kadler]

@Benzhucn what is the message given?

Also, is there a reason you are using SQL_TXN_READ_COMMITTED and using WITH RS on the select instead of using SQL_TXN_REPEATABLE_READ (the equivalent of *RS) for the whole connection?

[kadler]

FYI, CLI has a connection option called SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION which allows setting the behavior when locked rows are attempted to be selected. The default (IIRC) is SQL_WAIT_FOR_OUTCOME which waits for record locks for updated rows to become unlocked. There is also SQL_USE_CURRENTLY_COMMITTED which ignores the updated values and uses the previous values or SQL_SKIP_LOCKED_DATA, which skips records that are locked.

Setting this attribute has no effect except for when the isolation level is set to SQL_TXN_READ_COMMITTEDorSQL_TXN_REPEATABLE_READ`.

[github-actions[bot]]

:wave: Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.