June 7, 2021 at 5:27 pm
I acquired a exclusive lock on a table1 for a specific record as below.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT *
FROM TABLE1 WITH (XLOCK,ROWLOCK)
WHERE (FIELD1+FIELD2) = ('0101R001');
How to get unlocked records from table1.
When used with readpast as below, the results are inconsistant (displays all records). Is there a way to identify the unlocked records alone from table1 ?
SELECT * FROM TABLE1 WITH (READPAST)
June 7, 2021 at 7:50 pm
READPAST is what you would use to read only the "unlocked" (ie rows without a lock) on them as you did.
Where your problem MAY reside is in the isolation level of your READPAST select statement. Re-run your queries with BOTH in the REPEATABLE READ isolation level, in 2 separate query windows, and it should skip over any row that is locked.
The Isolation level, when run in a query, is applied to the current session (ie window or tab). In order for that READPAST to NOT read from the locked rows, you would need to run it in a separate session as the lock is held by the session.
One risk you run with the above is you NEED to make sure to commit or rollback your open transactions or your data can may get stuck. Fun part about your above query is if you run DBCC OPENTRAN on it, it will tell you there are no open transactions EVEN THOUGH you have one (at least on SQL Server 2016).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 8, 2021 at 9:10 am
Yes I executed the second query as below in another SSMS session, and the query result that I got was inconsistent as it displays all records.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM TABLE1 WITH (READPAST)
Only when I use where clause involving the primary keys to check for specific record, it skips the locked record which is an expected behaviour. If no where clause, it displays all records (incl locked records).
June 15, 2021 at 3:43 am
This reply has been reported for inappropriate content.
Life is a journey, you don’t care about the destination, you care about the scenery along the way and the mood of seeing the scenery.
Others laughed at me as crazy, I laughed at others and couldn't see through.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply