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?

PepperBob · Answer 1 · 2012-04-09T18:10:58.740

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.

When you said: `The challenge is to transfer this feature to be used by JPA.`, do you refer to the use of `@GeneratedValue`? So I try to set the column in the table `AUTO_INCREMENT`, I then try to create multiples `Person`. The first person create correctly with the `id` start out to be 5, but it generate an exception for person 2. `Null or zero primary key encountered in unit of work clone`. Can we not just annotated the id with `@Id`, without `@GeneratedValue`, then persist the entity object, and let the database with `AUTO_INCREMENT` PK take care of it? — Thang Pham, Apr 11 '12 at 20:13
Can you show your mapping? You should use `@GeneratedValue` to tell the JPA implementation that the ID is taken care of. Here's one link to help you get going: http://www.developerscrappad.com/408/java/java-ee/ejb3-jpa-3-ways-of-generating-primary-key-through-generatedvalue/ — Miguel Ping, Apr 16 '12 at 12:58

score 15 · Answer 2 · edited Nov 20 '18 at 07:25

A: uses IDENTITY id generation, @GeneratedValue(IDENTITY)

B: uses TABLE id generation

JPA supports three types, IDENTITY, SEQUENCE and TABLE.

There are trade-offs with both.

IDENTITY does not allow preallocation, so requires an extra SELECT after every INSERT, prevents batch writing, and requires a flush to access the id which may lead to poor concurrency.

TABLE allows preallocation, but can have concurrency issues with locks on the sequence table.

Technically SEQUENCE id generation is the best, but not all databases support it.

With TABLE sequencing if you use a preallocaiton size of 100, then only every 100 inserts will lock the row in the sequence table, so as long as you don't commonly have 100 inserts at the same time, you will not suffer any loss in concurrency. If you application does a lot of inserts, maybe use 1000 or larger value.

EclipseLink will use a separate transaction for TABLE sequencing, so any concurrency issue with locks to the sequence table will be reduced. If you are using JTA, then you need to specify a non-jta-datasource to do this and configure a sequence-connection-pool in your persistence.xml properties.

Your information about `TABLE sequencing a preallocaiton size` help me lot as I decide to use `TABLE` sequence for my implementation. You should have this bounty, I will wait out a bit more to see if anyone have any other idea to contribute, if not, I will grant you the bounty. thank you very much — Thang Pham, Apr 11 '12 at 20:26

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

2 Answers2

Linked