How to use the pooled-lo optimizer with Hibernate

Question

I have a JBoss 7.1.1 running an EAR that uses the JPA. With the JPA annotation, I use the strategy GenerationType.Table which is mapped to the org.hibernate.id.enhanced.TableGenerator.

Does anyone know how to configure the persistence.xml when using the "pooled-lo" optimizer?

Answer 1

TABLE generator is a terrible choice

Now, before I start explaining how you can configure the pooled or pooled-lo optimizers, you should know that the TABLE generator is a terrible choice as not only that it's 10 times slower, but it can congest your database connection pool since it requires an extra connection to fetch the identifier.

More, because it uses row-level locks to allocate the next identifier, this can lead to bottlenecks in your application. For more details about the perils of TABLE generator.

Sequence-based optimizers

Starting with Hibernate 5, the pooled optimizer is used whenever you are setting the allocationSize attribute of the JPA @SequenceGenerator annotation to a value that's greater than 1.

For Hibernate 4 or 3, to use the pooled or pooled-lo optimizers, you have to enable the following Hibernate property:

<property name="hibernate.id.new_generator_mappings" value="true"/>

It's worth noting that the pooled and pooled-lo optimizers are only available for SEQUENCE and TABLE generators as the goal of these optimizers is to reduce the number of database roundtrips needed to fetch the next entity identifiers.

Pooled optimizer

The pooled optimizer is very easy to set up. All you need to do is set the allocationSize of the JPA @SequenceGenerator annotation, and Hibernate is going to switch to using the pooled optimizer:

@Id
@GeneratedValue(
    strategy = GenerationType.SEQUENCE,
    generator = "post_sequence"
)
@SequenceGenerator(
    name = "post_sequence",
    sequenceName = "post_sequence",
    allocationSize = 3
)
private Long id;

Since this mapping is more straightforward, you can switch to pooled-lo instead of pooled if you also provide this Hibernate configuration property:

<property name="hibernate.id.optimizer.pooled.preferred" value="pooled-lo" />

Pooled-lo optimizer

To use the pooled-lo optimizer, the entity identifier mapping will look as follows:

@Id
@GeneratedValue(
    strategy = GenerationType.SEQUENCE,
    generator = "pooled-lo"
)
@GenericGenerator(
    name = "pooled-lo",
    strategy = "sequence",
    parameters = {
        @Parameter(
            name = "sequence_name",
            value = "post_sequence"
        ),
        @Parameter(
            name = "initial_value",
            value = "1"
        ),
        @Parameter(
            name = "increment_size",
            value = "3"
        ),
        @Parameter(
            name = "optimizer",
            value = "pooled-lo"
        )
    }
)

To understand how the pooled-lo works, check out this diagram:

If you have been using the legacy hilo optimizer, you might want to switch to using pooled or pooled-lo, as hilo is not interoperable with other clients that might not be aware of the hilo identifier allocation strategy.

Answer 2

In the meantime i got an answer for my question.

When you add the following line to the <properties> section in your persistent.xml file, hibernate will use the "pooled-lo" optimizer.

<property name="hibernate.id.optimizer.pooled.prefer_lo" value="true" />