db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Table Intent locks not optimized/collapsed if table-level lock already held?
Date Wed, 07 Dec 2005 18:21:17 GMT
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


Mime
View raw message