在MS SQL Server中，是否有一种方法可以“原子化”地增加作为计数器的列?

Assuming a Read Committed Snapshot transaction isolation setting, is the following statement "atomic" in the sense that you won't ever "lose" a concurrent increment?

假设有一个读取提交的快照事务隔离设置，那么下面的语句是“原子的”吗?

update mytable set counter = counter + 1

I would assume that in the general case, where this update statement is part of a larger transaction, that it wouldn't be. For example, I think this scenario is possible:

我假设在一般情况下，这个update语句是更大事务的一部分，它不会。例如，我认为这种情况是可能的:

update the counter within transaction #1
更新事务1中的计数器
do some other stuff in transaction #1
在事务1中做其他的事情吗
update the counter with transaction #2
使用事务#2更新计数器
commit transaction #2
提交事务# 2
commit transaction #1
提交事务# 1

In this situation, wouldn't the counter end up only being incremented by 1? Does it make a difference if that is the only statement in a transaction?

在这种情况下，计数器不会只增加1?如果这是事务中唯一的语句，这有什么区别吗?

How does a site like * handle this for its question view counter? Or is the possibility of "losing" some increments just considered acceptable?

像*这样的网站是如何处理问题视图计数器的?或者，“失去”某些增量的可能性是可以接受的吗?

4 个解决方案

#1

Read Committed Snapshot only deals with locks on selecting data from tables.

只读提交快照只处理从表中选择数据时的锁。

In t1 and t2 however, you're UPDATEing the data, which is a different scenario.

在t1和t2中，你更新数据，这是另一种情况。

When you UPDATE the counter you escalate to a write lock (on the row), preventing the other update from occurring. t2 could read, but t2 will block on its UPDATE until t1 is done, and t2 won't be able to commit before t1 (which is contrary to your timeline). Only one of the transactions will get to update the counter, therefore both will update the counter correctly given the code presented. (tested)

当您更新计数器时，您将升级为写锁(在行上)，以防止发生其他更新。t2可以读取，但是t2会阻塞它的更新直到t1完成，t2不能在t1之前提交(这与你的时间表相反)。只有一个事务可以更新计数器，因此在给出代码的情况下，两个事务都可以正确地更新计数器。(测试)

counter = 0
计数器= 0
t1 update counter (counter => 1)
t1更新计数器(计数器=> 1)
t2 update counter (blocked)
t2更新计数器(阻塞)
t1 commit (counter = 1)
t1提交(counter = 1)
t2 unblocked (can now update counter) (counter => 2)
未阻塞的t2(现在可以更新计数器)(counter => 2)
t2 commit
t2提交

Read Committed just means you can only read committed values, but it doesn't mean you have Repeatable Reads. Thus, if you use and depend on the counter variable, and intend to update it later, you're might be running the transactions at the wrong isolation level.

读取提交只是意味着您只能读取提交的值，但并不意味着您拥有可重复读取。因此，如果您使用并依赖计数器变量，并打算稍后更新它，那么您可能正在错误的隔离级别上运行事务。

You can either use a repeatable read lock, or if you only sometimes will update the counter, you can do it yourself using an optimistic locking technique. e.g. a timestamp column with the counter table, or a conditional update.

您可以使用可重复的读锁，或者如果您只是偶尔更新计数器，您可以使用乐观锁定技术来完成它。例如，带有计数器表的时间戳列，或有条件更新。

DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234

-- do stuff with the counter value

UPDATE MyTable
   SET counter = counter + 1
WHERE
   MyID = 1234
   AND 
   counter = @CounterInitialValue -- prevents the update if counter changed.

-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )
    ROLLBACK

This devx article is informative, although it talks about the features while they were still in beta, so it may not be completely accurate.

这篇devx的文章提供了很多信息，尽管它在测试时谈到了这些特性，因此它可能不是完全准确的。

update: As Justice indicates, if t2 is a nested transaction in t1, the semantics are different. Again, both would update counter correctly (+2) because from t2's perspective inside t1, counter was already updated once. The nested t2 has no access to what counter was before t1 updated it.

更新:正如Justice所指出的，如果t2是t1中的嵌套事务，则语义不同。同样，两者都会正确地更新计数器(+2)，因为从t2的角度来看，在t1内部，计数器已经更新了一次。在t1更新之前，嵌套的t2无法访问什么计数器。

counter = 0
计数器= 0
t1 update counter (counter => 1)
t1更新计数器(计数器=> 1)
t2 update counter (nested transaction) (counter => 2)
t2更新计数器(嵌套事务)(计数器=> 2)
t2 commit
t2提交
t1 commit (counter = 2)
t1提交(counter = 2)

With a nested transaction, if t1 issues ROLLBACK after t1 COMMIT, counter returns to it's original value because it also undoes t2's commit.

对于嵌套事务，如果t1在t1提交后发出回滚，计数器返回其原始值，因为它也取消t2的提交。

#2

According to the MSSQL Help, you could do it like this:

根据MSSQL帮助，您可以这样做:

UPDATE tablename SET counterfield = counterfield + 1 OUTPUT INSERTED.counterfield

This will update the field by one, and return the updated value as a SQL recordset.

这将逐个更新字段，并将更新后的值作为SQL记录集返回。

#3

No, it's not. The value is read in shared mode and then updated in exclusive mode, so multiple reads can occur.

不,它不是。值在共享模式下读取，然后在独占模式下更新，这样就可以进行多次读取。

Either use Serializable level or use something like

可以使用Serializable级别，或者使用类似的方法。

update t
set counter = counter+1
from t with(updlock, <some other hints maybe>)
where foo = bar

#4

There is at heart only one transaction, the outermost one. The inner transactions are more like checkpoints within a transaction. Isolation levels affect only sibling outermost transactions, not parent/child related transactions.

本质上只有一个交易，最外层的交易。内部事务更像是事务中的检查点。隔离级别只影响最外层的兄弟事务，而不影响与父/子相关的事务。

The counter will be incremented by two. The following yields one row with a value of (Num = 3). (I opened up SMSS and pointed it to a local SQL Server 2008 Express instance. I have a database named Playground for testing stuff.)

计数器将增加2。下面的代码生成一行，值为(Num = 3)。我有一个叫游乐场的数据库用来测试东西。

use Playground

drop table C
create table C (
    Num int not null)

insert into C (Num) values (1)

begin tran X
    update C set Num = Num + 1
    begin tran Y
        update C set Num = Num + 1
    commit tran Y
commit tran X

select * from C

#1