Click here to Skip to main content
14,331,456 members
Rate this:
Please Sign up or sign in to vote.
See more:
When multiple users attempt to modify data at the same time
Ex user 1 is updating record at same time user2 deleted
record sucessfully.now user1 has press update button . how
to handle concurrency
Posted
Comments
KASIMSETTY LAVAN 26-Feb-13 6:03am
   
When multiple users attempt to modify data at the same time
Ex user 1 is updating record at same time user2 deleted
record sucessfully.now user1 has press update button . how
to handle concurrency
Shanalal Kasim 26-Feb-13 7:23am
   
We can't handle that case so show a message in update like this "Selected record deleted by another user"
   
The question is not very clear but the transactionality is what ensures that there are no simultaneous access to data.

http://msdn.microsoft.com/en-us/library/ms188929.aspx
Rate this:
Please Sign up or sign in to vote.

Solution 2

Use transaction in your code.See below:

--For Update 
--Executed by USER1
BEGIN TRANSACTION
BEGIN TRY
	UPDATE [Employee] SET Salary=Salary *.5 WHERE Salary>1000
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
		SELECT
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_LINE() AS ErrorLine,
			ERROR_MESSAGE() AS ErrorMessage
	
END CATCH

--For Delete
--Executed by USER2
BEGIN TRANSACTION
BEGIN TRY
	DELETE FROM [Employee] WHERE Salary>1000
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
		SELECT
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_LINE() AS ErrorLine,
			ERROR_MESSAGE() AS ErrorMessage
	
END CATCH


In that case when USER1 runs UPDATE command it will put an EXCLUSIVE LOCK to the Employee table so the DELETE command of USER2 will wait until the USER1's UPDATE command to be finished.
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

SQL Locks can avoid this concurrency issue.

Have a read here:
MSDN: Understanding Locking in SQL Server[^]
Locks and Duration of Transactions in MS SQL Server[^]

Couple of answers to similar question here that too will help: Problem in updating data[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100