Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Posts
    22

    Unanswered: Functionally locking a row

    Hi, I'm sure this has been discussed here many times, though I'm at a loss to come up with some reasonably unique keywords to search for.

    Using: SQL Server 2008, Visual Studio 2008, .NET Framework 3.5

    I have a single table containing several columns including a TINYINT column named "status".

    In a mulituser environment, I want users to be able to retrieve a unique row.

    I want to be able to retrieve a single row where status=0, and set status=1 to prevent another instance from retrieving the same row.

    I have done this in the past where multiple users can be identified with a userid. I simply do the following;

    UPDATE table SET userid=@userid, status=1 WHERE status=0 AND id=(SELECT MIN(id) FROM table WHERE status=0)

    I can then do a SELECT WHERE userid=@userid to get the row. This works fine in a multiuser environment.

    I now have a situation where I have no identifier for the user. So I guess I have 2 questions;

    1. Is there an alternate id I can use? Session id might not cut it being that all users will be calling through the same tier. Timestamp isn't elegant.

    2. What would be considered current best practice? Applicable design patterns?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    GUID?
    Why don't you like like timestamp?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you tell us why you want to limit the number of users that can see a particular record?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2009
    Posts
    22
    Quote Originally Posted by pootle flump
    GUID?
    Why don't you like like timestamp?
    Thanks for your response. I don't like the idea of using the timestamp because in theory multiple events could occur at the same (at least within the presicion of the timestamp).

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - you know that timestamp has another meaning to old fogies like me in SQL Server? It is now known as "rowversion" (a rather sensible name change).

    Having said that, you would have to have an incredibly high transaction rate to make a clash very likely with datetime2 data type.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2009
    Posts
    22
    Quote Originally Posted by Wim
    Can you tell us why you want to limit the number of users that can see a particular record?
    Each record is a unit of work for a user and we don't want multiple users working on the same task.

    The logic is basically: Give me the next job from the queue and make sure nobody else can be given the same job.

    I'm sure it's a common requirement, but I'm not sure how it might be referred to. "Queue" functionality perhaps?

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    scorpioTiger, I may be missing something so please correct me if I misstate what you want.

    You have a task table with columns like unique id, Status and others.
    Multiple people can view the contents of this table.
    While viewing the result of the select, a user determines they will work on task X and you want to set the status = 1 (being worked).

    If that is all true can you:

    Select from the table.
    User1 decides to work on Task Y
    Update statement is: Update TaskTable Set status = 1 where Uniqueid-col = Uniqueid value and Status = 0.

    If another User2 decided to work on Task Y before User1 and had already successfully updated the Status, User1 update would not find a row to update (it is now equal to 1 and NOT 0).
    Handle the error message and tell User1 that Task Y is already being worked and to pick another one.

    Does this work for your situation?

  8. #8
    Join Date
    Jun 2009
    Posts
    22
    Quote Originally Posted by Stealth_DBA
    scorpioTiger, I may be missing something so please correct me if I misstate what you want.

    You have a task table with columns like unique id, Status and others.
    Multiple people can view the contents of this table.
    While viewing the result of the select, a user determines they will work on task X and you want to set the status = 1 (being worked).

    If that is all true can you:

    Select from the table.
    User1 decides to work on Task Y
    Update statement is: Update TaskTable Set status = 1 where Uniqueid-col = Uniqueid value and Status = 0.

    If another User2 decided to work on Task Y before User1 and had already successfully updated the Status, User1 update would not find a row to update (it is now equal to 1 and NOT 0).
    Handle the error message and tell User1 that Task Y is already being worked and to pick another one.

    Does this work for your situation?
    Thanks. Pretty close to what I'm looking for and you're aware of the implications of a multiuser environment. In this current situation, there is no user id, I simply have to provide a unique row to each request. This being the case, I need a way to identify the row I've just "locked".

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Several options:
    Generate a GUID. Use that GUID in place of the userID.
    Use the rowversion data type. Return the rowversion value on updating your status column using the OUTPUT clause.

    I would term it "disconnected concurrency management".
    Last edited by pootle flump; 06-04-09 at 09:50.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I'm going to recommend rowversion - it does more than you actually require for this spec, but it does allow you to do more sophisticated concurrency management in the future if required (i.e. you can guarantee whether or not a row has been modified in between your "locking" and subsequent update).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2009
    Posts
    22
    Quote Originally Posted by pootle flump
    Actually, I'm going to recommend rowversion - it does more than you actually require for this spec, but it does allow you to do more sophisticated concurrency management in the future if required (i.e. you can guarantee whether or not a row has been modified in between your "locking" and subsequent update).
    Thank-you Mr Flump. I must confess my ignorance of rowversion. I shall read up and see how I go with implementing it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •