选择MAX(field)+1 FROM ...并发问题 [英] Select MAX(field)+1 FROM ... Concurrency issues

问题描述

您好，我担心合作伙伴应用程序的并发性在最近几天一直困扰着CRUDS操作，尤其是插入操作.因此，我运行了SQL事件探查器，并注意到他的insert语句不使用事务，而且他正在使用:

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-kicks-make-sumptions-identity-a-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.

这篇关于选择MAX(field)+1 FROM ...并发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！