Select MAX(field)+1 FROM ... 并发问题

2022-01-09 00:00:00 concurrency insert sql-server

您好我担心合作伙伴应用程序的并发性，因为最近几天它在 CRUDS 操作方面遇到了麻烦，尤其是在插入方面.所以我运行 SQL Profiler 并注意到他的插入语句不使用事务，而且他正在使用:

Hello Im afraid about concurrency on partner application cause in the last days it was having troubles with CRUDS Operations, especially with inserts. So I ran SQL Profiler and note that his insert statement dont use transaction and also he is using :

INSERT INTO TABLEA VALUES ( (SELECT MAX(NUMBERFIELD) +1 FROM TABLEA), ....);

如何避免使用 MAX()+1 生成主键?我建议使用自动增量或事务范围，但他不想或者他不知道如何实现这一点，是否有另一种方法可以实现这一点?

How avoid the use of MAX()+1 for generate primary keys? I suggest to use autoincrement or transaction scopes but he dont want or maybe he dont know how to achieve that, is there another way for lead with this?

使用 SQL 和 C++ 1.1

Using SQL and C++ 1.1

*这不是我的代码，但我可能会考虑向他展示这篇文章，因为我认为他必须考虑到所有意见都是受欢迎的.:)

*Its not my code but I might consider show this post to him cause I think he have to consider that all opinions are welcome. :)

推荐答案

如果你编码正确，你应该能够获得与使用 IDENTITY 解决方案相同的并发性.它不直观；您会认为锁定会降低并发性.但是我用五个不同的连接敲击了桌子进行了几次测试，并向自己证明了 MAX+1 技巧的表现与 IDENTITY 几乎完全相同.您可以在以下博客文章的第一部分中看到讨论:

If you code it right, you should be able to get the same concurrency out of this as with an IDENTITY solution. It is not intuitive; you would think that the locking would reduce concurrency. But I ran several tests with five distinct connections hammering the table, and proved to myself that the MAX+1 trick performed just about exactly the same as IDENTITY. You can see the discussion in the first section of the following blog post:

https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity

无论如何，这是我推荐的语法(我不太相信 INSERT ... VALUES (SUBQUERY) 模型，抱歉):

Anyway here is the syntax I recommend (I don't really trust the INSERT ... VALUES (SUBQUERY) model, sorry):

   DECLARE @NextID INT;

   BEGIN TRANSACTION;

   SELECT @NextID = COALESCE(MAX(NumberColumn), 0) + 1
       FROM dbo.TableA WITH (UPDLOCK);

   INSERT dbo.TableA SELECT @NextID;

   COMMIT TRANSACTION;

您必须想象错误处理部分(为简洁起见省略)，但我认为您会有所了解.

You'll have to imagine the error handling portions (omitted for brevity) but I think you'll get the drift.

当然不要把这些当成福音.您将需要使用您的数据和访问模式在您的硬件上运行测试，以确定此方法是否会损害并发性.这很可能是那些视情况而定"的答案之一.

Of course don't take any of this for gospel. You will need to run tests on your hardware using your data and access patterns to determine whether concurrency will be harmed by this method. This could very well be one of those "it depends" answers.

相关文章