我变得持续但间歇性的“违反主键约束”错误
我是公司中试图解决 Coldfusion 错误和 bug 的人。我们每天都会收到包含冷融合错误等完整详细信息的电子邮件，并将这些信息存储在我们的数据库中。
对于 ColdFusion 中的一些不同应用程序，它们似乎偶尔会生成“违反主键约束”错误。
在代码中，我们总是在尝试插入之前检查数据库中是否存在行，但它仍然会生成该错误。
所以我的想法是，我们要么需要围绕这些检查、插入或更新块进行 cftransaction。但我不确定这是否能真正解决问题。
它们以标准 Coldfusion 风格/框架进行编码。这是伪代码的示例。
cfquery 名称 =“check_sometable”数据源 =“#dsn#” 选择ID 从某个表 /cfquery
if check_sometable.recordcount gt 0 - 插入 别的 - 更新 /endif
那么为什么这会间歇性地导致主键违规呢？
这是 sql server 问题吗？我们是否缺少配置选项？
我们得到这一切是因为我们没有使用 Coldfusion 8 标准的最新修补版本吗？
我们需要升级 jdbc/odbc 驱动程序吗？
谢谢。
I am the person in my company who tries to solve coldfusion errors and bugs. We get daily emails with full details of coldfusion errors etc, as well we store this information in our database.
And for a few different applications in ColdFusion, they seem to sporadically generated "Violation of PRIMARY KEY constraint" errors.
In the code we always check for the existence of a row in the database before we try to do an insert, and it still generate's that error.
So my thinking is, either we need to a cftransaction around these each of the check, insert or update blocks. But I am not sure this will truly solve the problem.
These are coded in standard coldfusion style/framework. Here is an example in pseudo-code.
cfquery name="check_sometable" datasource="#dsn#"
select id
from sometable
/cfquery
if check_sometable.recordcount gt 0
-do insert
else
-do update
/endif
So why would this intermittently, cause primary key violations?
Is this a sql server problem, are we missing a configuration option?
Are we getting all of this because we are not on the latest hotfixed version of coldfusion 8 standard?
Do we need to upgrade our jdbc/odbc drivers?
Thank You.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对我来说听起来像是竞赛条件。两个连接同时检查下一个可用 id，获得相同的 id，然后在第二个连接上插入失败。如果它是代理键，为什么不使用身份字段来创建 PK？
如果您有一个自然键的 PK，那么违规是一件好事，您有两个用户试图插入您不想要的相同记录。不过，我会尝试优雅地失败，并出现一个错误，表明其他人已经创建了相同的记录。然后询问他们是否要在将新值加载到屏幕后更新它。我不确定我是否希望将其设置为让第二个人自动更新数据，而他们不会看到第一个人放入数据库的内容。
此外，这可能表明您的自然密钥并不像您想象的那么独特。不确定这个应用程序的用途，但是两个人希望同时处理相同数据的可能性有多大？因此，如果您的自然键类似于公司名称，请注意它们不能保证是唯一的，并且您可能会让用户用另一家公司的数据覆盖一家公司的良好数据。我发现生活中确实很少有真正独特的、永不改变的自然键。因此，如果您的自然密钥确实不是唯一的，则您可能已经拥有错误的数据，并且 PK 违规只是不同问题的症状，而不是真正的问题。
Sounds like race conditions to me. Two connections check for the next available id at the same time, get the same one and then the insert fails on the second one. Why are you not using an identity field to create the PK if it is a surrogate key?
If you have a PK that is a natural key, then the violation is a good thing, you have two users trying to insert the same record which you do not want. I would try to fail it gracefully though, with an error that says someone else has created the same record. And then ask if they want to update it after loading the new values to their screen. I'm not sure I would want it to set up so that the data is automatically updated by the second person without them seeing what the first person put into the database.
Further this might be an indication that your natural key is not as unique as you think it is. Not sure what this application does, but how likely is it that two people would want to be working with the same data at a the same time? So if your natural key were something like company name, be aware that they are not guaranteed to be unique and you might have users overwriting good data for one company with data for another company already. I've found in life there are truly very few really unique, never changing natural keys. So if your natural key really isn't unique, you may already have bad data and the PK violations are just a symptom of a differnt problem not the real problem.