--- In firebird-support@yahoogroups.com, "Stan" <shizius@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> >
> > --- In firebird-support@yahoogroups.com, "Stan" <shizius@> wrote:
> > >
> > > Great information, thank you.
> > >
> > > I have hundreds of inserts just like this one to make
> > > from each of my clients. I do all the inserts from one
> > > client in 1 transaction.
> > >
> > > The reason that I use "WRITE|COMMITTED|REC_NO_VERSION|NOWAIT"
> > > transaction is that if there is a conflict lets say on the 10th (out
> > > of 200)insert I want to revert the transaction at this point and try
> > > again, instead of wasting time by processing the rest of the 190
> > > inserts and then getting a "conflict" when I commit.
> > >
> > > It does not make sense to create separate transactions for
> > > each insert, I tried this, and the performance is terrible.
> > >
> > > am I missing something?
> >
> > Stan,
> >
> > You should not be getting any conflict when you commit. You will get
> > an exception on the 10th insert. With Ann's suggested settings
> > (WAIT), you will then freeze until the transaction that is causing
> > the conflict commits or rolls back. If it commits, then you will
> > receive the lock conflict, but if it rolls back, you will succeed.
> >
> > Because this logic requires it to wait until commit, the subsequent
> > attempt should always succeed, whereas using a NOWAIT - retry logic,
> > the first transaction may still be running. Worst case means that you
> > are fruitlessly giving the database select/insert cycles, and it is
> > so busy doing this it can't get around to actually finishing the
> > first transaction causing the problem.
> >
> > Something I am not understanding, do the 200 inserts have to be an
> > atomic operation due to business rules? If you can independently run
> > each insert in its own transaction, you should get a better
> > throughput. What I mean by this is that if 10 fails, can't you put 10
> > in a queue to look at later and try 11. Once you get to 200, 10
> > should be free to process. Alternatively you could spawn a new thread
> > and in a separate connection use a WAIT transaction in that thread.
>
> 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.
>
> So it does not work to skip inserts that fail and do them later.
>
>
> >
> > The problem you seem to encounter is that you have large overlaps in
> > your sets of data, and because your transactions are running for so
> > long, there is an extremely high probability of a conflict occurring.
> > Worse still, if your problem occurs at 190 and you then have to
> > rollback, you had effectively stopped 200 clients from inserting 190
> > specific values for the duration of that transaction.
>
> Correct. That is exactly whats happening. It is even worse
> than you think. One client uploading a big set of data
> usually takes 2-5 SECONDS to complete its set of inserts.
>
> I know that the root of my problem is the amount of duplicate
> data that I am throwing at the Database, but I am looking for
> server side improvements; they are easier.
>
> >
> > If you are able to do 1 operation per transaction, then even though
> > the overhead of starting and committing transactions is higher, the
> > length of the transaction is shorter and the proportion of records
> > which are effectively locked to other transactions is much lower.
> >
> > Adam
>
>
> I will try using a separate transaction for each insert.
>
> Do you still advocate the use of CONCURRENCY|WAIT
> instead of COMMITTED|REC_NO_VERSION|NOWAIT if I use 1 transaction per
> insert?
>
> I use a back-off algorithm, the first try I sleep for 1 second
> before retrying, second try sleeps for 2 seconds, etc.
>

Hmmm, sleep (too much getting up early to watch world cup for me).
Disclaimer: Take any advice from me with a grain of tiredness.

If your actions are hierarchical and there is no way of preventing
that, using the method Ann showed you will mean that
* You will not wait a second longer than you have to
* You are not meaninglessly retrying.

I have never encountered a hierarchy based scenario where a node can
have multiple parents, so it is hard for me to see how you can
optimise. If the node only had a single parent, then the second
transaction would get the lock conflict on attempting to insert the
first node, not the 10th or 190th.

An idea that may or may not have merit:

If the entire hierarchy is duplicated on more than one client, then
you could use a hash (eg MD5) at the client end to see if you are
inserting a duplicate with a single insert.

Adam