CODEWITHSUNDEEP
javamysqlsqljpaeclipselink
Thang Pham
Thang Pham

Reputation: 38705

Can I put a MAX value for the database table primary key?

Can I put a MAX value for the database table primary key, either via JPA or at the database level? If it is not possible, then I was thinking about

  1. Create a random key between 0-9999999999 (9999999999 is my MAX)
  2. Do a SELECT on the database with the newly create key, if return object is null, then INSERT, if not repeat go back to step 1

So if I do the above, two questions. Please keep in mind that the environment is high concurrent:

Q1: Does the overhead of check with SELECT, if not there, INSERT significant? What I really mean is: is this process normal, since usually I let the DB create a unique PK for me?

Q2: If Q1 does not create significant performance degradation, can I run into concurrent issue? For example, if P1 with Id1 check the table, Id1 is not there, it ready to insert, P2 sneak in insert Id1 before P1 could. So when P1 insert Id1, it fails. I dont want the process to fail here, I want it to go back up the loop, find a new id, repeat the process. How do I do that?

My environment is SQL and MYSQL db. I use JPA with Eclipselink implementation

NOTE: Some people question my decision to implement it this way, the answer is exact what TravisJ suggest below. I have a very high concurrent environment. When a process kick off, I need to create a request to another process, passing to that process a unique 10 character long id. Since the environment is high current, I want to leverage the unique, not null feature of PK. The request contain lot of information in it, so I create aRequest table, with the request Id as my PK. I know since all DB index their PK, query out the PK is fast. If there are better way, please let me know.

Upvotes: 2

Views: 1742

Answers (3)

James
James

Reputation: 18379

See,

http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing

and,

http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Advanced_Sequencing

JPA already has good Id generation support, it does not make sense to implement your own.

If you are concerned about concurrency and performance, and using MySQL, I would recommend using TABLE generator with a large preallocation size (on other databases I would recommend SEQUENCE generator). If you have a lot of data, ensure you use a long for your id.

If you really think you need more than this, then consider UUID id generation. EclipseLink 2.4 with provide a @UUIDGenerator.

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53830

Why not start at 1 and use auto-increment? This will be much more efficient because you will not get collisions, which you must cycle through. If you run out of numbers, you will be in the same boat either way, but at least going sequentially, you won't have to deal with collisions.

Imagine trying to find an unused key when you have used up 90% of your available numbers. That will take some time, and there is always a possibility that it never (in your lifetime) finds an unused key if you are generating them randomly.

Also, using auto-increment, it's easy to tell if you're close to the limit (SELECT MAX(col)). You could script an alert to let you know when you need to reset. For the random method, what would that query look like?

If you're using InnoDB, then you still might not want to use a primary key. Inserting random records into a clustered index is a performance hit since the actual table data must be reordered. Instead use a unique key (with an auto-increment primary key).

Using a unique index on the column in question, simply generate a random number in the range and attempt to insert it. If the insertion fails, then generate a new number and try again. If the insert succeeds, then proceed. This accounts for the concurrency issue.

Still, the sequential auto-increment key is going to yield better performance.

Upvotes: 2

KeithS
KeithS

Reputation: 71565

You can implement a Check Constraint in your table definition:

CREATE TABLE P
(
P_Id int PRIMARY KEY NOT NULL,
...
CONSTRAINT chk_P_Id CHECK (P_Id>0 and P_Id<9999999999)
)

EDIT: As stated in the comments, MySql does not honor CHECK constraints. This is a 6-year old defect in the bug log and the MySql team has yet to fix it. As MySql is now overseen by Oracle Corp, it may never be fixed (simply considered a "documented limitation", and people who don't like it can upgrade to the paid DBMS). However, this syntax, and the check constraint feature itself, DO work in Oracle, MS SQL Server (including SQLExpress/MSDE), MS Access, Postgre and SQLite.

Upvotes: 2

Related Questions