From: Wilson Bilkovich <wilsonb@...>
Date: 2005-11-10T00:42:24+09:00
Subject: Re: Checking for race conditions with Ruby threads

Sorry to pollute the mailing list with SQL Server talk, folks. I beg
forgiveness in advance. :)

The application is (amongst other things) a data entry system for
volunteers working on hurricane disaster recovery.
They click the "Give me something new to work on" button, and the
system needs to find the first pending item of work, assign it to
them, and redirect them to a page where they can see scanned PDF
images, etc, etc.  The data is then hammered into a mainframe system
by distributed (Ruby) clients that hit a web page to find out which id
number to work next.

The original logic was:
Begin Transaction
@id = select min(id) from some_table where status_code = 'P' --(for pending)
update some_table set status_code = 'W' where id = @id
Commit

With default locking, two requests might select the same record before
the first update is issued. Because this happens inside a transaction,
the other transactions don't 'see' this pending change until they
themselves have committed.

READPAST, ROWLOCK, and XLOCK are the usual suggestions for this kind of thing.
However, a careful study of the SQL Server docs shows that XLOCK and
ROWLOCK are not compatible.  Only pages and tables can be locked with
XLOCK.   That would be OK, except the READPAST only works with row
locks, not with anything larger. Heh.

Because this system is live 24/7 right now, we can't make drastic
changes. However, it seems like (READPAST, UPDLOCK) on the select
statement, and then checking to see how many rows were updated in the
update statement has fixed it.  If no rows were updated (due to the
lock), then we just restart the transaction.  That seems incredibly
ugly to me, but luckily this system is going away soon.

That being said, I'm definitely not a SQL Server expert (I prefer
Oracle and Postgres), so if you can recommend a better solution, I'd
be in your debt.

Thanks,
--Wilson.

On 11/8/05, Sean O'Halpin <sean.ohalpin@gmail.com> wrote:
> On 11/9/05, Sean O'Halpin <sean.ohalpin@gmail.com> wrote:
> > On 11/9/05, Wilson Bilkovich <wilsonb@gmail.com> wrote:
> > > Unfortunately, this is a SQL Server application being used as a 'work
> > > queue', and SQL Server 2000 does not allow exclusive locks on rows.
>
> Look up ROWLOCK in SQL Books Online. You might also want the READPAST
> locking hint to skip locked rows held by other processes.
>
> Regards,
>
> Sean
>
>