在一个事务中SQL Server中的替代同义词 [英] Alternate synonym in SQL Server in one transaction

问题描述

我是Transact SQL编程的新手。

我创建了一个存储过程，它将删除并创建一个现有同义词，以便它指向另一个表。存储过程有两个参数：

• synonymName - 现有同义词


• nextTable -

  这是代码片段
   >  ... 
   BEGIN TRAN SwitchTran 
   SET @ SqlCommand ='drop synonym'+ @synonymName 
   EXEC sp_executesql @SqlCommand 
   SET @ SqlCommand ='create synonym'+ @synonymName +'for'+ @nextTable 
   EXEC sp_executesql @SqlCommand 
   COMMIT SwitchTran 
   ... 
    

  我们有一个应用程序可以定期使用同义词写入数据。

  
  
  

  我的问题是我会遇到竞争条件其中同义词被丢弃，而应用程序尝试写入同义词？

  
  
  

  如果上面是一个问题，有人可以给我建议解决方案。 >
  
  

  感谢

  解决方案

  p>
  
  

  一种管理方法是让 sp_getapplock 在事务模式下BEGIN TRAN之后，根据需要陷阱/处理返回状态。这将字面上串行化（在执行意义上，不是隔离）调用者，所以只有一个SPID在任何一个时间执行。

  
  

  I am new to Transact SQL programming.

  I have created a stored procedure that would drop and create an existing synonym so that it will point to another table. The stored procedure takes in 2 parameters:

  • synonymName - an existing synonym
  • nextTable - the table to be point at

  This is the code snippet:

  ...
  BEGIN TRAN SwitchTran
     SET @SqlCommand='drop synonym ' + @synonymName
     EXEC sp_executesql @SqlCommand
     SET @SqlCommand='create synonym ' + @synonymName + ' for ' + @nextTable
     EXEC sp_executesql @SqlCommand
  COMMIT SwitchTran
  ...
  

  We have an application that would write data using the synonym regularly.

  My question is would I run into a race condition where the synonym is dropped, while the application try to write to the synonym?

  If the above is a problem, could someone give me suggestion to the solution.

  Thanks

  解决方案

  Yes, you'd have a race condition.

  One way to manage this is to have sp_getapplock after BEGIN TRAN in Transaction mode and trap/handle the return status as required. This will literally serialise (in the execution sense, not isolation) callers so only one SPID executes at any one time.

  这篇关于在一个事务中SQL Server中的替代同义词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！