Concurrency problem............
-
Hello Friend I have faced a problem in SQL Server I have a stored procedure which works as follows Declare @Id int Select @id=max(id) from emp Set @id=@id+1 update emp Set id=@id return @id Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment, but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id. How can I solve it, so that every time the procedure returns unique value for each user. Is there any technique to execute this stored procedure in queue. Thanks in advance
-
Hello Friend I have faced a problem in SQL Server I have a stored procedure which works as follows Declare @Id int Select @id=max(id) from emp Set @id=@id+1 update emp Set id=@id return @id Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment, but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id. How can I solve it, so that every time the procedure returns unique value for each user. Is there any technique to execute this stored procedure in queue. Thanks in advance
Hi, I would suggest that you reconsider the logic in your procedure from multi-user point of view. However, if you want to prevent simultaneous operations with this structure, simply start with the update to get an exclusive lock. For example:
DECLARE @Id int
UPDATE emp SET id=(SELECT MAX(id) FROM emp);
SELECT @id=MAX(id) FROM emp;
RETURN @id;Mika