getting duplicates using for update lock in concurent access

[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

Any help is appreciated!

[former_member188958]

Here is an example of the feature working.

Note that the sp_configure option "select for update" must be set to 1

In one session, execute the following:

use test<br><br>go<br>create table card_details (cardNum int, userName varchar(20), flag varchar(20) null ) lock datarows<br>go<br>insert card_details values (1, "a", "processed")<br>insert card_details values (2, "b", "new")<br>go<br>begin tran<br>go<br>select cardNum from card_details where flag <> "processed" for update of flag<br>go

-- This should return "2" and now be waiting for your next statement.

-- In a second session,, execute the following:

use test<br>go<br>insert card_details values (3, "c", "new")<br>go<br>begin tran<br>go<br>select cardNum from card_details where flag <> "processed" for update of flag<br>go

-- This session should now be blocked until the first commits or rolls back it's transaction

--In a third session, to see the effect of the READPAST feature, execute the following:

insert card_details values (4, "d", "new")<br>go<br>begin tran<br>go<br>select cardNum from card_details readpast where flag <> "processed" for update of flag<br>go

-- This session should return "3" and "4" and now be waiting for your next statement.