Definitely learn to think in terms of inserting the record and asking the database what ID was assigned. Don't try to "predict" what the "next" ID will be.

This misconception spawns what people call a "race condition." That's when two separate processes are generating results independently, but there are some tasks which should have been centrally coordinated to avoid mistakes or duplication.

If written the wrong way, your program will crash in flames or introduce very subtle bugs, depending on which database engine or even how many CPUs your machine has. What's worse is that the first few times you run it, it will appear to work perfectly fine... but then start flaking out as soon as you forget about the issue.

--
[ e d @ h a l l e y . c c ]

In reply to Re: Finding the next avaliable id in a SQLite database by halley
in thread Finding the next avaliable id in a SQLite database by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.