RepeatableRead 似乎没有锁定读取
我遇到了一个奇怪的问题。我有一个表，该表的主键是在 INSTEAD OF 触发器中生成的。这可能是实现主键的最糟糕的方法，但它们基本上获取最大值，将其增加 1，然后使用该值作为键。这发生在代替触发器中。
我有一个启动 RepeatableRead 事务的 .Net 应用程序，并将一条记录插入到该表中。只要我不尝试同时执行多个插入，这种方法就可以正常工作。如果这样做，我会收到 PK 违规错误。在我看来，插入都被触发，触发器为每个事务获取相同的最后一个数字，将其增加 1，然后尝试使用相同的新“标识符”插入两条记录。
我已经与设置此触发器的 DBA 交谈过，他认为 RepeatableRead 应该阻止这种情况发生，因为它显然会锁定表以进行读取，而其他事务将等待锁被释放。因此，本质上，我的交易将串行发生。
所以，问题是，如果 RepeatableRead 按 DBA 描述的方式工作，为什么我会遇到 PK 违规？
I am getting a strange problem. I have a table that has a primary key that is generated in an INSTEAD OF trigger. It's probably the worst way to implement a primary key, but they basically fetch the maximum, increment it by 1, and use this value as the key. This happens in the instead of trigger.
I have a .Net app that starts a RepeatableRead transaction, and I insert a record into this table. This works fine as long as I don't try and do more than one insert simultaneously. If I do, I receive a PK violation error. It seems to me that the inserts both get fired, the trigger fetches the same last number for each transaction, increments it by 1, and then tries to insert both records with the same new 'identifier'.
I've spoken to the DBA that set this trigger up, and he is of the opinion that RepeatableRead should stop this from happening as it apparently locks the table for reads and other transactions will wait for the locks to be released. So, in essence, my transactions will occur in serial.
So, the question is, why would I be getting a PK violation if RepeatableRead works the way the DBA described it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
RepeatableRead 不会解决这个问题，因为它允许幻像插入，这正是您在这里所拥有的。第二个插入是错误的，因为它没有“看到”前一个插入，并且您具有所描述的行为。
您可以使用可序列化隔离来修复它，或者通过执行单独的事务来修复它（前者会增加争用，后者会减少争用，但后者可能对您来说不可能）。
实际上，修复方法是用属性标识约束替换代替触发器（这可以在现有表上完成，尽管存在困难，特别是如果正在使用复制），或者失败使用更好的算法来设置新值。
RepeatableRead won't fix that, because it allows for phantom inserts, which is exactly what you have here. The second insert is wrong because it doesn't "see" the previous insert, and you have the behaviour described.
You can fix it with serializable isolation, or by doing separate transactions (the former will increase contention, the latter reduce it, but the latter may not be possible for you).
Really, the fix is to replace the instead of trigger with a property identity constraint (this can be done on an existing table, though there are difficulties, esp. if replication is being used), or failing that using a better algorithm to set the new value.
首先让我表达我对生成主键所采取的方法的厌恶，现在就可重复读取隔离而言，它只能防止更新，您仍然能够读取，这就是您的实现的问题，没有保护 理想情况下
，我敦促您重组主键生成，但如果这是不可能的，那么剩下的唯一事情就是使用可串行化隔离，它也可以保护插入，但是取决于您确定下一个键值的方式和时间，您可能会无论哪种方式都无法解决
first let me express my disgust of the approach taken to generate the primary key, now as far as the REPEATABLE READ isolation, it only protects against updates, you will still be able to read and that is the problem with your implementation, no protection against inserts either
ideally I urge you to restructure the primary key generation but if that is not possible the only thing left is to use SERIALIZABLE isolation, which protects for inserts as well, however depending on how and when you determine the next key value, you might not be able to solve it either way