使用简单更新的计数器列的原子增量 [英] Atomic increment of counter column using simple update

问题描述

我试图了解如何安全地增加计数器列，许多用户可能会同时增加计数器列(这是移动应用程序的Web API).

I am trying to understand how to safely increment a counter column, that may be incremented simultaneously by many users (It's a Web API for a mobile app).

我已经阅读了SO中针对该问题的战略中的常见问题，但是我似乎无法弄清楚使用简单方法有什么问题:

I've read the popular questions in SO for strategies dealing with the issue but I can't seem to figure what's wrong with using a simple:

UPDATE Table SET Counter = Counter + 1  

我构建了以下代码示例，以尝试获取不一致的值，并向自己证明，仅使用此简单的update语句不是一种好习惯:

I've built the following code sample to try and get inconsistent values and prove myself that using only this simple update statement is not good practice:

class Program
{
    static void Main(string[] args)
        {
            List<Task> tasks = new List<Task>();

            for (int i = 0; i < 100; i++)
            {
                Task t = Task.Factory.StartNew(() =>
                {
                    WriteToCounter();
                });

                tasks.Add(t);
            }

            Task.WaitAll(tasks.ToArray());
        }

    static void WriteToCounter()
        {
            string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                Random rnd = new Random();
                for (int i = 1; i <= 100; i++)
                {
                    int wait = rnd.Next(1, 3);
                    Thread.Sleep(wait);

                    string sql = "UPDATE Table SET Counter = Counter + 1";

                    SqlCommand command = new SqlCommand(sql, connection);
                    command.ExecuteNonQuery();
                }
            }
        }
}

在示例中，我试图模拟一种场景，其中许多用户同时访问API并更新计数器.代码运行时，计数器始终为 10000 ，这表示计数器是一致的.

In the sample I am trying to simulate a scenario in which many users access the API simultaneously and update the counter. When the code runs, the counter is always at 10000, which means it is consistent.

测试是否正确模拟了我描述的场景?
如果是这样，为什么我可以在没有任何特殊的锁定/事务策略的情况下使用update语句并仍然获得一致的结果?

Does the test correctly simulates the scenario I described?
And if so, how come the I can use the update statement without any special locking/transaction strategies and still get consistent results?

推荐答案

如果只使用像这样简单的方法，就可以了.

If you only ever use it as simple as this, you're fine.

问题开始于:

• 您添加了一个条件-大多数条件都很好，但请避免基于Counter进行过滤，这是失去确定性的好方法
• 您在交易内部进行更新(请注意这一点-在实际更新语句范围之外的事务中很容易，如果使用例如TransactionScope，则更是如此)
• 您将插入和更新结合起来(例如，通常的如果不存在则插入"模式)-如果您只有一个计数器，这不是问题，但是对于多个计数器，很容易陷入此陷阱；不太难解决，除非您也有删除操作，否则它将变成一个完全不同的联赛:)
• 也许，如果您依赖Counter的值作为唯一的自动递增标识符.如果将select和update分开，这显然是行不通的(而且，基于 select的update 没有帮助-与普通的update不同，没有在同一行上进行更新序列化；这是锁定提示出现的地方)，我不确定使用output是否安全.

• You add a condition - most conditions are fine, but avoid filtering based on Counter, that's a great way to lose determinism
• You update inside of a transaction (careful about this - it's easy to be in a transaction outside of the scope of the actual update statement, even more so if you use e.g. TransactionScope)
• You combine inserts and updates (e.g. the usual "insert if not exists" pattern) - this is not a problem if you only have a single counter, but for multiple counters it's easy to fall into this trap; not too hard to solve, unless you also have deletes, then it becomes a whole different league :)
• Maybe if you rely on the value of Counter being a unique auto-incrementing identifier. It obviously doesn't work if you separate the select and update (and no, update based on select doesn't help - unlike plain update, the select isn't serialized with updates on the same row; that's where locking hints come in), I'm not sure if using output is safe.

当然，如果事务隔离级别发生变化，情况可能会大不相同.这实际上是错误的合理原因，因为SQL连接池不会重置事务隔离级别，因此，如果您进行更改，则需要确保它不会影响在SqlConnection上执行的任何其他SQL.从游泳池里拿出来的.

And of course, things might be quite different if the transaction isolation level changes. This is actually a legitimate cause of errors, because SQL connection pooling doesn't reset the transaction isolation level, so if you ever change it, you need to make sure it can't ever affect any other SQL you execute on a SqlConnection taken out of the pool.

这篇关于使用简单更新的计数器列的原子增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！