What are the difference between: sequence id using JPA @TableGenerator, @GeneratedValue vs database Auto_Increment

Question

Q1.: What is the difference between applying sequence Id in a database using

A.

CREATE TABLE Person
(
   id long NOT NULL AUTO_INCREMENT
   ...
   PRIMARY KEY (id)
)

versus

B.

@Entity
public class Person {
    @Id
    @TableGenerator(name="TABLE_GEN", table="SEQUENCE_TABLE", pkColumnName="SEQ_NAME",
        valueColumnName="SEQ_COUNT", pkColumnValue="PERSON_SEQ")
    @GeneratedValue(strategy=GenerationType.TABLE, generator="TABLE_GEN")
    private long id;
    ...
}

My system is highly concurrent. Since my DB is a Microsoft SQL server, I do not think it supports @SequenceGenerator, so I have to stay with @TableGenerator which is prone to concurrency issues.

Q2. This link here (http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Advanced_Sequencing) suggests that B might suffer from concurrency issues, but I do not understand the proposed solution. I would greatly appreciate it if someone could explain to me how to avoid concurrency issues with B. Here is a snippet of their solution:

If a large sequence pre-allocation size is used this becomes less of an issue, because the sequence table is rarely accessed.

Q2.1: How much allocation size are we talking about here? Should I do allocationSize=10 or allocationSize=100?

Some JPA providers use a separate (non-JTA) connection to allocate the sequence ids in, avoiding or limiting this issue. In this case, if you use a JTA data-source connection, it is important to also include a non-JTA data-source connection in your persistence.xml.

Q2.2: I use EclipseLink as my provider; do I have to do what it suggests above?

Q3. If B suffers from concurrency issues, does A suffer the same?

Answer 1

Using a TableGenerator the next id value will be looked up and maintained in a table and basically maintained by JPA and not your database. This may lead to concurrency issue when you have multiple threads accessing your database and trying to figure out what the next value for the id field may be.

The auto_increment type will make your database take care about the next id of your table, ie. it will be determined automatically by the database server when running the insert - which surely is concurrency safe.

Update:

Is there something that keeps you away from using GenerationType.AUTO?

GenerationType.AUTO does select an appropriate way to retrieve the id for your entity. So in best case in uses the built-in functionality. However, you need to check the generated SQLs and see what exactly happens there - as MSSQL does not offer sequences I assume it would use GenerationType.IDENTITY.

As said the auto_increment column takes care about assigning the next id value, ie. there is no concurrency issue there - even with multiple threads tackling the database in parallel. The challenge is to transfer this feature to be used by JPA.