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

[Kumar McMillan]

On Fri, Oct 10, 2008 at 8:53 AM, Lukasz Szybalski <szybalski at gmail.com> wrote:
> Hello,
> I was wondering if anybody has a good strategy for auto incrementing fields?
>
> I want to auto increment field called "case#" .
>
> I have a choice of database auto increment on field "case#" or do it
> myself? (correct? No other choices exists? or something in between?)
>
> 1. I would like to be able to do pick a number where we will start
> doing a case#?  (1,000,000+)
> 2. Reserver a case# for a special group which can auto increment case#
> between 2,000,000-2,999,999, and add them as they come.
> 3. I don't want to use (system_id)
>
> So it seems as the only way is to make my primary key:
> case# - unique key, primary, not auto incrementing and let some
> program manage auto incrementing.
>
> What options have worked for you in this situation?

Just make case_number a unique column.  Then all race conditions are
handled by the database.  If you want control over it don't make it
auto-incrementing, do that yourself.  case_number = last_case_number +
1.  As long as you do the select of the last case number *and* the new
insert all in one transaction, the database should be smart enough to
lock the right rows for you and all simultaneous selects of last case
number will block until the current insert transaction has committed.
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.

However, if your customers are saying they need to set ranges of case
numbers for special groups then it sounds to me like a much larger
problem: your users are putting special meaning on case numbers before
the cases are created.  Why?  It smells to me like other databases are
at work (like, *gasp* someone's personal Access database).  If that's
what's happening then you are essentially working with the
"distributed database" problem.  This is solved in a number of ways.
The easiest is to kick everyone in the groin and make them to stop
using Access because you just built a new db.  Another solution would
be the use of a central server that assigns node IDs to each database
(good luck integrating this into MS Access).  Then, all case numbers
are unique by node ID + case ID.  I.E. node 1 + case number 1 vs. node
2 + case number 1 are two unique case numbers.  Another approach is to
use hashes that uniquely identify each case by content somehow.  This
is how distributed version control works (google for Mercurial or git)
; it does not rely on any central server at all.

Kumar


>
> So right now options are:
> 1. let db auto increment
> 2. Hold the next case# in a separate database table, and let my
> program use it to find next case# value. How would I lock/unlock the
> next case# to make sure there is no race condition and each case#, and
> there is no holes?
> 3. Any other options?
>
> Have people used other strategy that is semi-automatic, and would work
> for these cases?
>
> Thanks,
> Lucas
>
>
>
> --
> Python and OpenOffice documents and templates
> http://lucasmanual.com/mywiki/OpenOffice
> Fast and Easy Backup solution with Bacula
> http://lucasmanual.com/mywiki/Bacula
> _______________________________________________
> Chicago mailing list
> Chicago at python.org
> http://mail.python.org/mailman/listinfo/chicago
>