mysql sequencegenerator_Java - JPA - Generators - @SequenceGenerator

Even though this question is very old and I stumbled upon it for my own issues with JPA 2.0 and Oracle sequences.

Want to share my research on some of the things -

Relationship between @SequenceGenerator(allocationSize) of GenerationType.SEQUENCE and INCREMENT BY in database sequence definition

Make sure @SequenceGenerator(allocationSize) is set to same value as INCREMENT BY in Database sequence definition to avoid problems. For eg. if we define the sequence in database with a INCREMENT BY value of 20, set the allocationsize in SequenceGenerator also to 20. In this case the JPA will not make a call to database until it reaches the next 20 mark while it increments each value by 1 internally. This saves database calls to get the next sequence number each time. The side effect of this is - Whenever the application is redeployed or the server is restarted in between, it'll call database to get the next batch and you'll see jumps in the sequence values. Also we need to make sure the database definition and the application setting to be in-sync which may not be possible all the time as both of them are managed by different groups and you can quickly lose control of. If database value is less than the allocationsize, you'll see PrimaryKey constraint errors due to duplicate values of Id. If the database value is higher than the allocationsize, you'll see jumps in the values of Id.

If the database sequence INCREMENT BY is set to 1 (which is what DBAs generally do), set the allocationSize as also 1 so that they are in-sync but the JPA calls database to get next sequence number each time.

If you don't want the call to database each time, use GenerationType.IDENTITY strategy and have the @Id value set by database trigger. With GenerationType.IDENTITY as soon as we call em.persist the object is saved to DB and a value to id is assigned to the returned object so we don't have to do a em.merge or em.flush. (This may be JPA provider specific..Not sure)

Another important thing -

JPA 2.0 automatically runs ALTER SEQUENCE command to sync the allocationSize and INCREMENT BY in database sequence. As mostly we use a different Schema name(Application user name) rather than the actual Schema where the sequence exists and the application user name will not have ALTER SEQUENCE privileges, you might see the below warning in the logs - 000004c1 Runtime W CWWJP9991W: openjpa.Runtime: Warn: Unable to cache sequence values for sequence "RECORD_ID_SEQ". Your application does not have permission to run an ALTER SEQUENCE command. Ensure that it has the appropriate permission to run an ALTER SEQUENCE command.

As the JPA could not alter the sequence, JPA calls database everytime to get next sequence number irrespective of the value of @SequenceGenerator.allocationSize. This might be a unwanted consequence which we need to be aware of.

To let JPA not to run this command, set this value - in persistence.xml. This ensures that JPA will not try to run ALTER SEQUENCE command. It writes a different warning though - 00000094 Runtime W CWWJP9991W: openjpa.Runtime: Warn: The property "openjpa.jdbc.DBDictionary=disableAlterSeqenceIncrementBy" is set to true. This means that the 'ALTER SEQUENCE...INCREMENT BY' SQL statement will not be executed for sequence "RECORD_ID_SEQ". OpenJPA executes this command to ensure that the sequence's INCREMENT BY value defined in the database matches the allocationSize which is defined in the entity's sequence. With this SQL statement disabled, it is the responsibility of the user to ensure that the entity's sequence definition matches the sequence defined in the database.

As noted in the warning, important here is we need to make sure @SequenceGenerator.allocationSize and INCREMENT BY in database sequence definition are in sync including the default value of @SequenceGenerator(allocationSize) which is 50. Otherwise it'll cause errors.