Sequence number
J?rg Knebel (7/19/08 1:17PM)
Bill Desjardin (7/20/08 12:42PM)
Bill Desjardin (7/20/08 1:00PM)
Bill Desjardin (7/20/08 1:04PM)
Bill Desjardin (7/20/08 1:09PM)
Ortwin Zillgen (7/21/08 7:52AM)
David Dancy (7/21/08 9:58AM)
J?rg Knebel (7/21/08 10:51PM)
Bernd Fr?hlich (7/30/08 9:51AM)
Bill Desjardin (7/30/08 11:46AM)
J?rg Knebel (7/19/08 1:17 PM)
Hi Bill,
On 19/07/2008, at 09:14 :17GMT+10:00, Bill Desjardin wrote:
I know that the Sequence number command is a regular topic on the
list. I also know that the last time it was discussed the command
was said to work reliably as documented.
I can confirm the reliability of SEQUENCE NUMBER.
The problem you described reminds me of version 2.x / 3.x of 4D where
it was possible to get big mess in regards to SEQUENCE NUMBER after
deletion of records.
But this should not happen any more.
It might be a long shot, but assigning the sequence number during 'On
Record Load' when the record status is 'Is New Record' and at the
same time another client in client / server or another process tries
to create a record in the same table could cause such a thing.
Still it's a wild guess so don't quote me on this. ;-)
I don't have the time to try to reproduce what you're describing but
here a couple of quickies:
- assign the sequence number just before finally saving a new record
- use a semaphore to create the sequence number for all tables in ONE
routine
*****************
`Get a unique record ID for a table
`SO_GetRecSequenceNumber ( TablePtr; IDFieldPtr {; vldiffNumber})
--> Sequence number
` Modified :16/08/06, 15:52:03, TTT Data Systems P/L Jörg Knebel
C_POINTER($1;$2)
C_LONGINT($0;$3;$min)
C_STRING(255;$vsTable)
$vsTable:="TableNo"+Table name($1)
While (Semaphore($vsTable)) `checks to see if another user/process
is requesting a sequence number for the same table
IDLE
DELAY PROCESS(Current process;2)
End while
Case of
: (Count parameters=2)
$2->:=Sequence number($1->)
Else `temp sequence number
PUSH RECORD($1->)
ALL RECORDS($1->)
$min:=Min($2->)-1
REDUCE SELECTION($1->;0)
POP RECORD($1->)
$2->:=$min
End case
$0:=$2->
CLEAR SEMAPHORE($vsTable)
***************************************
- flush buffers after saving
Please help. This is driving me crazy.
If you need a sample how to clean up 'SEQUENCE NUMBER - mess' contact
me off list.
Cheers
Jörg
Bill Desjardin (7/20/08 12:42 PM)
Jörg,
On Jul 18, 2008, at 8:17 PM, Jörg Knebel wrote:
Hi Bill,
On 19/07/2008, at 09:14 :17GMT+10:00, Bill Desjardin wrote:
I know that the Sequence number command is a regular topic on the
list. I also know that the last time it was discussed the command
was said to work reliably as documented.
I can confirm the reliability of SEQUENCE NUMBER.
Is your reliable experience with Sequence number a result of your use
of the semaphore? What experience has led you to the conclusion that
the semaphore is necessary. This is the first that I have heard of a
semaphore being used with the Sequence number command?
It might be a long shot, but assigning the sequence number during
'On Record Load' when the record status is 'Is New Record' and at
the same time another client in client / server or another process
tries to create a record in the same table could cause such a thing.
Still it's a wild guess so don't quote me on this. ;-)
Have you had any experience that leads you to suspect that creation
of a subrecord via the Add Subrecord automatic action works any
differently than a CREATE RECORD in terms of the operation of the
Sequence number command? As I step through the code, the Sequence
number increments after the SAVE RECORD, in this case, just the same
as it does when the new record is created with a CREATE RECORD
command. I have not actually managed to step through code, however,
when the issued sequence number was a duplicate.
- flush buffers after saving
Is this superstition, or have you actually seen a difference.
If you need a sample how to clean up 'SEQUENCE NUMBER - mess'
contact me off list.
Thanks for this, but I have had practice at this in the past.
I thank you for your help. The reason that I ask these questions, is
that I want to understand the problem and fix that, rather than throw
fixes that I don't understand at the problem
Bill Desjardin
Bill Desjardin (7/20/08 1:00 PM)
Ortwin,
On Jul 19, 2008, at 1:06 AM, Ortwin Zillgen wrote:
I can confirm the reliability of SEQUENCE NUMBER.
me too: SEQUENCE NUMBER just works, anywhere between creating a
record or during On Load with Add record or anytime later including
trigger.
So please tell the whole story, not just what you think is
important :-)
The whole story is difficult to tell because of the variety of ways
that the Sequence number command is used. The problem came to light
as a result of testing some new code. In the test scenario, the
sequence number is assigned after a CREATE RECORD and just before a
SAVE RECORD. It is happening in a new process and within a
transaction. After becoming aware of duplicate IDs, I looked at
another table and found duplicate IDs there as well. The other table
does not have any records being created inside a new process. It
does, however, have records being created inside of transactions.
Because I have not been able to create the duplicate sequence numbers
upon demand or ever within a code trace, I do not know exactly which
code is causing the problem. There is very similar code within other
tables where duplicate IDs have never been found. The tables where I
found duplicate IDs are both related many records that may be created
within subforms in a parent record's input form. The tables are
[Purchase_Order_Detail] and [Payable_Detail]. The IDs have been
added to these tables within the last year or so to support an audit
trail function. I noticed the problem in 4D version 2004. I don't
know if it is a problem unique to 4D 2004, or that I just did not
have the ID fields in 4D 2003 long enough to notice the problem.
Thanks for your help.
Bill Desjardin
Bill Desjardin (7/20/08 1:04 PM)
Walt,
Sorry to hear that you share my problem. The good news is that we
get double the mileage out of any help that is offered. Have you
managed to pinpoint any specific offending code?
Bill
On Jul 19, 2008, at 3:18 AM, Walt Nellson wrote:
Bill
What a coincidence. Just this morning, I discovered the same thing:
duplicate sequence numbers in a table where I use the built-in 4D
Sequence Number command. This is 2004.7 R4.
Bill Desjardin (7/20/08 1:09 PM)
Bill
On Jul 19, 2008, at 9:44 AM, Bill Weale wrote:
Such as: are transactions involved?
Yes, transactions are involved. Have you seen problems with the
Sequence number command that are unique to use within transactions?
It seems to me that the last time we had Sequence number discussed on
the Nug, the conclusion was that the command just works, regardless
of transactions.
Bill Desjardin
Ortwin Zillgen (7/21/08 7:52 AM)
So please tell the whole story, not just what you think is
important :-)
The whole story is difficult to tell because of the variety of ways
that the Sequence number command is used. The problem came to light
as a result of testing some new code.
in a situation like yours, I'd
-use triggers
-if the sequence number field isn't zero, I'd assign the sequence
number
-do a Find index key(sequence number field;just assigned sequence
number) and if
-if the result is less than zero,
-trace or
-mark the record or
-send and error or
-write to the error-protocoll
Regards
Ortwin Zillgen
___________________________________________________
Stadt Land Fluþ * Geographic Informationsystems
<mailto:dddd@... <http://dddd.mettre.de?4713>
<http://dddd.mettre.de/geodddd.shtml?4714>
David Dancy (7/21/08 9:58 AM)
<650f557e0807201658i20bc1424u9174a3caa705d65d@...
Bill / Walt
My experience with Sequence number (SN) dates back to well before v3
and I've _never_ found a problem that wasn't attributable to a logical
error in my own code. (There was the old old bug that reset the SN
when 4D Tools compacted a table, but that disappeared aeons ago. It's
so old and so not relevant that I probably shouldn't even mention it
lest anyone start worrying!)
Based on my experience with 4D, some things to look out for include
the following. You're probably aware of all of them, but just in case
you're not:
1. 4D's record buffer sticks around even after you cancel a
transaction. Thus it's possible for the value in a field assigned by
SN to persist if you don't explicitly clear the record buffer (use
CREATE RECORD, UNLOAD RECORD, or load a different record in its place)
after you've created a new record and given it a SN. If your record
creation logic doesn't always explicitly create a new record, there
may be old values knocking around in the record buffer that could
stuff things up for you. (Technical terms!)
2. I try to "wrap" the record-manipulation commands of 4D so that they
do what _ I _ want, not necessarily what 4D wants. So, for example, I
would wrap the CREATE RECORD command and make sure that a new record
always gets a new unique ID. Then, if I find that strategy isn't
working, it's easy to modify it for the whole database by changing
just one method.
3. The SN command will consume a new number for a given table if you
have a transaction open _whether or not you save the record_. I can't
remember if the internal SN counter is incremented on CREATE RECORD or
on each calll to Sequence number (sorry, no time to test it either).
So you could have 2 processes creating records in the same table, and
provided that each one has a transaction open, SN will give out new
numbers for all records of that table created in each process. You
don't have to save the record in process 1 before creating a new one
in process 2; SN just gives out new numbers when it's called in the
context of a transaction irrespective of whether you've saved the
record with the previous number it gave out. This is completely safe
and reliable, but of course it could result in "holes" in your SN
sequence.
4. If your 2 processes are not running transactions, you _must_ save
the new record in process 1 before assigning a SN in process 2 to a
record of the same table, otherwise the SN may be a duplicate. Outside
of a transaction, the internal SN counter seems to only increment when
a record is actually saved. This synchronisation requirement is a
fertile source of bugs, and I'd look for this scenario before
suspecting a problem in 4D.
One way around the problem is to only assign SN's inside a trigger,
when you know that the record is about to be saved. This has the
advantage that it doesn't matter whether you're in a transaction
context or not. However, the disadvantage is that you never know the
SN until the record has actually been saved, which can be inconvenient
in a Master-Detail type of arrangement, especially for the Master
record. But I believe that it's enough to execute LOAD RECORD directly
after SAVE RECORD (without moving the current record pointer) to get
the server to send the full record (which now includes the assigned
SN) back to the client, so this is straightforward to get working
properly. In fact, if you wrap the SAVE RECORD command, you can test
to see if you're running on 4D Client, and if you are, execute LOAD
RECORD to get the full record back to the client from the server. (The
saved record is automatically reloaded on 4D Mono). I think it's
necessary to do this because otherwise any code executed on the client
will use the client's copy of the record buffer, which doesn't have
the assigned SN yet, and doesn't get updated with the values that are
now on the server until the record is explicitly loaded back to the
client.
The situation is more complex where you're saving lots of records at
one go (e.g. ARRAY TO SELECTION) but v11 actually uses real record
numbers instead of fake ones in a transaction, so you could memorise
record numbers in an array as they are created and re-query the
database with CREATE SELECTION FROM ARRAY and SELECTION TO ARRAY to
get the SNs that have actually been given out if it's important to do
so straight away. If you're not on v11, you'll have to find another
way to do this.
HTH
David
J?rg Knebel (7/21/08 10:51 PM)
Bill,
On 21/07/2008, at 05:42 :51GMT+10:00, Bill Desjardin wrote:
Is your reliable experience with Sequence number a result of your
use of the semaphore? What experience has led you to the
conclusion that the semaphore is necessary. This is the first that
I have heard of a semaphore being used with the Sequence number
command?
I introduced semaphore to my SEQUENCE NUMBER wrapper not that long ago.
Have you had any experience that leads you to suspect that creation
of a subrecord via the Add Subrecord automatic action works any
differently than a CREATE RECORD in terms of the operation of the
Sequence number command?
Sorry, don't ask me about subrecords because I never used that stuff.
- flush buffers after saving
Is this superstition, or have you actually seen a difference.
It might be superstition but at least it works. ;-)
David Dancy made some interesting points worth to consider.
Cheers
Jörg
Bernd Fr?hlich (7/30/08 9:51 AM)
Bill Desjardin wrote:
Others have suggested that semaphores are required to protect
against one user process using the Sequence number command
after another user process has used the Sequence number
command to obtain a new sequence number but before the that
other user process executes the SAVE RECORD command.
I have tested this some time ago (I don´t remember if it was
2003 or 2004):
When you call Sequence Number in Process A, you get X.
When you call Sequence Number in Process B, you get X+1.
So even when no SAVE RECORD is called in process A you do not
get duplicate numbers even when using separate processes.
I do not use semaphores for that kind of stuff and so far
Sequence Number never failed me. (That is not to say that there
may not be circumstances when it fails, but this is not one of them).
Greetings from Germany,
Bernd Fröhlich
Bill Desjardin (7/30/08 11:46 AM)
To List,
I think that I have traced my Sequence number problem to the creation
of related many records in a subform object on an input form. The
record is created by an Add Subrecord auto action button. Then in
the form method for the subform object I have code like the following:
$Form_Event:=Form event
Case of
: ($Form_Event=On Load Record ) & ([Purchase_Order_Detail]
Purchase_Order_Detail_ID=0)
[Purchase_Order_Detail]Purchase_Order_Detail_ID:=Sequence number
([Purchase_Order_Detail])
SAVE RECORD([Purchase_Order_Detail])
End case
I am pretty sure that this is the offending code, because the only
tables with duplicate sequence numbers are ones with this type of
code. I adopted this methodology with the upgrade from 4D 2003 to 4D
2004, and that is about the time the problem started. Previously,
this code ran in the On load form event. By the time that this code
runs, 4D has created the record and apparently saved it, since Is new
record([Purchase_Order_Detail]) returns False during a Trace of this
code. Everywhere else, I have a CREATE RECORD preceding the Sequence
number call and it seems to work there. Apparently, here the Add
Subrecord does not act the same as a CREATE RECORD would. How are
others dealing with this situation?
Bill Desjardin
Reply to this message
Summary created 7/30/08 at 4:11PM by Intellex Corporation
Comments welcome at: feedback@intellexcorp.com