Spring Data / Hibernate MS SQL Server独特的约束和竞争条件 [英] Spring Data/Hibernate MS SQL Server unique constraint and race condition

问题描述

前一段时间，当两个单独的交易尝试同时检查记录是否存在（由4个字段组成）并且如果不存在 - 创建新记录时，我遇到了竞争条件问题。

我的环境：

MS SQL Server ， Spring Data / JPA / Hibernate

这是一个重复的记录问题。我实现了模拟并发调用的测试，因此能够（在99.99％的执行时间内非常稳定）重现此问题。

现在我解决了这个问题通过对这4个字段应用唯一的约束。此时我的测试无法重现此问题。我对此感到非常满意，但说实话，我并不完全明白它是如何运作的。这正是我创建这个问题的原因 - 我不明白为什么例如我的测试不会因为 ConstraintViolationException 而失败，而两个并发事务同时检查记录然后尝试创建它（每个）。在我的理解中，根据我的测试 - 两项交易同时进行，在第一次检查时不应找到任何记录。之后，他们应该尝试创建新记录，其中一个事务应该能够执行，另一个应该以 ConstraintViolationException 失败。但是代码工作正常，一切正常，没有任何异常。

在Spring Data / JPA / Hibernate级别还是有内部同步机制，甚至是MS SQL Server，交易来自不正确的工作，并让他们等待彼此的工作成果？请解释。谢谢！

解决方案

简答

在您的查询中使用了表格和索引。如果不同的进程使用不同的执行计划来检查行的不存在，那么它们都会返回true，并且由于SQL Server发出资源锁的方式而添加记录。

从[1]开始：

1. 执行写入操作时，SQL Server不会锁定相关的
   index ...只有相关的数据行。
   
2. 执行读取操作时，SQL Server仅锁定找到的对象（如索引，数据行等），在
   的访问路径中使用。
   

通过在这4个字段上添加唯一约束你隐式地和有效地添加了一个覆盖索引，这会导致你的所有进程使用相同的查询计划，并因此在相同的对象上以相同的顺序获取资源锁。

长答案

在使用不同索引查询时数据不匹配

有关资源锁定的非常详细的信息，请阅读：
[1] https：// www.mssqltips.com/sqlservertip/1485/using-sql-server-indexes-to-bypass-locks/

Some time ago I ran into the race condition issue when 2 separate transactions try to simultaneously check if the record exists(by 4 fields) and if no - create a new one.

My environment:

MS SQL Server, Spring Data/JPA/Hibernate

It was a duplicate records issue. I implemented the test that simulates concurrent calls and thus was able(pretty stable at 99.99% of execution times) to reproduce this issue.

Right now I'm fixed this issue by applying unique constraint over these 4 fields. At this moment my test is unable to reproduce this issue. I'm really happy with it but honestly, I don't fully understand how it works under the hood. This is exactly why I created this question - I don't understand why for example my test doesn't fail with a ConstraintViolationException while both of the concurrent transactions simultaneously check for record presence and then try to create it(each of them). In my understanding according to my test - both of the transactions work simultaneously and shouldn't find any record during the first check. After that, they should try to create new records and one of the transactions should be able to do it and another one should fail with ConstraintViolationException. But the code works fine and everything is working without any exceptions.

Is there any internal synchronization mechanisms at Spring Data/JPA/Hibernate level or even MS SQL Server that prevents concurrent transactions from incorrect work and allow them to wait for the results of work from each other? Please explain. Thanks !

解决方案

Short Answer

It all depends on indexes you have on the table and the indexes being used by your queries at the time. If different execution plans were used by different processes to check for the row's non-existence then they would both return true and both add a record due to the way SQL Server issues resource locks.

From [1]:

1. When performing a write operation, SQL Server does not lock related indexes ..., only the relevant data row.
2. When performing a read operation, SQL Server locks only the objects (e.g. indexes, data rows etc') that it found and used within its access path.

By adding a unique constraint on these 4 fields you implicitly and effectively added a covering index, which resulted in all of your processes using the same query plan and therefore acquiring resource locks in the same order on the same objects.

Long Answer

Data mismatch when querying with different indexes

For very detailed info about resource locking read this: [1] https://www.mssqltips.com/sqlservertip/1485/using-sql-server-indexes-to-bypass-locks/

这篇关于Spring Data / Hibernate MS SQL Server独特的约束和竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！