如何在不复制存储过程代码的情况下提示更新锁 [英] How to hint update lock without duplicating stored procedure code

问题描述

我有一个现有的存储过程SP1，只需从表中选择即可。它已在多个地方使用。

I've an existing stored procedure SP1 which is simple select from a table. It's being used at multiple places.

现在有一个新代码，它在一个可序列化事务中使用此SP1和更新语句。我看到了死锁，两个事务能够在同一行行上获取读取锁，现在想将该锁转换为更新。

Now there is a new code which uses this SP1 along with an update statement in one serializable transaction. I'm seeing deadlock where two transactions are able to acquire read lock on same set of rows and now want to convert that lock to update.

一种可能的解决方案是使此SP1在读取提交的隔离级别中执行。 但是我认为这不是正确的解决方法，因为可能会丢失更新。另一种解决方案是在SP1的select语句中提示 UPDLOCK 。这将解决死锁，因为无论任何事务获得了UPDLOCK都将继续进行。

One possible solution is to make this SP1 execute in read committed isolation level. But I think this is not the right fix, as there can be lost updates. Another solution is to hint UPDLOCK in SP1's select statement. This will resolve the deadlock as whatever transaction acquires that UPDLOCK will only proceed. The other transaction will have to wait for this to commit.

现在向此SP1添加 UPDLOCK 不必要地增加了此开销其他调用SP1但又不想 UPDLOCK 的地方。因此，人们可能会考虑复制此SP1并使用新的 SP1UPDLOCK 与SP1相同，但具有 UPDLOCK 。我不要这个重复。
因此，调用者有任何方法可以暗示应该使用UPDLOCK来获取SP1返回的任何内容。

Now adding UPDLOCK to this SP1 unnecessarly adds this overhead for other places which call SP1 but don't want UPDLOCK. So one might think to duplicate this SP1 and have new SP1UPDLOCK which is same as SP1 but with UPDLOCK. I don't want this duplication. So is there any way caller can hint that whatever SP1 returns should be take with UPDLOCK.

还有其他更好的方法来解决此类问题。

Any other better way to solve this type of issue.

我正在使用SQL Server 2008，C＃、. NET 4。

I'm using SQL server 2008, C#, .NET 4.

示例代码

CREATE PROCEDURE SP1
    @SomeId int
AS
BEGIN
    Select Foo From Bar Where SomeOne = @SomeId
END

CREATE PROCEDURE SP1UPDLOCK
    @SomeId int
AS
BEGIN
    Select Foo From Bar (UPDLOCK) Where SomeOne = @SomeId
END

CREATE PROCEDURE SP2
    @Foo int
    @SomeId int
AS
BEGIN
    Update Bar
    Set Foo = @foo
    Where SomeOne = @someId
End

C＃代码

Using(Transaction t = new Transaction())
{
    result = SP1(someId);
    // some logic here
    if(somecond == true)
    {
        SP2(fooVal, someId);
    }

    t.Commit();
}

推荐答案

如果随后调用了SP1通过SP2是原子的，它们应在T-SQL中组合。
或丢失c＃交易。您正在通过往返来不必要地延长事务。

If the calls to SP1 follows by SP2 are atomic, they should be combined in T-SQL. Or lose the c# transaction. You are prolonging the transaction unnecessarily with roundtrips.

此外，为什么在SP1UPDLOCK上设置了UPDLOCK而不在SP1上设置了UPDLOCK？我不明白为什么。如果问题出在锁提示上，请不要使用它们。如果某些东西是可序列化的（为什么？），则再次使其成为单个原子调用

Also, why have UPDLOCK on SP1UPDLOCK but not for SP1? I can't see why. If the problem is lock hints, don't use them. If something is serializable (why?) then again, make it a single atomic call

请注意，默认情况下仍为 READ COMMITTED

Note that the default is READ COMMITTED anyway

最后，您是说信号量未锁定吗？使用 sp_getapplock 将控制代码流，而无需在数据上使用锁

Finally, do you mean "semaphore" not lock? Using sp_getapplock will control flow through the code without using locks on the data

这篇关于如何在不复制存储过程代码的情况下提示更新锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！