Well, there's no law that says that you can't "waste" id's. Get the next id
& update before entering the transaction, I'd say. Or, if your transactions
are in batches, get the id, add a hundred(or some other arbitrary but large
enough number), and use all of those sequentially in this process.
Here's one I encountered recently that I don't recommend: randomly generate
each of 8 integers, then read the file to see if it already exists. If it
does, generate 8 more... there were times where it took 20 minutes to find
an unused one. The idea was to avoid this kind of bottleneck, but once files
started to contain millions of records, there was a problem. Solution?
prefix with a julian date...
"Our greatest duty in this life is to help others. And please, if you can't
help them, could you at least not hurt them?" - H.H. the Dalai Lama
"When buying & selling are controlled by legislation, the first thing to be
bought & sold are the legislators" - P.J. O'Rourke
Dan Fitzgerald
From: "Stevenson, Charles" <[EMAIL PROTECTED]>
Reply-To: [email protected]
To: <[email protected]>
Subject: [U2] Best practice for Sequential IDs using TRANSACTION START &
COMMIT/ROLLBACK
Date: Fri, 10 Jun 2005 18:39:30 -0400
For files with sequential numbers as record IDs, it is common practice
to use a control item (in dictionary or in a special control table) to
get the next sequential id counter, then write back the incremented
counter ASAP so it is available for the next process that needs to do
the same. Some variation on this theme (probably involving a utility
subroutine):
READVU SEQ.ID FROM ctrl.fvar, ctrl.id, n ...
WRITEV SEQ.ID+1 TO ctrl.fvar, ctrl.id, n
WRITE NEW.REC TO fvar, SEQ.ID
===
BUT:
===
If that happens inside an explicit transaction bounded by TRANSACTION
START and TRANSACTION COMMIT (or TRANSACTION ROLLBACK), the Sequential
ID control record will not actually be written and/or released until all
updates are done/ditched during commit/rollback. If the transaction is
extensive and involves many updates, this could be a serious bottleneck.
Making sure that you read the next sequential id as close to the moment
of executing the commit would be helpful, but that is not always an
option in complicated transactions.
What is the best practice for handling this? What is your experience?
Bottlenecks?
Ideally, I know what I'd prefer: I wish U2 would handle sequential ids
internally for variants of Type-2 or dynamic SEQ.NUM files. That should
have happened a couple decades ago in PI and Pick. But back to the real
world. What is the best way to handle sequential IDs when using
explicit TRANSACTIONs?
cds
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
