[Chicago] auto incrementing - what options do we have?

[David Durham, Jr.]

On 10/10/08, Lukasz Szybalski <szybalski at gmail.com> wrote:
> On Fri, Oct 10, 2008 at 11:43 AM, David Durham, Jr.
>  <david.durham.jr at gmail.com> wrote:
>  > On 10/10/08, Kumar McMillan <kumar.mcmillan at gmail.com> wrote:
>  >>  Actually, you might have to explicitly declare table level locks as
>  >>  opposed to row level locks.  Most dbs support LOCK TABLES, i.e. for
>  >>  myql : http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html which
>  >>  is what you want in this case.
>  >
>  > Right, but locking is not the only way to ensure transaction
>  > integrity.  It's better to stick with simply setting transaction
>  > isolation to what you need and letting the db deal with whether or not
>  > it will use locking or something like MVCC.
>
>
> So what would be the transaction isolation in this case? Is that
>  session option or do I change it only on first insert?

I think you want repeatable read or read serializable.  Here's a
Google feeling lucky for read serializable:

http://www.devx.com/getHelpOn/10MinuteSolution/16488/1954

With that said, you may find that dealing with id concurrency issues
within your database doesn't match your use-case, which would most
likely be high-volume distributed stuff (so far as I know).  In such
cases, it's perfectly reasonable to generate your own universally
unique id's and there are known algorithms for doing so (I think they
often rely on a mac address and timestamp to seed the algorithm).
UUIDs are not, in my experience, auto-incremented values, but if you
absolutely need auto-incremented values in a distributed high-volume
app then you or someone you know is probably just being weird :).

HTH,
Dave