Bryan Pendleton wrote:
> My question is basically - why does the nested-transaction need to fail
> immediately if it encounters a lock?
It may be because the code is concerned that it is the parent transaction
itself which holds the lock, and if we waited for the lock we'd have a
self deadlock which would never be resolved.
That is:
- parent transaction does something which causes it to hold a lock on
the system table.
- parent transaction initiates a nested transaction which needs to lock
the system table.
- nested transaction can't get the lock, blocks waiting for it.
- parent transaction never proceeds, because it's waiting for the
nested transaction.
thanks,
bryan
I have not had time to look at code or the test case, but can explain
the nowait issue. Bryan's comment is exactly why the code exists. The
problem is that update locks of a subtransaction
are not compatible with the parent transaction. So a conflict between a
parent and a subtransaction is basically an undetected deadlock. It is
good to do the system catalog update in a separate transaction as it
allows it to commit separate from the user transaction, but as you can
see can cause problems if locks conflict. I would see no problem
allowing the lock to wait if it is waiting on another user but do see
a problem if it is self waiting. When coded there was no way to tell
which of these 2 cases the wait was happening on.
What I don't remember
is if the main problem is the row lock on the system catalog or some
sort of problem with row locking vs table locking on the base table.
Do you have a stack trace from the failed lock in the sub transaction?
This part of the system could do with work. The current implementation
was from a long time ago, before derby where the main user of the system
was more likely a single user than 100 concurrent threads. For instance
the current default of one transaction committed system catalog update
per 100 inserts could be way better optimized for your application -
with 100 concurrent inserters it could be that every insert may be
competing to update the system catalog. The tradeoff in the current
system is that making the range larger can mean more "lost" numbers when
the system shuts down.
An interesting project might be to compare/contrast concurrent insert
performance on tables with autoincrement fields and not. With today's
multi-cores there are probably performance improvements to be had in the
derby autoincrement tables.
