6 thoughts on “SQL SERVER – Concurrency Basics – Guest Post by Vinod Kumar

  1. Pingback: SQL Server: Locking basics | Vinod Kumar (Blog home)

  2. Pingback: SQL Server: Database Basics | Vinod Kumar (Blog home)

  3. I’m facing a problem on concurrent update happen on same row.

    Situation:
    I have a queue of records. The user will pick a record from queue, process and submit the record. There will be more than 100 concurrent users process the queue.
    The time taken for processing a records is a sec. So the simultaneous request to the queue will be more.
    A user will pick a random record from queue and mark the record as “P” for processing and mark “D” as complete after processing.
    The problem is multiple user pick the same record and update the same record as “P” for more than one user.

    Select Top 1 @QueueId = ID
    From Queue
    Where Status = ‘ND’ (Not picked)

    Update Top(1) DataQueue
    Set Status = ‘P’
    Where UniqueId = @QueueId And Status ‘P’

    We believe that only one update happen at same time for a row. But one or more users can update the same row at same time.
    How to prevent the multiple users can update the same record at same time?
    Give me suggesstion on this pinal dave or vinoth.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s