In order to accommodate concurrency for a given table that will be under constant fire of both updates and selects I'm trying to introduce an efficient locking pattern on an Azure SQL DB. After investigating READPAST for selects and WITH (ROWLOCK) for updates I'm not seeing the results I would expect nor others are reporting. My select statement with READPAST always returns rows that are in the process of being updated. Consider my simple scenario:

Table [Job] : Id varchar(12) PK | CreatedOn datetime | StatusId smallint

I seed the Job table with 5 records and set the StatusId to 0 for all.

In one session I execute the following command without committing:

BEGIN TRANSACTION
UPDATE JOB WITH (ROWLOCK) SET StatusId = 1

In another session I execute the following command

SELECT * FROM Job WITH (READPAST)

The select statement executes and returns all 5 rows with their pre-updated StatusId (0). It's my understanding that READPAST is supposed to skip the rows which are locked. I have verified the lock is indeed present and granted using the sp_WhoIsActive proc.

What I am missing to get this scenario working correctly?

Thanks for your assistance.

CodePudding user response：

Azure SQL Database has the READ_COMMITTED_SNAPSHOT database option on by default. That uses row-versioning instead of locking for read consistency in the READ_COMMITTED isolation level so the SELECT query will return the pre-update row version until the UPDATE transaction is committed.

You can override the isolation level in the SELECT query with a hint like below:

SELECT * FROM dbo.job WITH(REPEATABLEREAD, READPAST);