Hibernate乐观地锁定Postgres和MariaDb之间的不同行为 [英] Hibernate optimistic locking different behavior between Postgres and MariaDb
问题描述
我刚刚发现,当我对Postgresql或MariaDB数据库使用开放式锁定时,我的应用程序的行为会有所不同,我想知道是否有人可以解释会发生什么情况,以及如何使该应用程序与MariaDB一样工作?我将Postgresl 10.5和MariaDB 10.3.10与InnoDB引擎和默认设置一起使用.我使用的是Spring框架版本5.1.0和Hibernate 5.3.6.
I just discovered that my application behaves differently when I use optimistic locking with a Postgresql or a MariaDB database and I am wondering if somebody can explain what happens and how could I make the application work in the same way with MariaDB? I use Postgresl 10.5 and MariaDB 10.3.10 with InnoDB engine and default settings. I use Spring framework version 5.1.0 and Hibernate 5.3.6.
所以我的代码如下:
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Bla {
@Id
@GeneratedValue
private long id;
@Version
private long version;
private int counter;
}
我还有一个用于该实体的存储库和以下服务方法:
I also have a repository for this entity and the following service method:
@Transactional
public int increment(long id) {
Bla bla = blaRepo.getOne(id);
bla.setCounter(bla.getCounter() + 1);
return bla.getCounter();
}
如果我在多个线程上调用此方法,我希望如果它们接触具有相同版本的实体,则更新只会对其中一个执行成功.例如:如果我一次运行以Postgres db启动50个线程,则会获得3个成功调用并返回值1、2、3,而其他47个失败,并返回ObjectOptimisticLockingFailureException,这是预期的行为-这就是我想要的方式该应用程序的行为.
If I call this method on multiple threads I would expect update would succeed only for a single one of them if they touch the entity with the same version. As an example: if I start 50 thread with Postgres db in one run I get 3 calls that succeed and return the values 1, 2, 3 and the other 47 fail with an ObjectOptimisticLockingFailureException which is the expected behavior - this is how I would like the app to behave.
但是,如果我切换到MariaDB,则不会发生这种情况.这些线程中的所有50个均已成功完成,并且在多个线程中得到的响应值相同,就好像没有乐观锁一样.例如,现在前5个线程返回1,然后其中20个线程返回2,其余3个或4.
However, if I switch to MariaDB then this doesn't happen. All 50 of these threads are completed successfully and I get the same response value in multiple threads as if there would be no optimistic lock. For example now the first 5 threads returned 1, then 20 of them returned 2, and the rest 3 or 4.
为什么会这样?这没有任何意义-对于这两个数据库,生成的查询都是
Why this is happening? It doesn't make any sense - with both databases the query generated is
update bla set counter=?, version=? where id=? and version=?
但是在Postgresql中将正确失败,并且使用MariaDB将会意外成功.
But in Postgresql will fail correctly and with MariaDB will succeed unexpectedly.
推荐答案
我找到了解决此问题的方法.
I found the solution to this problem.
似乎我在application.properties中设置了此属性:
It seems that I had this property set in application.properties:
spring.jpa.properties.hibernate.jdbc.batch_size = 50
当我使用Postgresql时,我得到了带有两个线程的以下调试跟踪:
When I do use Postgresql I get the following debug trace with two threads:
13223 [pool-2-thread-2] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
13223 [pool-2-thread-1] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
13226 [pool-1-thread-1] DEBUG org.hibernate.engine.jdbc.batch.internal.BatchingBatch - Executing batch size: 1
13226 [pool-1-thread-2] DEBUG org.hibernate.engine.jdbc.batch.internal.BatchingBatch - Executing batch size: 1
13230 [pool-1-thread-1] ERROR org.hibernate.engine.jdbc.batch.internal.BatchingBatch - HHH000315: Exception executing batch [org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1], SQL: update bla set counter=?, version=? where id=? and version=?
,然后使用具有相同批处理大小50的MariaDB:
and then with MariaDB with the same batch size 50:
21978 [pool-2-thread-2] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
21978 [pool-2-thread-1] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
21979 [pool-2-thread-2] DEBUG org.hibernate.engine.jdbc.batch.internal.BatchingBatch - Executing batch size: 1
21979 [pool-2-thread-1] DEBUG org.hibernate.engine.jdbc.batch.internal.BatchingBatch - Executing batch size: 1
21980 [pool-2-thread-2] DEBUG org.hibernate.jdbc.Expectations - Success of batch update unknown: 0
21980 [pool-2-thread-1] DEBUG org.hibernate.jdbc.Expectations - Success of batch update unknown: 0
,然后使用批处理大小为1的MariaDB:
and then with MariaDB with batch size 1:
12994 [pool-2-thread-2] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
12994 [pool-2-thread-1] DEBUG org.hibernate.SQL - update bla set counter=?, version=? where id=? and version=?
12997 [pool-2-thread-1] DEBUG org.hibernate.cache.internal.TimestampsCacheEnabledImpl - Invalidating space [bla], timestamp: 6307671153053696
12998 [pool-2-thread-2] DEBUG org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl - JDBC transaction marked for rollback-only (exception provided for stack trace)
现在应用程序确实抛出了预期的ObjectOptimisticLockingFailureException
And now indeed the application throw the expected ObjectOptimisticLockingFailureException
但是不幸的是,这意味着无法将MariaDb与乐观锁在实体上使用,并且任何批量大小大于1的对象都不兼容.
But unfortunately this means that using MariaDb with optimistic locking on entities and any batch size greater than 1 is not compatible.
这篇关于Hibernate乐观地锁定Postgres和MariaDb之间的不同行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!