Primary Key. Auto update or manual update?

[Mark Hershberg]

Hello,
I'm developing a web based database driven application.
In my database tables, I have an integer column for primary key.
Currently, I'm using the auto update of the mysql for the updating of primary key, whenever a new record is created.
In some example applications that I have read, they do this manually. i.e. retrieve the value of last primary key using the max(x) function and then add 1 to it as the new primary key for the new record.

What I want to know, is which method is better to use? what are the pitfalls of using one method over the other?

Another question is that when using the auto update feature, does the first record always start with 1? or can I give it a startign value?

Your responses are greatly appreciated.

[Jesper de Jong]

...they do this manually. i.e. retrieve the value of last primary key using the max(x) function and then add 1 to it as the new primary key for the new record.

The problem with this is that it's not safe if there are multiple processes or threads updating the database at the same time. Suppose there are two threads, then the following might happen:

1. Thread A does "select max(id) from table" and gets the answer: 1234.
2. Thread B does "select max(id) from table" and gets the answer: 1234.
3. Thread A inserts a new record with id = 1235.
4. Thread B inserts a new record with id = 1235 -> ERROR, unique constraint violated.

The problem with the first method (letting MySQL create a new ID automatically) is that it's difficult to get the ID of the newly created record reliably. You could use the method Statement.getGeneratedKeys() to do that, but maybe not all database drivers support that method (I don't know if the MySQL JDBC driver does).

[Paul Clapham]

I'm using version 4 point something of MySQL in Java 5 and it does support the getGeneratedKeys() method.

[Paul Sturrock]

Better than tying yourself into a database-specific solution (such as the autonumber data type or sequences), or be restricted by driver implementations, you should consider using a proper key generation algorithm. For example the high-low algorithm which apes Oracle's sequences. For this you create a high key table for each table in your database. When a process wants to create a new row in a table, the process creates a lock on that table's high key table, reads the value in there and increments it, then releases the lock. Now this process knows that it has a unique value for this primary key, regardless of how many other processes tried to add a new record at the same time. It also means the process has the PK value (something which people have already pointed out is a headache with autonumber).

This is by no means the best, or only, strategy - it just happens to be the one implemented in the application I am currently working on. Google for "key generation patterns"; you are sure to turn up lots of ideas.
[ November 24, 2005: Message edited by: Paul Sturrock ]

[Stan James]

If you feel the overhead of going to the database for a new key every time is too much, it's common to build a two part key with the first coming from a key table or sequence number and the second being a simple incrementing integer. For example in my current system we get a 10-digit number from the database and increment a 3-digit suffix. With punctuation for use by humans the first key looks like:

00000-00001-001

We have a cluster of six servers. Every one of them gets a new 10-digit part at startup and any time they run through 999 keys. I figure when we get to 99999-99999-999 I'll start over with different punctuation, maybe 0000-000000-001. I'm not too worried about it today.

BTW, Paul: I thought the incrementer was the "low" part of high-low. Does that sound right? I first read this in a Scott Ambler article years ago but don't recall if he used that name for it.

[Paul Sturrock]

Yeah, the "low" is the increment value. And yes, Scott Ambler was where it originally came from. I can't remember if that's how he labelled it; its a similarly long time since I read the article myself.