一个事务中 SQL Server 中的备用同义词 [英] Alternate synonym in SQL Server in one transaction

问题描述

我是 Transact SQL 编程的新手.

I am new to Transact SQL programming.

我创建了一个存储过程，它会删除并创建一个现有的同义词，以便它指向另一个表.存储过程接受 2 个参数:

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 - 现有的同义词
• 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
...

我们有一个应用程序会定期使用同义词写入数据.

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.

谢谢

推荐答案

是的，您会遇到竞争条件.

Yes, you'd have a race condition.

管理此问题的一种方法是在 BEGIN TRAN 之后使用 sp_getapplock在事务模式下并根据需要捕获/处理返回状态.这将逐字地序列化(在执行意义上，而不是隔离)调用者，因此在任何时候只有一个 SPID 执行.

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屋！