While the given request will not give the row to multiple threads due to the implicit transaction that is being created, this can cause problems when one thread manages to confuse the queue. If you want to stop this, you can add the READ PAST and ROW LOCK hints to the query. This prevents the lock that one thread receives from blocking the other threads from receiving the string.
For instance:
UPDATE TOP (1) CatQueueItem WITH (READPAST, ROWLOCK) SET ClaimedBy = 'Mittens' OUTPUT inserted.CatQueueItemIdId into @test WHERE ClaimedBy is null
Long explanation
All SQL Server statements work in the context of a transaction. If the statement is executed and there is no transaction specified, SQL Server creates an implicit transaction for that single statement only.
There are three main types of locks that SQL Server uses Shared (S), Update (U), and Exclusive (X). Locks are acquired as part of the Transaction. If a transaction has an S lock on a row, other transactions can get an S or U lock, but not an X lock on the same row. If a transaction has a U lock on a row, other transactions can only get S-locks on that row. If a transaction has an X lock on the row, no other transaction can get a lock on the row. In order to write to a string, a transaction must have an X lock, as this leads to blocking all other transactions from reading the row, while it is partially able to update it.
This gives the following compatibility table:
SUX ---------- S | YYN U | YNN X | NNN
The update contained in the question has two parts. First he reads the table to find a row with a null value in ClaimedBy. When it finds a string, the second part of the operation will update the found string.
Normally, when reading from tables, SQL Server uses S locks because they do not allow other transactions to read rows and improve read performance, but this stops other transactions, getting X locks for writing to rows. The problem is that when the second part of the update request tries to switch to X lock, so it can be written to a string, this can lead to a lock. The reason for this is because the first part of the request in another transaction may have acquired an S lock in the same way as a transaction, but may not have updated it yet. This prevents your transaction by updating its lock to lock X, and your S-lock also prevents another transaction. None of the transactions can succeed so that they come to a standstill. In this case, SQL Server selects one transaction and rolls it back.
To stop the deadlock, SQL Server uses U locks when executing the read part of the update statement. U locks allow other transactions to acquire S locks, allowing those who read to succeed, but do not allow other U locks. Using U lock, you say that you are reading only, but you have the intention of writing at some point in the future. This prevents a situation where you have two transactions that are trying to upgrade to X lock. Thus, a transaction that has a U lock can upgrade to X in security, knowing that it will not be blocked by another transaction by doing so.
The relevance of all this to the scenario asked in the question is that the U locks used by a single thread transaction to block rows when looking for an available block of rows from all other thread flows. This is because when a transaction tries to get a lock on a row that already has an incompatible lock, it just waits for the queue until the lock lock is unlocked. Since all threads look for the same lines for free, they all try to get U locks on the same line, and they all form in an ordered queue, waiting for U locks on the same line. In other words, only one thread transaction allows you to search for free rows at a time.
What is the table hint READPAST, it stops the transaction queue to read rows in the table. With READPAST, when a transaction scans to get a lock on a row that is already locked, instead of joining the queue to lock, it says this and goes and tries the next row. In this case, he will say that I do not know if the string has a value of ClaimedBy or not, I am not ready to wait to find out, so I just assume that it is, and try the next line. This may mean that it skips the available lines, but does not get the line inaccessible. This will improve the speed with which threads and their transactions can receive items from the queue, since they can all search for available rows at the same time.
Purchasing locks can be quite expensive. It takes time and memory. To combat this problem, SQL Server has several features for locks. You can lock the entire database, a whole table, a table page, or a table row. The query optimizer will try to use statistics to predict how many rows need to be blocked. If there are many, he will choose to lock the page or table instead of blocking the rows. This results in fewer locks in general.
The ROWLOCK table hint tells SQL Server not to use these coarse-grained locks and only use row locks. This is an advantage in this case because it stops large chunks of available rows skipped by transactions that look for available rows.