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!

[former_member188958]

Doesn't sound like the SELECT FOR UPDATE is happening in a transaction. Can you add a "select @@trancount, @@transtate" to verify that a transaction is open after the SELECT?

Also, I don't see how will help with concurrently - the SELECT FOR UPDATE will cause the first connection to hold exclusive locks, which will block the second connection when it tries to do the same SELECT FOR UPDATE until the first connection commits - at which point the first connection could simply loop and repeat the process - no need for a second concurrent process to alternate the work with.

Seems you should using the READPAST option as part of this approach (but you do have to get the SELECT FOR UPDATE working as well).

-bret

[former_member245994]

We tested "select..for update" behaviour in interactive sql tool

our observation is : "select..for update" is not blocking second connection establishment or second user to access same table with read query (Select)

Scenario what we have tested is:

1. first user :connected to db ---> begin transaction and then----> SELECT cardNum, userName, validity, amountLimit, city, flag, cust_id FROM card_details10k WHERE ((flag <> 'processed') OR (flag IS NULL)) FOR UPDATE

2. from second user: -->connected same db --> executed SELECT cardNum, userName, validity, amountLimit, city, flag, cust_id FROM card_details10k WHERE ((flag <> 'processed') OR (flag IS NULL)) FOR UPDATE

and second user is able to get records even though first user has not committed his transaction yet

status of select for update configuration : 1

So My query is "for update" provides lock to read operation..or it is only restrict for update based queries?

and second i tested with readpast : i am seeing same behaviour. please find below query

SELECT * FROM project READPAST WHERE ((project_name <> 'processed') OR (project_name IS NULL)) FOR UPDATE