Creating a safe sequence generator

Rails
1

Hi all,

I’ve got a rails and database question. Accounts have jobs, and job
numbers should be assigned per account. Account 89 should have jobs
1-whatever, which are not the same as account 67, which has jobs
1-whatever.

In the past, I have accomplished this quite easily with:

create table job (
accountid int(10) unsigned not null default 0,
jobid int(10) unsigned not null auto_increment,
… other fields …
primary key (accountid, jobid)
);

Then, when inserting into the table, mysql takes care that the jobid is
the highest for the given account, and unique as well.

The rails way is to create one numeric key for the whole table, and then
use a specifically named column for the foreign key:

create table jobs (
id int(10) unsigned not null auto_increment
account_id int(10) unsigned not null default 0,
jobseq int(10) unsigned not null, # not assigned by mysql :frowning:
… other fields …
primary key (id)
);

Which is workable, but now the job numbers aren’t specific to the
customer, as I would like them to be. In fact, in this model, they are
not supplied or managed by the database at all.

Is there a safe way to generate the next jobseq during the insert
operation, such that the jobseq is the next job for the given customer,
and, most importantly, two jobs for the same customer cannot have the
same jobseq assigned, even accidentally?

Many thanks.

Regards,
Rich

2

On 7/19/06, Rich D. [email protected] wrote:

accountid int(10) unsigned not null default 0,

not supplied or managed by the database at all.

Is there a safe way to generate the next jobseq during the insert
operation, such that the jobseq is the next job for the given customer,

You could either:

  1. Give each account a next_jobseq field and increment this value
    along with creating the job, inside a transaction with the serialized
    isolation level.
    or
  2. Lock the jobs table, find max(jobseq)+1 for that account and create
    the job before unlocking.

#1 obviously scales better, as it will let you create jobs for
different accounts concurrently

and, most importantly, two jobs for the same customer cannot have the
same jobseq assigned, even accidentally?

Most efficient/secure approach is no doubt a unique constraint in the
db.

create table jobs (
id serial,
account_id integer,
jobseq integer,
primary key (id),
unique (account_id, jobseq)
);

Rails also has the validate_uniqueness_of validation. See
http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#M000816.

I think the AR validations add a lot of overhead myself, but figure
there may be advantages to using them in addition to db level
constraints.

Isak

3

Rich,
I wrote an acts_as_sequenced extension recently that should work
nicely here. The code is on my blog. Here is the relevant post:

http://www.depixelate.com/articles/2006/07/19/acts-as-sequenced

Zack