CODEWITHSUNDEEP
sqlsql-server
NullReference
NullReference

Reputation: 4484

How would turning on Read commit snapshot affect Data Queues using WITH (ROWLOCK, READPAST)?

We're considering turning on read committed snapshot isolation level to help with some of our table locking problems.

We have a few stored procedures that use the table hint WITH (ROWLOCK, READPAST) as a queuing based table setup. This prevents multiple worker roles from reading the same row.

I'm a little concerned that the read committed snapshot could potentially break the queuing system and more than one worker role could read the same row. Does anyone know if turning on RCS isolation would break this process?

WITH  q AS
(
    SELECT TOP Column1
    FROM Table1 c WITH (ROWLOCK, READPAST)
    WHERE c.NextAttemptDate <= GETUTCDATE() 
    ORDER BY c.NextAttemptDate ASC
)
UPDATE  q
SET OperationStatusType = 8
OUTPUT inserted.Column1

Upvotes: 3

Views: 190

Answers (1)

usr
usr

Reputation: 171206

An UPDATE takes U-locks on rows of the target table while it checks whether they match your filter or not. After that the lock is either released or converted to an X-lock further down the query pipeline.

It is not possible to affect write locking using locking hints or the isolation level. You are safe. U-locks will be taken.

I'm in the habit of always adding WITH (UPDLOCK) redundantly as well so that this behavior is documented in the code.

Upvotes: 3

Related Questions