On Mon, Oct 18, 2004 at 01:19:35PM +1000, Jamie Wilkinson wrote:
> This one time, at band camp, Michael Kraus wrote:
> >
> >No, this definetly opens up race conditions and lots of rollbacks and
> >reprocessing.
> >
> >Eg (in pseudocode).
> >
> >id = execute("SELECT MAX(id) FROM tablename") + 1;
> >...
> >result = execute("INSERT INTO tablename VALUE(id, ....)");
> >if (!result) { rollback(); repeat(); }
>
> Your +1 is broken.
>
> id = select nextval(sequence);
> insert into tablename (id, ...) values ($id, ...)
So, the key part that I feel isn't being explained here is that this is
a common problem when you're dealing with databases, and any decent
database will already have mechanisms to deal with it that have been
thoroughly tested and will likely be tuned for good performance too.
Postgres has the notion of "sequences", which are entities distinct from
any table that you can use to get a new, unique number, atomically.
Now, in the case of just fetching a value from a sequence, there's no
race-condition. It's an atomic operation (as far as I'm aware). Note
that once a number is grabbed from the sequence it will *never* appear
again. Not even if you roll the transaction back.
Now, someone will probably correct my rather rash use of the word
"atomic", but I think you follow what I mean.
HTH,
James.
--
"Now, there are no problems only opportunities. However, this seemed to be an
insurmountable opportunity."
- http://www.surfare.net/~toolman/temp/diagram.html
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
