Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Question Unanswered: Database concurrent access issue

    Dear All,

    I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,

    Entry 1: [1, 2]
    Entry 2: [3, 8]
    Entry 3: [9, 10]
    Entry 4: [11, 11]
    Entry 5: [12, 20]

    The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll have to explain that better.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Posts
    84
    For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.

    The winning number will be drawn from the number range.


    Quote Originally Posted by blindman
    You'll have to explain that better.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Clear as mud.

    If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time....

    qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance

    Code:
    create procedure CheckIt (@StVal int, @EndVal int)
    as 
    
    BEGIN TRANSACTION
    declare @CheckFailed char(1)
    set @CheckFailed = 'N'
    if exists (select * from MyTable with (tablockx) where @StVal between StartVal and EndVal)
        set @CheckFailed = 'Y'
    if exists (select * from MyTable where @EndVal between StartVal and EndVal)
        set @CheckFailed = 'Y'
    
    if @CheckFailed = 'Y'
    begin
        ROLLBACK TRANSACTION
        return 0
    end
    else 
    begin
        insert into MyTable ......
        COMMIT TRANSACTION
        return 1
    end

Posting Permissions

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