休眠乐观锁定 Postgres 和 MariaDb 之间的不同行为 [英] Hibernate optimistic locking different behavior between Postgres and MariaDb

问题描述

我刚刚发现，当我对 Postgresql 或 MariaDB 数据库使用乐观锁定时，我的应用程序的行为会有所不同，我想知道是否有人可以解释发生了什么以及如何使应用程序以与 MariaDB 相同的方式工作?我使用带有 InnoDB 引擎和默认设置的 Postgresl 10.5 和 MariaDB 10.3.10.我使用 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.

所以我的代码如下所示:

So my code looks like this:

@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.

这篇关于休眠乐观锁定 Postgres 和 MariaDb 之间的不同行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！