Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.
Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.
The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.
You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP() cluse will be able to set the flag on at most one row. By using the OUTPUT cluse you can return this to the application automically.
I define a simple test table and populate it:
create table dbo.T(id int, IsProcessing bit default 0);
insert dbo.T(id) values (1),(2),(3),(4);
The output clause needs a table variable to receive the changed values:
declare @U table (id int);
A bit of debug code to make the "before" and "after" states obvious:
select 'Before' as B, * from dbo.T;
And the statement itself:
update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;
And the results:
select 'After' as A, * from dbo.T;
select * from @U;
This is the output from the second execution of the above.
B id IsProcessing
Before 1 1
Before 2 0
Before 3 0
Before 4 0
A id IsProcessing
After 1 1
After 2 1
After 3 0
After 4 0
id
2
The row with id=2 had gone from IsProcessing=0 to IsProcessing=1 and that id is returned in the table variable.
With this trivial data the rows are processed in the sequence they were created. In a more complex environment the optimizer may choose any row that matches the where clause. If you have a requirement to process rows in, say, time sequence further qualification will be required.
I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.
It is of course possible that there are no rows with IsProcessing=0 at the time the statement runs. In this case the table variable will have zero rows.
To fully isolate each transaction from the other you could try sp_getapplock. This will add overhead and reduce concurrency. You have to be careful to release the app lock as soon as you can in both success and failure scenarios.
Best Answer
You are correct, it is a signal to the engine that you wish to put a lock on an object. To over-simplify it, imagine you want to obtain a book from the library, if somebody has the book checked out you cannot obtain it, so you put your name on a list of people who intend to check that book out. When everybody else is done with it, and it is your turn, then you are allowed to check out the book.
If you haven't done so already I highly recommend reading Transaction Locking and Row Versioning Guide on MSDN which provides some great detail on how it all works.
The below is a snippet from that page: