[Chicago] auto incrementing - what options do we have?
David Durham, Jr.
david.durham.jr at gmail.com
Fri Oct 10 19:23:11 CEST 2008
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:
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 :).
More information about the Chicago