Does anyone understand where/when we do SYSDEPEND system catalog
locking. I am trying to understand where we should be doing
SYSDEPEND operations (ie. user transaction or nested transactions),
to be able to answer yip's observations in DERBY-1621.
Derby supports 2 kinds of nested user transactions:
1) read nested transactions
o only read operations permitted, no insert/update/delete
o the locks come from the same space as the parent transaction.
o commit releases these locks separate from parent locking.
o original intended usage was for ddl compilation locks which
were gathered consistently, and then released prior to end
of user transaction. No problem conflicting with parent
locks.
2) update nested transactions
o read and write operations permitted.
o because it can be committed separate from parent transactions
the locks conflict with parent transaction to prevent 2
independent transactions from having the same row modified.
o original intended usage was for auto-increment system catalog
maintenance, where a range of values could be allocated but
need not be tied to user transaction. User transaction
in normal circumstance would never hold the row lock on the
row being updated in nested update transaction. One edge
case was if user by hand went out in serializable mode and
locked system catalog rows, in this case the nested user
transaction gets an error - this is a known issue.
Seems like we should either always be updating SYSDEPENDS rows in
nested xacts, or always in user transactions. Mixing is going to
lead to lock issues as yip describes in 1621.
