Reg：sql server两个用户一次编写数据 [英] Reg:sql server two users manuplating data at a time

问题描述

当多个用户同时尝试修改数据时

Ex用户1同时更新记录user2已删除

记录成功。现在用户1有按更新按钮。

如何处理并发

解决方案

在您的代码中使用交易。参见下文：

   -   更新 
   -   由USER1执行 
  BEGIN   TRANSACTION  
  BEGIN  TRY 
 更新 [员工]  SET 薪水=薪水*。 5   WHERE 薪资> 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 
 
 < span class =code-comment>  -   对于删除 
   -   由USER2执行 
  BEGIN   TRANSACTION  
  BEGIN  TRY 
  DELETE   FROM  [Employee]  WHERE 薪资> 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 

在这种情况下，当USER1运行时UPDATE命令它会将一个EXCLUSIVE LOCK放到Employee表中，因此USER2的DELETE命令将一直等到USER1的UPDATE命令完成。

SQL Locks 可以避免这种并发问题。



请在此处阅读：

MSDN：了解SQL Server中的锁定 [ ^ ]

MS SQL Server中的事务的锁和持续时间 [ ^ ]



这里对类似问题的几个答案也有帮助：更新数据时出现问题 [ ^ ]

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

解决方案

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.

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[^]

这篇关于Reg：sql server两个用户一次编写数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！