Post reply

How to lock rows for update?

  • August 27, 2004 at 11:02 pm

    #109428

    Hi everybody,

    I have a querry like that:

    UPDATE tblGood SET userID = @userid, DateUpd = @DateUpd

    WHERE ID in ( SELECT top 5 ID from tblGood where userID is null&nbsp

    The problem is:

    when the user a and user b connect at the same time, they will get the same

    IDs in the select querry. I intend to use transaction, but it will cause the deadlock if there are about 70 users connect!

    Can anyone show me How to make them not select the same IDs without deadlock?

    I greatly appreciate your help!

     

  • August 28, 2004 at 12:59 am

    #520971

    UPDATE tblGood with (rowlock)  SET userID = @userid, DateUpd = @DateUpd  WHERE ID in ( SELECT top 5 ID from tblGood where userID is null 

  • August 31, 2004 at 1:05 am

    #521158

    Thank you Juli,

    I will try it!

     

  • September 5, 2004 at 11:44 pm

    #521804

    Hi all,

    I tried used row lock in Update querry, but it still has problem, some user get the same ID to update and it causes errors.

    I try another way like that:

    UPDATE tblGood with (rowlock)  SET userID = @userid, DateUpd = @DateUpd  WHERE ID in ( SELECT top 5 ID from tblGood with(readpast) where userID is null&nbsp

    But it still has the same problem!

    Could anyone show me the reason why? And how to fix it?

    Thank you so much!

     

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

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