Subject Re: [firebird-support] Re: enumeration table question
Author Svein Erling Tysvaer
Stan wrote:
> The 200 inserts represent a "linked" hierarchy of data, so the 11th
> insert might needs the primary key of the record in the 10th insert.
> If the record in the 10th insert is already in the database, then the
> 11th insert needs the primary key
> of the existing record so it can "link" to it.

Well Stan, depending on your requirements it could still be a viable
option. Just add enum1_name_parent and enum1_description_parent to your
table. Then, when you need to put record 10 on hold you store its name
and description in these two fields of record 11 rather than its
parent_ID and in the parent_ID (if foreign key) you store -1 or some
other special number referring to a 'handle me later' flag. Then, create
a stored procedure that you run at regular intervals or simply after
you've done the rest of your changes (gone through all 200 records) that
retrieves this 'waiting records', find the parents ID, sets it and
possibly empties the enum1_name_parent and enum1_description_parent
fields (I guess that if you didn't empty them, you could later use that
information to find how often you encountered such duplicate row situations.

This solution could possibly solve your problem completely. What you are
currently doing, requires transactionB to wait for transactionA to
finish its five second transaction before continuing, and transactionC
that is waiting for transactionB has to wait 10 seconds until both
transactionA and transactionB finishes. With my suggestion they could
both simply move on and then you'd just have 2 updates to do at the end
rather than wait for 10 seconds. With 200+ simultaneous transactions,
you could end up with a lot of happy users all of a sudden.