On Friday 23 November 2001 12:28 am, Jason Wharton wrote:

> > > Your system will fail under heavy multi-user load, count on it.
> >
> > No it won't. Sorry but I've done the above and tested heavily...
>
> You have to give up one or the other. You cannot have sequentiality and
> contiguousness in a multi-user environment without going to a second phase,
> which means you cannot do it with a primary key at the same time. If you
> don't accept this statement, then we simply need to agree to disagree. I
> don't think I can make it any more clear that I have.

I don't know what you mean by a second phase. Perhaps if I explain what I'm
doing in detail, you can explain to me what you see wrong with it:

1. At the time of creating data, say perhaps an invoice, I need to allocate
the next number. Note that this next number is *not* shown on the invoice
screen before it is created but instead there is an id field that will get
the next number automatically if its left blank. After the data is filed a
message shows up that tells the user the data was filed successfully, shows
them the invoice number, and asks them if they want to print it.

This might be a key point in how you see things. If you decide that it is
required to show what the next invoice number is going to be before it gets
created, then you certainly would have a hell of a lot more trouble with the
unbroken sequential numbering. But at least in my history (and I have
hundreds of stores using my invoicing software), it is *not* a requirement
that they see the number ahead of time. It is much more important that they
have an unbroken sequence of numbers.

2. The process of creating an invoice goes like this:

try {
Id number = getUniqueId("invoice", "number");
...generate create statements for invoice and execute...
commit();
invoice.setNumber(number);
} catch (...) {
rollback();
}

3. The getUniqueId routine uses an id table based on a table name and field
name. It does a select on id_table first to get the row locked (yet I know
Firebird doesn't really work with locks but the end result works out to the
same thing) and then gets the next available number and increments it. It
does not do a commit so any other client that tries to allocate an invoice
number while this is going on will block until the current client is
finished. Because the next available number is incremented in the
transaction, it will only update at the end when the whole invoice has been
written. If there is a problem, the whole thing is rolled back and the
number will not be assigned to the invoice and returned to the caller and the
next client that wants an invoice number will get the same one.

There is actually more details in there. Since I pass in the table name and
column name to the routine, it can verify that the next number allocated is
indeed unique with a simple select statement (in some cases the user is
allowed to either enter in their own number or else have the system assign
one so it has to jump over the ones already used in that case). If the
number is already used, it can do another simple select to get the max number
allocated and then use the next one from there.

The only problem I have with the above code is that it means there is high
contention on the id table. But any modern database effectively does row
level locking so that the contention is limited to other clients doing the
exact same thing (ie: a client entering journal entried won't conflict with
one doing invoices). And the largest transaction type (customer invoices)
takes a fraction of a second to create as I mentioned so it would take a very
large number of users generating invoices at exactly the same time to really
cause a problem. Someone else mentioned slow connections but this is all
done on local area networks with at least ethernet speeds so thats not a
problem.

The benefits of this way of doing things is that it satisfies user
requirements (and its always easier to just do this rather than try to
convince the user that they are wrong!), its database independent so that the
same code work on Sybase and PostgreSQL databases as well as Firebird, and
the one generic C++ routine can handle a wide variety of sequential numbering
requirements rather than creating triggers, generators, and stored procedures
for every table.

Now I acknowledge that this way of generating numbers will not work in all
cases. Its easy to construct cases where it doesn't work well at all. But
that doesn't mean its not a useful technique when appropriately used just
like any other tool and dismissing it out of hand is dogmatic. Its like
dismissing goto out of hand. Its a lot better I think to explain both when
it shouldn't be used and when it should rather than just saying "its bad" and
teaching nothing about why.

--
Brad Pepers
brad@...