I ran the following experiment, with somewhat surprising results:
create table a (a integer); autocommit off; lock table a in exclusive mode; select * from syscs_diag.lock_table; insert into a values (1); select * from syscs_diag.lock_table; -- Note (1) below commit; select * from syscs_diag.lock_table; lock table a in exclusive mode; select * from syscs_diag.lock_table; update a set a=2 where a = 1; select * from syscs_diag.lock_table; -- Note (2) below commit; quit; At points (1) and (2) in the above script, I was surprised to see that Derby had taken out additional IX-mode locks on table A. It seems that Derby is smart enough to know that it doesn't need to take out ROW locks, since I have the table locked exclusively, but that same optimization doesn't seem to be performed at the table level, and the (apparently) unnecessary IX-mode table lock is redundantly acquired. Am I overlooking something? Is there a reason for the extra IX-mode lock to be taken? Or is this just an opportunity for an additional optimization? thanks, bryan
