Is auto-primary-key generation feature of Databases reliable?

I have developed a web application and various clients can store records to that database simul开发者_运维问答taneously.

I have set the auto-primary-key generation functionality of that database that generates a primarykey automatically (auto-increement) whenever a new record is added to the database.

So my question is Can I rely on this auto-key-generation feature?

Would there be no problem if various users are storing records simultaneously?

Yes, the auto increment primary key is reliable in that a duplicate key will never be generated. You have to be careful how you use it though.

• You should not assume that it will always increase in increments of exactly 1 (or whatever you set it to).
• You should not insert an entry and then without using transactions expect to be able to select max(id) to get the id of the record last inserted.

I'd like to add one more thing to keep in mind when using the auto_inc feature of MySQL:

Imagine you have a table employees that uses auto_inc and (for whatever reason) a second table named former_employees. Let's further pretend that every employee would have an unique ID (therefore the auto_inc) attached to him - and that he won't even lose it due to dismissal or quitting.

For performance reason (let's just imagine the company has several billion employees) your company moves the records of former employees to the homonymous table.

Now here's a snapshot of the two tables (don't mind the small IDs now):

employees                       former_employees
------------------------        ------------------------
id   |  name  |  ...            id   |  name  |  ...
------------------------        ------------------------
27   | Peter  |  ...            29   | Andrew |  ...
28   | Jacko  |  ...            30   | Dennis |  ...
32   | Paula  |  ...            31   | Lenny  |  ...
                                33   | JoDon  |  ...

Notice that former_employees last ID equals 33 and that employees auto_inc counter equals 34 right now.

If you'd shutdown the server at this stage and restart it, employees auto_inc would jump back to 33!!! That's because MySQL doesn't store the auto_inc counter between restarts!

Keep that in mind, though. Regards.

PS: To circumvent this "feature" you would have to trigger stored procedures that look at former_employees last ID and set that if greater.

Note (S.Leske): This applies to InnoDB tables, but not to MyISAM tables. Don't know about other table engines.

The database developers have taken the necessary precautions to ensure that keys are not duplicated. No software is ever free of bugs, of course, but I've never observed a problem with this functionality.

What do you mean by "reliable"?

Databases use locks.

There's no "simultaneously" anywhere in a database. Resources are locked, and write access is serialized.

What are you asking? Please clarify "reliable" in your question.

Are you asking whether it is ever possible to get a duplicate key on an IDENTITY column that does not have a unique constraint? Yes, it is possible (thus the need for DBCC CHECKIDENT RESEED) albeit the probability is low. I have had it happen to me on a handful of occasions with SQL Server over the past couple of decades. The only way to reliably guarantee that any field will be unique is to use a unique constraint (or primary key constraint).

Yes, this functionality works reliably. Countless systems (ours included) use them in production every day. The DBMS contains the appropriate precautions to never generate the same value twice.

There is one caveat: You can insert duplicate values into a column if you insert them explicitly (if the DBMS allows explicitly setting the value of an autoinc column, which most do). This may seem trivial, but you can set up "time bombs" if you (accidentally) insert an explicit value into an autoinc column that is greater than the current autoinc ID.

This will work fine, until the autoinc counter reaches the value you inserted, then "boom" (i.e. constraint violation).

This can happen e.g. if you're copying records from another database, or restoring a backup. We had the problem on our systems, and now routinely "resynchronize" autoinc counters: After a "dangerous" update such as copying an entire DB, we retrieve the highest value in use for each autoinc column, and set the autoinc counter to the value +1.