ansaurus

Question

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

Answer 1

A:

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.

S.Lott 2010-02-22 19:17:18

Spoken like a software engineer with many years of experience. :)

Terry Donaghe 2010-02-22 19:20:11

S.Lott, I don't like your tone. I think it's pretty clear what the OP means, and the question is not a bad one either.

Philippe Leybaert 2010-02-22 19:23:10

@Terry Thanks Terry for two things:.. 1. Guessing my experience without knowing my past..... 2. For telling everybody that programmers with less experience can't ask questions... 3. Your experience is reflected from your SO points... lol... There are much-2 better and experienced professionals on SO who believe in answering questions not posting useless comments like yours...If you don't know answers then please don't post filty comments...

Yatendra Goel 2010-02-22 19:26:06

@Philippe Leybaert: I regret not being as smart as you. The question is not clear to me. I don't know if it's good or bad; I can't understand it. I can only guess at it and ask for clarification.

S.Lott 2010-02-22 19:28:46

@S.Lott Actually the question is clear from my (and almost others) point of view.. but no problem if you have some doubts in getting my question as I have got my answer already.. I hope you can get the question by reading the accepted answer... and even then if you don't get the question.. then just post another question on SO :) and we (I and all of those who have understood the question and have answered them) will explain you in detail what I mean :)

Yatendra Goel 2010-02-22 19:37:42

@S.Lott: there are other ways of expressing your doubts. Your answer is very condescending.

Philippe Leybaert 2010-02-22 19:55:35

@Yatendra Goel: Actually, if someone asks for clarification, the question isn't really perfect. Claiming it's perfect seems like a difficult way to get information from people offering to help. I have no idea what words like "reliable" and "problem" mean in the question. Crash the DB? Crash the OS? Crash the application?

S.Lott 2010-02-22 19:56:23

@Philippe Leybaert: Instead of simply saying my questions are not good, can you suggest a better way to get clarification on the question?

S.Lott 2010-02-22 19:58:02

@S.Lott: I think you got up in a bad mood today, but you could have asked if the OP was concerned about duplicate ids being generated instead of throwing short and highly technical questions and remarks at him. But I'll rest my case. Read your reply again tomorrow, and I'm sure you'll know what I mean.

Philippe Leybaert 2010-02-22 20:03:10

S.Lott's answer was brief and to the point, yes, but that is not necessarily rude or condescending. I think if you're asking for help in a forum, it's incumbent upon you to assume good intentions from anyone who takes their time to reply unless they make it quite clear that their intent is to cause offense. S.Lott's question is a legitimate question, and clearly intended to help get at a good answer.

Nate C-K 2010-02-22 20:13:50

Woah guys! I just liked this answer. I apologize if anyone read more into what I said than what I meant. I didn't mean to belittle or make any comment about the person posting the original question.

Terry Donaghe 2010-02-22 20:20:58

@Philippe Leybaert: How did you know the question was about duplicate keys? What did I miss?

S.Lott 2010-02-22 20:47:25

@S.Lott: I thought it was pretty obvious, and it seems others thought so too. But I have to admit it doesn't state this explicitly.

Philippe Leybaert 2010-02-22 21:13:26

Reliable is pretty specific. Does this work as is intended? I get a number every time. I get a unique number every time. Its not too big a hit. What else could be unreliable?

Stephanie Page 2010-02-22 21:20:14

@Stephanie Page: I don't get the "reliable" means "unique number" from the question. In context, it's clear. But the question doesn't say that. Often people omit things from questions because they have hidden assumptions or related questions they're not asking. In this case, the obvious "no duplicates" never appears in the question, leading to wonder why it's missing. Why omit a specific reliability issue and write in a vague, general way? What's missing? I can't parse the question unless I assume something. Which I'm uncomfortable with. I prefer to ask.

S.Lott 2010-02-22 21:33:09

@Slott. Excellent. We agree. In context it is clear. The only way it's unclear is to TAKE IT OUT OF CONTEXT. See boys, we can come to an agreement. ----- I also agree that some people have bad assumptions that need to be corrected. The most polite way is to ask for clarifications in a -comment-, not in an -answer-. Or answer the question as it can be understood from the context. And after answering, state any concerns you may have about misconceptions. As in, "Q: what is 2+2", "A: 4, but only if you're not using base 3 or 4"

