How to prevent two or more session to select same data from table

Question

Mr @Erland Sommarskog answer this way in my another post

my purpose if two or more session is trying to select & update data from same table then they should not be able to fetch same data.

if session one acquire 10 rows then session two should be able to fetch those 10 rows rather session two should get next unlock rows.

  Declare @outputTable Table (col1 int, col2 int, col3 int);  

 ; WITH CTE AS (  
    SELECT TOP (5) * FROM sourceTable WITH (READPAST)  
    ORDER  BY col1  
 )     
   Update Top (5)  
          CTE  
      Set readFlag = 1  
    Output inserted.col1, inserted.col2, inserted.col3 Into @outputTable  
    Where readFlag = 0  

 SELECT * FROM @outputTable  

1) in sql there READPAST hint used. does it lock rows ?

2) when dumping inserted data into @outputTable ?

3) I am not able to understand how above code will solve my purpose?

4) if i used READPAST hint in select then what READPAST hint does ? please discuss the usage of READPAST like what it does and when to use it ?

looking for good discussion on this topic. thanks

Answer 1

Hi @Sudip Bhatt ,

I will provide an example about READPAST.

Create one table with 300 rows.

CREATE TABLE TESTTABLE  
(ID int,  
SALARY INT  
)  
   
DECLARE @i INT  
SET @i = 1  
WHILE (@i <= 300)  
BEGIN  
INSERT INTO TESTTABLE VALUES(@i, 100*@I)  
SET @i = @i + 1  
END  
  
SELECT * FROM TESTTABLE  

Then we could have 300 rows of data.

Run below update statement.

BEGIN TRANSACTION  
      UPDATE TOP(1) TESTTABLE  
      SET SALARY = SALARY + 1  

In another session, run below select statement.

SELECT COUNT(*)  
  
FROM TESTTABLE WITH(READPAST)  

Output:
299

This table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The READPAST does not lock rows. It tells SQL Server to just skip locked resources and not wait for locks to be released.

Here is a demo. In one window run:

CREATE TABLE dummy (id int NOT NULL PRIMARY KEY, name sysname NOT NULL)
INSERT dummy (id, name) SELECT object_id, name FROM sys.objects
SELECT COUNT(*) FROM dummy
BEGIN TRANSACTION
UPDATE dummy WITH (ROWLOCK)
SET   name = 'KilroyWasHere'
WHERE  id BETWEEN 1 AND 10
SELECT @@rowcount

Pay attention to the two counts. In a second window run:

SELECT COUNT(*) FROM dummy WITH (ROWLOCK, READPAST)

You will see that the COUNT(*) query is smaller than in the first window.

You may note that I have added the ROWLOCK hint. This was actually something I learnt the hard way. Initially my second COUNT(*) query was blocked despite the READPAST hint. This was because it was taking locks on page level. So adding ROWLOCK in this scenario can be a good idea to avoid accidents.

And, yes, I believe that Jeffery's query with my modification will need your needs. But we may have misunderstood what you were looking for. In any case, you should test this further on your own.