Post reply

Transaction deadlocks

  • December 18, 2008 at 11:03 pm

    #199160

    Hi there! I have a troubles with post's subject. Here is scenario: there is a table with 1 million records. Fields are Id, ParentId, State, JobId (foreign key), UserId (foreign key), CellId (foreign key), Data. State may have one of two states : "ready" and "inwork". Each user gets a record for work (some client side operation with Data value) and sends it back to server which have to update a record. When user gets a record (to prevent from others to get it too) the system updates a State field with "inwork" value and a UserId filed with current user's Id to know who received a work. The code is:

    BEGIN TRAN

    DECLARE @Id bigint

    DECLARE @State nvarchar(256)

    SET @Id = NULL

    UPDATE TOP(1) DataTable SET @Id = Id,

    @State = State,

    PrevState = @State,

    State = 'inwork',

    WorkTime = GETDATE(),

    WorkerUserId = @userid

    FROM DataTable

    WHERE State LIKE 'ready' AND JobId = @JobId

    AND CellId = CASE WHEN @CellId IS NULL THEN CellId ELSE @CellId END AND WorkerUserId != @userid

    IF @Id IS NOT NULL

    SELECT Id, PId, Data, @JobId AS JobId, @State AS State

    FROM DataTable

    WHERE Id = @Id

    COMMIT TRAN

    The problem is if there are two or more users and they want to get a work - I receive a transaction deadlock because (by sql profiler) they both trying to update the same record! I think its because of "hard" where clause and while first user starts to execute it the second one also can read an "old" values. AM I right?

    How can I build this mechanism (of getting works) to prevent such cases? Thank you.

    P.S. The same happens on saving work - update command takes too long time and while updating row (before completing) other user can get a work (deadlock too) because as I described every get work need to update a State.

  • December 18, 2008 at 11:39 pm

    #914988

    You've posted the question twice!!


    Pradeep Singh

  • December 18, 2008 at 11:43 pm

    #914990

    I know. Thanks.

  • December 19, 2008 at 12:17 am

    #915007

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic622591-9-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply