Luckly since SQL Server 2005 with the advent of the OUTPUT clause, a much more elegant solution is available, and now MSDN recommends using the OUTPUT clause: Basically there are 3 parts of the puzzle you need to get right in order for this to work in a highly concurrent manner: 1) You need to dequeue atomically.
I would stay away from messing with locks in a table.
However, if there are multiple workers, each tries to get the row with ID=2.
Only the first will get the pessimistic lock, the rest will wait. What query/configuration would allow each worker client to get a different row each and work on them concurrently?
Base your update status on the Version and the primary key columns.
If the update fails then someone has already been there.