SQL Server Performance

Concurrency Problem

Discussion in 'SQL Server 2008 General Developer Questions' started by rw610, Oct 16, 2009.

  1. rw610 New Member

    This question must have been answered a hundred times but I cannot seem to find the answer.
    I have an application that needs to read work off a queue (the queue is a database table). There are many instances (hundreds) of the application running and every instance will be looking for the first item in the queue which has not yet been processed - the status column on the table = 0. I am not worried about the order in which the queue items are processed, I just want to pull back the first item and work with it but I must not pull back an item that is alreday being processed by another instance.
    I know that I can read an item from the queue and then 'lock' it by updating its status but how do I prevent another instance from reading this item and trying to work with it at the same time?
    My thanks for your help....
  2. satya Moderator

    Do you use any query locking hints for SELECT?
    http://www.sql-server-performance.com/reducing_locks.asp
    http://technet.microsoft.com/en-us/library/ms171845.aspx
    You may know DB engine supports 2 types of concurrency models,pessimistic & optimistic. Pessimistic is useful to block access to data that is used by another process at the same time, see the locking section items in BOOKS ONLINE too.Whereas optimistic works on assumption that a transaction is unlikely to modify data that another transaction is modifying at the same time.
    In general it is better to use NOLOCK hint whenever SELECT is passed on. http://technet.microsoft.com/en-us/library/ms187373.aspx and http://technet.microsoft.com/en-us/library/ms189857.aspx are good for reference.
  3. bseefelt New Member

    Take a look at the UPDLOCK and READPAST hints. That would allow you to lock the row at the time of select, and keep it locked for the duration of the transaction, but ignore rows locked by another process.
    SELECT TOP 1 * FROM table WITH (UPDLOCK,READPAST) WHERE status = 0
    HTH

Share This Page