Stephanie Page 2010-02-22 22:58:10

@Stephanie Page: Sorry, I did not say that "reliability" was clear in context of this question. Indeed, it's the context of the question that made it unclear. I can guess -- based on context -- but that does not make it clear. I refuse to guess or assume (hence my reputation). While I'm aware of the issues around this topic, I refuse to guess; I ask for clarification.

S.Lott 2010-02-23 01:58:18

Answer 2

+13 A:

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.

Mark Byers 2010-02-22 19:17:47

You can, however, rely on last_insert_id, as that applies to the last inserted id over that connection.

Erik 2010-02-22 19:19:24

+1 thanks......

Yatendra Goel 2010-02-22 19:38:13

Erik, Is that SQL Server or MySQL? Mark, transactions have nothing to do with it... isolation level will determine whether a transaction can read dirty or not.

Stephanie Page 2010-02-22 21:13:19

It is not true that a duplicate will never be generated. It is possible with SQL Server to get duplicates on an IDENTITY column if the seeding gets screwed up. One need only try it by filling a table with an IDENTITY column with some values and using DBCC CHECKIDENT RESEED to reset the seed to 1.

Thomas 2010-02-22 21:17:58

@Thomas: Of course, you can get any strange result by "screwing up". All DBMSs allow you to change the seed of an identity generator, and they trust you not to use this power for bad.

erikkallen 2010-02-24 11:51:17

You make it sounds as if it cannot get screwed up from a design choice. I've see upsert routines that allow for passing a specific ID using SET IDENTITY_INSERT [Table] OFF.At the end of the day the answer to the question should be: is it safe to use an identity column with a primary/unique constraint? Yes. Is it safe without a constraint: Absolutely not.

Thomas 2010-02-24 16:17:42

Answer 3

+2 A:

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.

dsolimano 2010-02-22 19:18:31

Odds are that any method you used to generate unique keys on your own would be less reliable than what's built into the DBMS!

Jay 2010-02-22 20:11:52

Answer 4

+5 A:

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.

aefxx 2010-02-22 19:54:40

You have indicated a very important point to be considered... thanks for the efforts

Yatendra Goel 2010-02-22 20:14:04

You're welcome. This bugged me once for quite a week.

aefxx 2010-02-22 20:16:38

-1 I cannot reproduce this, and believe it is wrong. I just tried it (create a table with auto_increment, insert some values, delete some, restart server), and MySQL does *not* reuse values. Furthermore, if you `mysqldump` the table, the CREATE TABLE in the dump contains the snippet "AUTO_INCREMENT=5", i.e. MySQL *does* internally store the last autoinc id it used.

sleske 2010-02-22 23:47:00

It's not about "some" records, it's about the last one. Try it: create a table, insert 3 records, delete the last one, restart the server. Don't forget to upvote again.

aefxx 2010-02-22 23:56:57

@aefxx: I think you're right too (haven't tested it personally, but I've heard about this issue from friends). I've already upvoted you. But I think sleske won't be able to upvote you because it's not possible to change a vote unless you edit your post. It's a stupid SO rule.

Mark Byers 2010-02-23 00:13:16

Ah, now I see: aefxx's problem does exist, but only for InnoDB tables. I tested with MyISAM, which does not do this. Thanks for alerting me. I took away the downvote.

sleske 2010-02-23 01:45:16

@sleske Thanks for the upvote :D

aefxx 2010-02-23 07:43:39

Answer 5

A:

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).

Thomas 2010-02-22 21:15:29

Btw, if the IDENTITY seed gets messed up and it has a unique constraint or primary key constraint and tries to use a value that already exists, you will obviously get constraint violations (i.e., exceptions will be thrown back to the users).

Thomas 2010-02-22 21:22:02

Answer 6

A:

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.

sleske 2010-02-22 23:53:40

ansaurus

tags:

views:

answers:

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

related questions