2

For example, there is one table T1 with clustered index. I have two sessions and I want to have session one return one record and session two returns another record. Is it possible?

I tried following codes in two sessions but the table scan will block another session.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(UPDLOCK) ORDER BY NEWID()
-- ROLLBACK TRANSACTION

1 Answer 1

0

Yes. You can achieve it using READPAST hint. But you will need to make sure that your query is reading on row level. You need review your indexes and order by. The order by clause should be on an indexed column.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(READPAST, UPDLOCK) ORDER BY IndexedColumn
-- ROLLBACK TRANSACTION

Here the extract from the documentation:

READPAST

Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks. READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified.

READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes. READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.