Brian Aker wrote:
Hi!
Auto-increments have always had a set behavior... some of this worked
well, and some of it causes bottlenecks.
1) They always increase in value in the table. You can use them as a
"what came before what".
2) Gaps don't (for the most part) exist).
What are people's feelings about the above assumptions? Is it ok to
break them, or would it be better to add a different type which did
not have the above assumptions, and leave the old ones in?
Gaps are a perpetual PITA. They are unavoidable (two concurrent
transactions, one rolls back). They are also guaranteed trouble from
auditors and bean counters -- as soon as they find out about them, they
demand an accounting for gaps. No matter how many times they get beaten
down, more come out of the woodwork.
A brief synopsis of the history of the feature:
I think I invented them at Interbase. We called them "generators".
They could be assigned by trigger or initialization clause on a field.
Unlike MySQL, they weren't tied to tables except by convention. I did
provide a mechanism to reset them, which, in retrospect, I deeply
regret. Nothing good ever came from a reset generator.
Oracle picked up the idea and called them sequences.
In Netfrastructure I called them sequences and used Oracle syntax.
Again, they weren't tied to specific tables except by convention. A
very nice feature, however, let the DBA define a multiplier per schema
and let a reasonably privileged session set a base. The sequences were
still incremented by one, but the return value from a next sequence
operation as <sequence> * <multiplier> + <base>. Used correctly, this
made sequences unique cluster wide, a very, very nice feature. It also
increased non-sequentiality. On the other hand, we trained users not to
tell bean counters and auditors about them.
My (eternally humble) person opinion is that auto-increment fields are a
bad implementation of a badly thought out, feeble feature. Having them
reset on a truncate is particularly (no, Jim, don't say that), well,
shortsighted.
The "what came before what" feature flatly doesn't work in transactional
systems with overlapping transactions unless you block on
auto-increment, which is ridiculous.
--
Jim Starkey
President, NimbusDB, Inc.
978 526-1376
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
