0

In SSMS, in one session, I acquired a exclusive lock on a table1 for a specific record as below.

Session1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT * FROM TABLE1 WITH (XLOCK,ROWLOCK)
WHERE (FIELD1+FIELD2) = ('0101R001');

In another Session2

How to get unlocked records from table1.

When used with readpast as below, the results are inconsistent (displays all records). Is there a alternative ways to identify the unlocked records alone from table1 ?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM TABLE1 WITH (READPAST)
Dale K
  • 25,246
  • 15
  • 42
  • 71
sairam2021
  • 1
  • 2
  • `XLOCK` does not lock in a `SELECT` query if there are no changes, see https://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations, so you are not actually locking it in the first place, you need `UPDLOCK` for this. What are you actually trying to achieve by locking it? – Charlieface Jun 08 '21 at 11:07

1 Answers1

0

Before modification, we acquire a xclusive lock and we do some processing on those fetched values and then initiate an update statement followed by commit. During this time, no other process should change the values of that record. Using XLOCK, was able to get an exclusive lock on that record. This is working. In the select statement, if we change to WITH (UPDLOCK), it allows other process to read that record values which we want to restrict.

With Xclusive locks in place, if we use (READPAST) along with where clause involving primary keys to check for specific record, it skips that locked record which is an expected behaviour. If no where clause, it displays all records (incl locked records).

SELECT * FROM TABLE1 WITH (READPAST)

sairam2021
  • 1
  • 2