SQL Server Performance

queuing records - ensuring one record per user

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by cbtoolkit, Dec 8, 2004.

  1. cbtoolkit New Member

    Hello all...

    I have an MS Access front end that provides call information to a call center. There are 40 users who access only about 40,000 records. The users are split into different call groups.

    Call groups pull up different records from the same master table based on criteria in the record (region, dollar amount, special codes, etc.)

    It is possible for a given record to end up in more than one call group based on its criteria.

    Here is the basic process:

    User enters call system

    Opens form which queries for the "next" record (next, uncalled, unresolved record);

    A unique key field is pulled out of the database and then set as part of the recordset property for the form.

    The record is stamped in a time/date field and also with the user's ID to signify that it is open. This ensures that it does not appear in future queries on the database - so that the "next" users receives the next record.

    However, if multiple users in the same call group hit "next record" simultaneously, it is possible that the same unique key field, hence, the same record will be returned and then opened. This results in a "Write Conflict" error when the second user to open the record goes to save their changes.

    How can I ensure that when a user goes to get the next record, that record is locked from the time they query the DB - write their stamp to the record - open the record, and finally save it.

    If they open the record, my stamp ensure it drops off any call queues. It is specific to a request for the next record simultaneously.

    I hope I was clear. Any help is appreciated.

    Thanks,

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

    Hi ya,

    do the whole operation in a stored proc, then you can

    begin a transaction

    update the record, copying the id into a local variable
    update blah set @lv = id = id, userid = user_id(), etc.
    select the record just updated using the local variable
    select xxx from blah where id = @lv

    commit transaction (or rollback if there is an error)

    Cheers
    Twan
  3. cbtoolkit New Member

    This is more of an underlying process question:

    How does this ensure that the record identified in the transaction will not be affected by the same stored procedure started by another user?

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

    The transaction puts any subsequent request by a parallel process into hold, until the transaction is committed - then the parallel process gets its turn.
  5. Chappy New Member

    What about just using an auto_increment field ?
  6. Twan New Member

    Hi ya,

    if I understand the problem right, there is a table of rows that need to be processed and you want to pick a row out of this 'queue'. To ensure that the same row isn't picked up by two processes, you flag the row to say that is being processed.

    so an auto-inc field isn't going to help in this case, since the rows are already there.

    options really are:
    do the select and then the update but make sure that a transaction is wrapped around it and either the select uses xlocks or isolation level is read repeatable

    do the update and then the select, again within a transaction

    as Adriaan mentioned the effect of the transaction is to serialise access to that row until both the select and the update have taken place. There is no way that another process can get in between

    Cheers
    Twan
  7. Chappy New Member

    Ah Twan, youre quite right. I was under the impression the users were creating the records at the same time, but after rereading this isnt the case <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  8. cbtoolkit New Member

    Thanks Twan.. You've got it right.

    I played around with it yesterday and will be testing it today.

    Thanks for your assistance.

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

    Twan and others...

    I am just getting back to this. Is this the basic idea? I am fairly new to the stored-procs but have created some simple select sp's.

    The stored procedure will return a unique key based on a record being available. It will stamp the record with the current date/time, removing it from other queries, and put the user's ID in the record as well. The reason I do this, is that if the timestamp is more than 5 minutes old, the record becomes available again and will not be locked in the case of a system crash on the part of a user.


    =================================
    Stored procedure (sp_lockandreturn)

    Received a variable which will specify the query criteria (I have several call groups that have different record criteria);

    Begin a transaction
    - return the first record matching the criteria;
    - stamp the record with the date, time, and user;
    - if update is successful, return unique key, if not successful, rollback trans and return a 0
    End the transaction
    - return the unique key for this record
    =================================

    Once returned, the form will open the record specified by the unique key returned.

    When the form is open, it re-stamps the record every 2 minutes to ensure it stays any queries being run by other users.

    So, here is a related question...

    I am only interested in returning a single record. If the transaction fails, it returns a 0 and I re-call the stored procedure. However, can I loop within the procedure until I get a record or until no records match the criteria? I would imagine that this would keep the processing at the server - rather than return to the client.

    Should I return multiple records, being the transaction, attempt an update, rollback on failure - move to the next record, update again, etc. inside of the stored procedure.

    This way, I am not querying the DB to return records multiple times - only once. Once the transaction succeeds I have my number and don't need to attempt any other updates. I leave the procedure.

    Am I on the right track? Does this make sense?

    Thanks.

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

    Hi Matthew,

    yep along the right track. I would update the record first and then return the value if it succeeds. You can set local variables to the user, date and time which will also give you a unqieu key to select the record back on

    i.e.

    set @date = getdate()
    set @user = user_name() -- or passed in
    set @return = null

    begin transaction

    update ...
    set date = @date, user = @user
    where ...

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

    if @error = 0
    if @rowcount > 1 -- not sure if you want to handle this?
    else if @rowcount = 1
    begin
    select @return id from ... where date = @date and user = @user

    select @error = @@error, @rowcount = @@rowcount
    end
    else

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

    return @error

    Cheers
    Twan
  11. cbtoolkit New Member

    Okay, upon some study but still in a psuedo code format:

    Begin Trans:
    - Return some records matching criteria;
    - if no records
    return (value indicating no records in this call list)
    - else
    set a record id to a variable (@recordid)
    Update tblCallList Set LockDateTime = (currentdatetime)
    If @@ERROR <> 0
    ROLLBACK TRAN
    Return (value indicating could not write lock)
    END

    Return @recordid (set to the unique key)
    COMMIT TRAN
    =====================

    Or something similar. The idea is to return the next available record, stamp it, if that fails inform the application so it can get another record. If it succeeds, return the record id value.

    The same question applies (above) about looping through multiple records inside of the stored procedure before returning to the client application. Thanks.

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

    We posted across each other. What you posted was very helpful. Sorry that I am belaboring this point but I want to make sure I understand...

    I'll begin the transaction by updating the record so that it potentially cuts down on the amount of work SQL does. In affect, I am assuming my update will work but will catch the exception - rather than return records and then perform my update.

    The idea is that if it works, only one record will have that unique time, date, and user stamped.

    Also, can I ensure that it updates only the next record. Multiple records should virtually never be updated at the same time. It is imperative that I return a single record (there is actually an exception but we can discuss that later).

    I know I can use a top 1 select statement. Can I do a top 1 on an update statement?

    UPDATE Top 1 tblCallList Set LockDatetime = DAte yada yada yada...

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

    Now my post are more like spam... sorry.

    No top in the update statement and I need to ensure only one record does get updates.

    However, It would seem that I can return the top 10 records and loop through it until one of the updates commits properly - and then return the value to my client application.

    If an end of file occurs, I could trap and return that as well.

    If none of my commits work, and it is not the eof, I could then re-call the stored procedure.

    I don't see a way around first retrieving records and then attempting to lock them with an update.

    As stated, it must always be a single record, not multiple records.

    And the query, at least to start, would include thousands of records. I can't risk updating the LockDateTime field on mutiple as this would result in all the other users retrieving 0 records.

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

    hi ya

    'set rowcount' is your friend. set it to 1 before the update and then set it back to 0 before the proc commits. This has the same affect as select top, but works for all DML

    Cheers
    Twan
  15. cbtoolkit New Member

    Interesting!!! In the SQL Server documentation, looking at Set Rowcount, it says...

    ===========================
    It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax.
    ===========================

    But when you look at the update command, it does not indicate that TOP can be used?

    I'll try the rowcount.

    Did you give thought to the idea of returning multiple records, attempting an update on them sequentially until successful, and then committing the transaction? Forcing it to eval a single record would mean the client would have to recall the stored procedure until it is successful.

    Thanks.

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

    Hi Matthew,

    I'd probably do the error handling in the calling app and yes calling the proc again if needed. There are some errors which you can't trap in the SQL side (e.g. deadlock, server gone down, etc.) So you'd have to deal with those in the front-end app anyway

    also the likelyhood of an error is going to be very very small, so there is unlikely to be a subsequent call to the proc from the app

    Cheers
    Twan
  17. cbtoolkit New Member

    Oh great SQL Swami, I am learning a thing or two about stored procedures - and I like what I am reading..

    However, I used your code above - with some changes, and was first receiving errors about declaring the variables I am using. Now the syntax checks out but I receive an error when I call this from the query analyzer...

    ==================== MESSAGE ======
    Server: Msg 266, Level 16, State 2, Procedure wmspGetNext, Line 34
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
    ==================== END MESSAGE ======

    Here is the stored procedure...

    I am using the row count and am forcing the @user variable. Once it is working, I will pass the variable. The return value s/b the record ID number.

    I am assuming, based on what I am reading, that it is a nesting problem. What I cannot determine is exactly how you next if..then statements in transact-sql for stored procedures. I am researching/learning this now.

    Any insight is appreciated.

    ====================
    CREATE PROCEDURE wmspGetNext AS
    declare @date as datetime, @user as integer
    declare @return as integer, @error as integer
    declare @rowcount as integer
    set @date = getdate()
    set @user = 25
    set @return = null
    Set ROWCOUNT 1

    begin transaction
    update tblCallList
    set LockDateTime = @date, user_cd = @user

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

    if @error = 0
    begin
    select @return CallList_uno from tblCallList where LockDateTime = @date and user_cd = @user

    select @error = @@error, @rowcount = @@rowcount
    end
    else

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

    set ROWCOUNT 0

    return @error

    GO


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

    Here is what is going on:

    The update works properly - no error - but the query to return records that have just been changed (1 record) returns nothing. At least not until after the commit statement.

    It would appear that I need to run the update, check for an error, commit the transaction, return the record number, and then exit the procedure.

    I'll test it and let you know.

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

    Final: It's working!

    The select @return is now
    set @return = (Select calllist_uno....)

    I commit the transaction prior to returning the value from the db to return.

    Thanks for getting me started down this road.

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

    Hi ya,

    the problem is that you have one else too many



    if @error = 0
    begin
    select @return CallList_uno from tblCallList where LockDateTime = @date and user_cd = @user

    select @error = @@error, @rowcount = @@rowcount
    end
    else

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

    Cheers
    Twan
  21. cbtoolkit New Member

    Thanks. I've now been adding several enhancements and created several other procedures for some other items in the system.

    Here is a question...

    Can I call CDO mail and other system objects using stored procedures. I have a client who was using SQL Mail but that requires a MAPI client on the SQL Server. They are moving the sql server (actually, putting up two) and I would rather not use SQL Mail and the SQL client as it requires an older (non-security patched) version of a MAPI client.

    I know this is a new topic but a quick reference would be fine. I am just starting to research this so maybe I'll have the answer shortly.

    Thanks again for your assistance.

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

Share This Page