Grails sequence generation for Oracle 11g

Question

I realize this is more of a hibernate question than Grails. In a load balanced (2 nodes) environment I see that the ids of my objects are jumping around quite a bit. Even without restarting the app server I see that the numbers skip 10 sometimes 20 numbers. I suspect the hibernate session is caching a block of sequence values. Is there a way to control this behavior with grails 1.3.7 ? Essentially I am OK with server pulling nextval from DB every time it needs one.

My domain object sequence declaration (same for 2 objects):

static mapping = {
        id generator:'sequence', params:[sequence:'MY_SEQ']  
    } 

Answer 1

I have since went to the database and modified the Sequence with the following DDL:

ALTER SEQUENCE MY_SEQ NOCACHE;

I think this is the best solution for this issue. Does anyone see potential problems with this approach?

Thanks all!

Answer 2

The caching issue is because Hibernate defaults to an Oracle dialect which does two things. It creates a sequence shared across all tables for primary key generation and the sequence caches 20 numbers at a time, which if they aren't used within a specific timeframe, Oracle will discard the remainder.

The following Oracle solution comes from the a post by Burt Beckwith, with a tweek to prevent the Oracle sequence from caching numbers. Thus, this dialect will do two things for you:

• It will create a single sequence for each table, so the sequence isn't shared and primary key numbers aren't split across tables.
• It will disable the caching of numbers in Oracle, so you won't lose any sequence numbers in an expired cache. This is set up in the PARAMETERS property with the NOCACHE command.

Since you are defining your table's sequence in the mapping you could probably strip out the sequence per table logic and leave in the NOCACHE sequence definition to achieve your desired results.

Also, you'll need to drop the sequence from your existing tablespace, as Grails will not re-create it, except in create and create-drop scenarios. When doing this you may also want to bump up the start value of the new sequence to prevent conflicting primary key issues with keys already in use by the database.

To use the dialect add dialect = SequencePerTableOracleDialect to your DataSource.groovy file in the dataSource definition closure.

import java.util.Properties;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.id.PersistentIdentifierGenerator;
import org.hibernate.id.SequenceGenerator;
import org.hibernate.type.Type;

public class SequencePerTableOracleDialect extends Oracle10gDialect {
    public static final String PARAMETERS = "MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE";

    /**
     * Get the native identifier generator class.
     * 
     * @return TableNameSequenceGenerator.
     */
    @Override
    public Class<?> getNativeIdentifierGeneratorClass() {
        return TableNameSequenceGenerator.class;
    }

    /**
     * Creates a sequence per table instead of the default behavior of one
     * sequence.
     */
    public static class TableNameSequenceGenerator extends SequenceGenerator {

        /**
         * {@inheritDoc} If the parameters do not contain a
         * {@link SequenceGenerator#SEQUENCE} name, we assign one based on the
         * table name.
         */
         @Override
         public void configure(final Type type, final Properties params,
                 final Dialect dialect) {
             if (params.getProperty(SEQUENCE) == null
                    || params.getProperty(SEQUENCE).length() == 0) {
                 /* Sequence per table */
                 String tableName = params
                        .getProperty(PersistentIdentifierGenerator.TABLE);
                 if (tableName != null) {
                     params.setProperty(SEQUENCE, createSequenceName(tableName));
                 }
                 /* Non-Caching Sequence */
                 params.setProperty(PARAMETERS, SequencePerTableOracleDialect.PARAMETERS);
            }
            super.configure(type, params, dialect);
        }

        /**
         * Construct a sequence name from a table name.
         * 
         * @param tableName
         *            the table name
         * @return the sequence name
         */
        String createSequenceName(final String tableName) {
            return "seq_" + tableName;
        }
    }
}

This link has some history on this question, with a link to Burt's original code, and a response for PostGreSql: Hibernate & postgreSQL with Grails