不中断地执行一个SQL块

本文关键字：一个 SQL 中断 执行 | 更新日期: 2023-09-27 17:53:00

假设我在Web应用程序中使用c#中的ExecuteNonQuery(DbCommand)执行以下SQL语句

DECLARE @InsertedProductID INT -- this is passed as a parameter
DECLARE @GroupID INT -- this is passed as a parameter
DECLARE @total INT
SET @total = (SELECT COUNT (*) FROM Products WHERE GroupID = @GroupID)
UPDATE Products SET ProdName = 'Prod_'+ CAST(@total as varchar(15)) 
WHERE ProductID = @InsertedProductID

我的问题是我想确保整个块在1执行。我的目标是每个组的ProdName都是唯一的。如果我让一切保持原样，那么如果在获取@total和执行UPDATE之间发生了insert，我很有可能会得到重复的产品名称。是否有一种方法可以确保整个SQL块在没有中断的情况下立即执行。exec或sp_executesql会实现这个目标吗?我最后的办法是在ExecuteNonQuery(DbCommand)周围放一个lock，但我不喜欢这样，因为它会造成瓶颈。我不认为在这里使用sql事务是有帮助的，因为我不担心命令的完整性，我更担心的是命令的并行性。

不中断地执行一个SQL块

一般来说，任何DML语句(UPDATE/INSERT/DELETE)都会在特定的表上放置一个锁(行级/表级)，但如果你想明确地保证你的操作不会干扰其他正在执行的语句，那么你应该考虑将整个SQL块放在一个事务块中

Begin transaction
begin try
DECLARE @InsertedProductID INT -- this is passed as a parameter
DECLARE @GroupID INT -- this is passed as a parameter
DECLARE @total INT
SET @total = (SELECT COUNT (*) FROM Products WHERE GroupID = @GroupID)
UPDATE Products SET ProdName = 'Prod_'+ CAST(@total as varchar(15)) WHERE ProductID = @InsertedProductID
commit; // commits the transaction
end try
begin catch
rollback; //Rolls back the transaction
end catch
end

您还应该考虑将Transaction Isolation Level改为READ COMMITTED以避免dirty reads。此外，显然您应该将整个逻辑包装在stored procedure中，而不是作为adhoc SQL

执行它们。

如果您可以控制SqlConnection对象的创建，请考虑使用Transactions和适当的IsolationLevel依赖数据库锁。例如，如果一个单独的事务在提交发生之前接触了数据，那么使用Snapshot将导致第二个提交的事务失败。

类似:

var c = new SqlConnection(...);
var tran1 = c.BeginTransaction(IsolationLevel.Snapshot);
var tran2 = c.BeginTransaction(IsolationLevel.Snapshot);
DoStuff(c, tran1);//Touch some database data
tran1.Commit();
DoStuff(c, tran2);//Change the same data
tran2.Commit();//Error!

我不确定你能不能这么做

UPDATE Products 
SET ProdName = 'Prod_'+ CAST((SELECT COUNT (*) 
                              FROM Products 
                              WHERE GroupID = @GroupID) as varchar(15)) 
WHERE ProductID = @InsertedProductID

但对我来说这是一个奇怪的更新

使用事务是正确的方法。除了其他答案，您还可以使用TransactionScope。TransactionScope隐式地将连接和SQL命令注册到事务中。由于TransactionScope在using块中，如果出现问题，回滚将自动发生。

的例子:

        try
        {
            using (var scope = new TransactionScope())
            {
                using (var conn = new SqlConnection("your connection string"))
                {
                    conn.Open();
                    var cmd = new SqlCommand("your SQL here", conn);
                    cmd.ExecuteNonQuery();
                }
                scope.Complete();
            }
        }
        catch (TransactionAbortedException ex)
        {
        }
        catch (ApplicationException ex)
        {
        }

相关文章：