The terminals operate sequentially - i.e., there will not be multiple
active (financial) transactions. So I am fortunately certain that the
TXNSEQNO will only be used by this terminal for this transaction and that
this terminal will not attempt to initiate another transaction before this
one is completed.

It is, I suppose, both more complex and less complex than I describe. It is
more complex, in that the financial transaction is held as a class in the
application (in memory), not in the database, until the transaction is
complete, at which stage the entire transaction is written to DB. This is
how the app has been developed, and is a consequence of the original
developers using DBFs and all their limitations.

It is less complex in that a simple (and fast) fetch is done using terminal
ID to get the next txn sequence number - the db transaction in this case is
very small (which is how I have learnt to do db transactions)

However, I will, with extreme gratefulness, read the document you pointed
me to - it is indeed something that I must address, both for this and other
issues with the software - that sequence numbers must be unique and
sequential, sometimes within a range.

Thanks

Tim

>The problem of non-concurrency that exists for generators is replaced by
>one of concurrency if you use this strategy, viz., How will you know that
>another user didn't update TXNSEQNO? The differences between using a
>generator and going this way are
>a) that you can use dynamic SQL to reset the starting number. Horribly
>dangerous unless you have exclusive control of TXNSEQNO for the whole
>duration of the transaction.
>b) You cannot guarantee that the number you work on is unique, since
>another transaction may also be working with it on the same wrong
>assumption that it's the only transaction able to read it.
>
>You can set up a pessimistic lock on your number by "updating" it to its
>current value at the start of the transaction. This guarantees that, if
>you can get this lock, then no other transaction can. It also means that,
>if another transaction already has this lock, then you can't get it.
>
>If this pure sequence is really important, then there are things you can do
>with a generator, that involve using events and triggers. You'll find a
>paper on this technique at http://www.ibobjects.com/TechInfo.html (How to
>maintain a pure sequence).
>
>heLen

[Non-text portions of this message have been removed]