Hibernate Envers - Support for JDBC batching in ValidityAuditStrategy with allow_identifier_reuse=true

Question

Using Hibernate + Envers (version 5.2.17.Final), I'm trying to persist approximately 250000 JPA entities and audit the initial insert using the Envers ValidityAuditStrategy. I'm using JDBC batching to improve performance. I see batching occurring for both

• Inserts to the base table (i.e. INSERT INTO dbo.EXAMPLE_TABLE)
• Inserts to the audit table (i.e. INSERT INTO dbo.EXAMPLE_TABLE_AUD)

but not for the query used to update the end revision of any previous audit rows, which I believe is enabled when setting allow_identifier_reuse=true (which is mandatory for my use case). Example of one of these update queries:

update
    dbo.example_table_aud
set
    revend=? 
where
    id=? 
    and rev<> ? 
    and revend is null

Entity code:

@Entity
@Audited
@Table(schema = "dbo", name = "EXAMPLE_TABLE")
public class ExampleEntity {

    @Id
    @Column(name = "ID", nullable = false)
    private long id;

    @Column(name = "NAME", nullable = false)
    private String name;

    @Version
    @Column(name = "VERSION", nullable = false)
    private int version;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getVersion() {
        return version;
    }

    public void setVersion(int version) {
        this.version = version;
    }
}

Hibernate/Envers config:

  org.hibernate.envers:
    audit_table_suffix: _AUD
    revision_field_name: REV
    revision_type_field_name: REVTYPE
    default_schema: dbo
    audit_strategy: org.hibernate.envers.strategy.ValidityAuditStrategy
    do_not_audit_optimistic_locking_field: false
    store_data_at_delete: true
    allow_identifier_reuse: true
  hibernate:
    dialect: org.hibernate.dialect.SQLServer2012Dialect
    format_sql: true
    jdbc.batch_size: 100
    jdbc.batch_versioned_data: true
    order_inserts: true
    order_updates: true

Is there a workaround to enable use of JDBC batching for the queries to update the end revision for any previous rows?

Answer 1

The update you mention happens for several reasons:

1. Identifier Reuse (which really only is important for REV_TYPE = 0 or RevisionType.ADD rows).
2. REV_TYPE != 0 (aka RevisionType.MOD and RevisionType.DEL rows).

Currently there is no real workaround so that those statements can be batched, primarily because of the nature of how those updates work anyway. The strategy as it exists expects those predicates to impact a single row in the table and therefore also checks that as a part of its sanity-check or else we force the transaction to fail.

I think it would be great to find a way to do this such that batch insert/updates would work, but we'd first have to find a way to implement those updates and maintain the same sanity-check where only one row would be influenced by that change rather than multiple which would be the case if they were delayed.

All that said; the all this logic is handled inside the ValidityAuditStrategy, which is a pluggable option for users and so you could potentially find a workable solution that you could share back with us.

In either case, I would suggest opening a JIRA enhancement issue with us and we can discuss in more detail how (if possible) best to handle potentially supporting batched inserts/updates effectively and more efficiently.