Problem with RowLock
-
Hi everybody,
I have a querry 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)
The problem is:
when the user a and user b (or many users) connect at the same time, they will get the same IDs in the select querry. This will cause the error in Updating.
Can anyone know the reason why rowlock can not prevent the users select the same IDs?
Any your help is appreciated!
Regards
Hang Bui
Software developer
-
looks to me like your problem is that there is no demand on the part of your code, for the userids to match the real userids
to start with, your userids are null, so therefore we have no way of knowing who they are.
then you assign a userid to the top 5 ids. obviously these can be associated with more than one user, there is your problem.
you seem to be expecting rowlock to do something about it, but rowlock's only function is to stop other processes writing to the same row at the same time
-
Hi Danmorph! Thanks for your reply!
Yea, Because I dont care who they are, all I need to do is assigning a user with top 5 rows random
It's right, that's really my problem, those rows can be asscociated with more than one use! I was advised to use ROWLOCK to prevent it, but it seems not effective at all.
I think ROWLOCK also lock the processor reading the same rows at the same time! In your comment, it can not?
Do you know the other way to prevent it?
Regards
HangBui
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply