Issue in reading records using select for update query in SAP ASE 16 Database

[former_member245994]

Query:

I am trying to read records from SAP ASE 16 Database table concurrently using java to increase performance. I am using select….for update query to read database table records concurrently. Here two threads are trying to read records from single table.

I am executing this command on a microservice based environment.

I have done below configuration on database:

• I have enabled select for update using: sp configure "select for update", 1
• To set locking scheme I have used: alter table poll lock datarows

Table name: poll

This is the query that I am trying to execute:

SQL Query:

SELECT e_id, e_name, e_status FROM poll WHERE (e_status = 'new') FOR UPDATE

UPDATE poll SET e_status = 'polled' WHERE (e_id = #e_id)

Problem:

For some reason I am getting duplicate records on executing above query for majority of records sometimes beyond 200 or 300. It seems like locks are not being acquired during execution of above command. Is there any configuration that I am missing from database side. Does it have anything to do with shared lock and exclusive lock?

Any help is appreciated!

[Mark_A_Parsons]

It's not clear from what's been posted if you are performing the two queries - 'select/for update' and 'update' - within a single transaction. A transaction is required to hold the exclusive lock (obtained by the 'select/for update') until the 'update' is run.

Can you confirm you are performing one of the following:

• db connection has been configured with 'autocommit=false'
• running 'set chained on' before running the 'select/for update' and 'update'?
• running 'begin tran' before running the 'select/for update' and 'update'?

-------------

It's not clear from what's been posted if the 'select/for update' and 'update' are working with the same set of rows, ie, the two queries have different where clauses:

• WHERE e_status = 'new' (select/for update)
• WHERE e_id = #e_id (update)

Also, assuming 'e_id' is the PK, the 'select/for update' would appear to have the ability to select (and lock) multiple rows while the 'update' would affect a single row; while this is technically doable and shouldn't lead to duplicate updates it also highlights the potential for the two queries to work with different sets of rows.

How are you insuring the two queries are working with the same record(s)?

• Do you have a client-side cursor that's generating an 'update' for each row retrieved by the 'select/for update'?
• Do you issue a 'set rowcount 1' to limit the rows returned by the 'select/for update'?
• Something else?

[former_member245994]

1.Can you confirm you are performing one of the following:

db connection has been configured with 'autocommit=false'

running 'set chained on' before running the 'select/for update' and 'update'?---- check this can we do this for select query also

running 'begin tran' before running the 'select/for update' and 'update'?

-------------

Answer: we are doing db connection configuration with 'autocommit=false' at code level

2.How are you insuring the two queries are working with the same record(s)?

Do you have a client-side cursor that's generating an 'update' for each row retrieved by the 'select/for update'?

Do you issue a 'set rowcount 1' to limit the rows returned by the 'select/for update'?

Something else?

Answer: we are using flag/status column to get that record is processed or not .it checks flag/status column have null or flag <> 'processed' then read it .

Polling SQL: SELECT cardNum, userName, validity, amountLimit, city, flag, cust_id FROM card_details10k WHERE ((flag <> 'processed') OR (flag IS NULL)) FOR UPDATE

Post SQL: UPDATE card_details10k SET flag = 'processed' WHERE (cardNum = #cardNum)