SQL Server Performance

Write conflict...

Discussion in 'T-SQL Performance Tuning for Developers' started by cbtoolkit, Dec 22, 2004.

  1. cbtoolkit New Member

    Okay, I need quick help here...

    As mentioned prior, I created a stored proc to bring up records 1 by 1 as users request the next record in a queue. However, here is the problem...

    I do this in a stored proc and inside a transaction.

    I write a lock and date time on the record in the transaction and check for success. If it is successful, I commit the transaction and return a unique key. This is returned from the stored proc and then used to open that record for the user.

    I am going to post my code.


    CREATE PROCEDURE wmspGetNextRecdefaultGroup (
    @user int, @date datetime, @return int OUTPUT
    ) AS
    declare @recno as integer
    declare @error as integer
    declare @rowcount as integer
    set @return = 0

    begin transaction

    Set rowcount 1
    Select @recno = callList_uno from vwCallList_TZ
    Where
    IsComplete <> -1 And CanCall = -1 and (LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
    And (
    IsInactive = 0
    or (IsInactive = -1 and days_120 > 0)
    )
    And (CharIndex('S@',STAALL) + CharIndex('GC',STAALL)=0)

    ORDER BY CallCount, delTotal DESC, billingname
    set rowcount 0
    UPDATE tblCallList Set LockDateTime = @date, user_cd = @user
    WHERE calllist_uno = @recno

    select @error = @@error, @rowcount = @@rowcount

    if @error = 0
    begin
    commit tran
    select @error = @@error, @rowcount = @@rowcount
    end

    else
    begin
    rollback tran
    set @recno = 0
    -- set @return = 0
    end
    Set @return = @recno
    return @return
    GO


    I use the first select to ensure that the queue is ordered properly. The ordering works properly but apparently if multiple users hit the system simultaneously, it allows each to retrieve the same ID. This is very, very bad..

    How do I remedy this? How can I ensure that if 20 users hit the system simultaneously, that each retrieves their own unique record?

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  2. Twan New Member

    I'd suggest something like the following

    Cheers
    Twan



    CREATE PROCEDURE wmspGetNextRecdefaultGroup (
    @user int, @date datetime, @return int OUTPUT
    ) AS
    declare @error as integer
    declare @rowcount as integer
    set @return = 0

    begin transaction

    Set rowcount 1

    UPDATE vwCallList_TZ
    Set
    @return = callList_uno,
    LockDateTime = @date,
    user_cd = @user
    Where
    IsComplete <> -1
    AndCanCall = -1
    and( LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
    And( IsInactive = 0 or ( IsInactive = -1 and days_120 > 0) )
    And( CharIndex('S@',STAALL) + CharIndex('GC',STAALL)=0)
    ORDER BY
    CallCount,
    delTotal DESC,
    billingname

    set rowcount 0

    select @error = @@error, @rowcount = @@rowcount

    if @error = 0
    begin
    commit tran
    end
    else
    begin
    rollback tran
    set @return = 0
    end

    return @return
    GO


    Cheers
    Twan
  3. cbtoolkit New Member

    Thanks. Originally, I started with the update, as above, but I get an error on the ORDER BY...

    Is there a way arouund this because it must select records by an internal callcount and then by the amount of money owed. After a call is made on an account, it is either completed, dropping it off the list, or its callcount is incremented and it drops to the bottom of the list.

    I suppose I could order the view that I am updating/calling, vwCallList_TZ?

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  4. cbtoolkit New Member

    Just tested attempting to order the view, vwCallList_TZ and remember why I could not. When ordered, it adds the TOP 100 Percent to the select clause. The recordset does not allow writes when this is the case??

    Oh, woe is me.

    Any suggestions?

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  5. Twan New Member

    Hi Matt,

    ah yes sorry the joy of writing sql without being able to test it...

    You'd probably need to use a subselect

    as in



    UPDATE tblCallList
    Set@return = tblCallList.callList_uno,
    LockDateTime = @date,
    user_cd = @user
    from tblCallList
    inner join ( select top 1 calllist_uno
    fromvwCallList_TZ
    WhereIsComplete <> -1
    AndCanCall = -1
    and( LockDateTime < DateAdd(n,-180,@date) or LockDateTime is null)
    And( IsInactive = 0 or ( IsInactive = -1 and days_120 > 0) )
    And( CharIndex('S@',STAALL) + CharIndex('GC',STAALL)=0)
    ORDER BY
    CallCount,
    delTotal DESC,
    billingname
    ) as vwCallList
    on vwCallList.calllist_uno = tblCallList.calllist_uno


    Cheers
    Twan
  6. cbtoolkit New Member

    Ah ha moment! Maybe...

    As I looked over my code, I realized something. I am going to test but confirm if you are able.

    In my transaction,

    I do a select, limiting my result to records without a current lockdatetime (or null lockdatetime). I then use the record number (unique key) returned to update the lockdatetime of that record - and commit the transaction.

    I then return the record number to my application, which then opens the form to that "locked" record.

    Here is why I am running into problems.

    Two individuals can be running the SP, return the same unique key and in fact successfully write to the same record, because, in my update statement, I do not specify any information about the lockdatetime - only the unique id!

    The transaction can be successul in this case and have two users write to the same record. That is bad.

    Twan, your recommendation above looks like it is the way to go. I could alternatively, add the lockdatetime where clause to my update but that is requiring two distinct queries. I do not know the difference - performance wise, of a sub-select versus two different transact-SQL statements.

    Am I right (write)?

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  7. Twan New Member

    Hi Matt,

    adding lockdatetime to the update statement will prevent a 2nd user from updating the same row, but it won't solve the chance that two users running the proc at the same time both have the same record number returned.

    as select statement doesn't stop another user also doing the same select at the same time

    Cheers
    Twan
  8. cbtoolkit New Member

    Understood. I actually got it to work using an isolation level of readrepeat. If there is a lock on the record, it returns 0 and the process is retried. With several people hitting the DB, 5-7 seconds is the most time we have had to wait for a record. I am playing with some indexes and some other rowlocking.

    I am using the sub select statement as you indicated above.

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com

Share This Page