5. Have a separate prefix table with your prefix defined as UNIQUE and some
dummy number. Update this table like
UPDATE <table>
SET dummyNumber = dummyNumber
WHERE prefix = :prefix
(or INSERT INTO <table> if the prefix hasn't been used before)
The update should produce an error if some other person was updating that
very prefix in the prefix table at the same time (and your user would have
to wait). Then you can safely do your SELECT MAX ... and assign your
sequence number before committing (use the same transaction for the prefix
query as for SELECT MAX... and your original query)

Geoff is a much more skilled IBOer than me and there may well be reasons
for him not mentioning this possibility. If so, I trust he will correct me.

Set

At 13:35 15.03.2001 +1100, Geoff wrote:

>There are a number of possibilities...
>
>1. Cached updates - can be used to hold onto all the changes, even
>master/detail until you are ready to commit all of them. I am not a
>fan of Cached Updates, but this is one area where they can be useful.
>
>2. Setup the form(s) so that the user must create the entry (and
>commit it) before providing any details - forcing the just the
>sequence/identifying record itself to be created. This action can
>therefore be very quick and minimise update conflicts. I generally
>think of this as a "wizard" style setup, where you prompt the user
>through a series of short steps so you can maintain tighter control
>over what is happening.
>
>The difficulty is in trying to work out what to do if the user wants
>to cancel the creation (or if the application/PC fails in the middle).
>This can often be managed with "flag" fields that are only cleared
>when successfully completed. A separate process can be used to check
>for incomplete items and take the appropriate action.
>
>
>3. Have the user create the record details first and then assign a
>number to the record later - possibly even in a batch process. This
>method requires some administrative/process reporting to be put in
>place to ensure no records are ever left un-sequenced.
>
>
>4. Maintain a separate table that you insert sequence numbers into.
>When a new record is created, you generate the sequence field value in
>the insert trigger (but use a separate/surrogate key for IBO purposes)
>and try to insert the new sequence value into the sequence number
>table. You catch any exceptions with a "WHEN" statement and retry
>with a new sequence number. This processing could be embedded in a
>stored procedure executed from the trigger to allow some level of
>recursion.
>
>This setup means that your separate sequence number table is entirely
>redundant, it is there only to allow you to catch and handle problems
>with duplicate sequence numbers inside the insert trigger of your main
>table.
>
>The arrangement does mean that it would be possible to fill holes in
>the sequence numbers caused by cancelled transactions - if that were
>required/appropriate. Such processing would only be practical if the
>likely number of sequences is fairly small.
>
>
>There are probably other scenarios, but these are just a few that come
>to mind at the moment.
>
>Geoff Worboys
>Telesis Computing