Database IDs incrementing by more than one

Question

For saving my grails database to a file rather than memory, I have just changed my data source in my grails app to:

development {
    dataSource {
        dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
        url = "jdbc:h2:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
    }
}

as shown in online tutorials.

However, when I create a User object and save it to the database, it may have an id of say, 1. If I do some other operations then create another User object is is given an ID such as 14, but never 2.

I understand this may have something to do with Hibernate creating the ID for an object regardless of domain class?

Is there a way to get id's to be unique to domains?

So as `user` objects are added, that are assigned IDs such as 1,2,3,4... Rather than a `user` being created, being assigned ID 1, then a `task` being created and being assigned 2, another `user` being added, will be assigned ID 3 and so on — andy mccullough, Apr 19 '13 at 11:42
Well, sequences usually do not guarantee that increments be always by-one. This questions are related: http://stackoverflow.com/questions/7171626/hibernate-with-oracle-sequence-doesnt-use-it/7171829#7171829 http://stackoverflow.com/questions/8333998/grails-sequence-generation-for-oracle-11g — Elias Dorneles, Apr 19 '13 at 11:48
by default hibernate makes a single sequence to use for all ids. You can specify a separate sequence for each class in the mapping closure (see Kelly's answer) — codelark, Apr 19 '13 at 18:48

score 1 · Accepted Answer · answered Apr 19 '13 at 17:46

As Elias said you probably can't guarantee they will be absolutely sequential if you use a database sequence. In some databases (like Oracle) you can control it a little by setting NOCACHE or some other setting on the sequence. That still does not guarantee no gaps, and if you have a lot of inserts it can affect performance.

You can also set a different sequence for domain classes where you care about this behavior.

class User {
    static mapping = {
        id generator: 'sequence', params:[sequence:'seq_user']
    }
}

Database IDs incrementing by more than one

1 Answers1