This code gets the first record available for updating (TOP 1, IsUpdated = 0) and does some processing, finally updates the record. This code does not allow others to modify the record while it is being accessed, avoiding inconsistent analysis and lost updates. But it reduces the concurrency. It does not allow other to get the second available record too, making a deadlock situation if the same is executed by another connection. There is no harm of letting others to access the second available record. How do we do it? This cannot be implemented with REPEATABLE READ isolation level. All we have to do is, use UPDLOCK and READPAST query hints. UPLOCK puts exclusive lock on the object and does not allow others to obtain a lock on the same. READPAST skips all locked records and read the non-locked records. Here is the corrected code;
This locks the record while it is being accessed but allows to process the next available record for others.