| Subject | RE: [firebird-support] Re: Slow concurrent insert performance. |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-12-16T04:36:26Z |
Sorry I can't find the original posting for this thread...
me as though you have these transactions in WAIT. If so, the other
transactions have to wait for the oldest one that has inserts pending to
either commit or rollback before their INSERT requests can even be
considered. And then, one by one, the others will subsequently have to
wait for the next oldest...and so on.
This is "as designed". Firebird doesn't support Dirty Read, so no
transaction knows what uncommitted work is pending by other
transactions. Inserts are strictly serial: the engine accepts INSERT
requests (or rejects them) based on its knowledge of the committed state of
the database. Data integrity would fly out the window if multiple users
were all allowed to post inserts willy-nilly, regardless of whether any of
these concurrent inserts would conflict with other pending inserts. So -
in WAIT, the first transaction posts, the others wait for commit or
rollback. In NO WAIT, a conflict exception will be thrown immediately.
All this stuff about "old record versions" is nonsense. There are NO old
recversions for inserts. What there is, is the Undo log (a memory
structure) The engine doesn't use the undo log for consistency checks,
since everything in there is uncommitted. Consistency is protected by
simply not allowing concurrent inserts. Period.
I guess if you have a *need* to have inserts happening in close to
concurrent time, you should implement some kind of "hurry-up" strategy to
minimise delays caused by slow humans, e.g. put the transaction in Read
Committed isolation (author ducks to avoid a wolf attack!!) and use some
form of Autocommit, so that inserts are not kept pending too long. (Don't
forget to implement some *other* strategy to hard commit the transactions
eventually - or regularly, in conditions where you are allowing users to
perform both inserts and updates in the same transaction...
./heLen
> >Assuming that all transactions are inserting to the same table, it looks to
> > > I am concerned about the performance of concurrent inserts. It
> > appears that for 2 concurrent inserts there is a factor of 2.8 times
> > longer in elapsed time, a factor of 4.3 times longer in elapsed time
> > for 3 concurrent inserts and a factor of 5.8 times longer in elapsed
> > time for 4 concurrent inserts. Can anyone explain why the impact is so
> > significant? What may be causing this impact?
me as though you have these transactions in WAIT. If so, the other
transactions have to wait for the oldest one that has inserts pending to
either commit or rollback before their INSERT requests can even be
considered. And then, one by one, the others will subsequently have to
wait for the next oldest...and so on.
This is "as designed". Firebird doesn't support Dirty Read, so no
transaction knows what uncommitted work is pending by other
transactions. Inserts are strictly serial: the engine accepts INSERT
requests (or rejects them) based on its knowledge of the committed state of
the database. Data integrity would fly out the window if multiple users
were all allowed to post inserts willy-nilly, regardless of whether any of
these concurrent inserts would conflict with other pending inserts. So -
in WAIT, the first transaction posts, the others wait for commit or
rollback. In NO WAIT, a conflict exception will be thrown immediately.
All this stuff about "old record versions" is nonsense. There are NO old
recversions for inserts. What there is, is the Undo log (a memory
structure) The engine doesn't use the undo log for consistency checks,
since everything in there is uncommitted. Consistency is protected by
simply not allowing concurrent inserts. Period.
I guess if you have a *need* to have inserts happening in close to
concurrent time, you should implement some kind of "hurry-up" strategy to
minimise delays caused by slow humans, e.g. put the transaction in Read
Committed isolation (author ducks to avoid a wolf attack!!) and use some
form of Autocommit, so that inserts are not kept pending too long. (Don't
forget to implement some *other* strategy to hard commit the transactions
eventually - or regularly, in conditions where you are allowing users to
perform both inserts and updates in the same transaction...
./heLen