it does seem like there are minor problems there. Note that releasing the individual lock actually may be more cpu intensive then just letting it get released as part of the transaction - but there is probably a slight memory savings to be had.
I think isolation level 3 probably could be changed to release the lock, it probably wasn't due to paranoia of ever releasing a lock before end transaction in serializable. Bryan Pendleton wrote:
the logic is slightly different dependent on isolation level, what isolation level are you running. All the code gets the table level intent lock first, and if that succeeds then checks if it has covering locks such that it does not need to get row locks.I tried an experiment at lock level 3, too, and it seems to have similar not-quite-optimal behavior. In general, it seems like the "covering" logic is successful at noticing that a table-level covering lock removes the need for row-level locks, but the logic seems to take and hold unnecessary table-level intent locks at both isolation level 2 and isolation level 3. From what I can tell, the lockContainer() method in RowLocking3 doesn't even try to release the unnecessary intent-mode table-level lock, while the lockContainer() method in RowLocking2 does try to release the unnecessary table-level intent lock, but fails to do so. thanks, bryan C:\bryan\src\derby\lockTable>java -classpath c:\bryan\src\derby\trunk\c lasses org.apache.derby.tools.ij lockLevel3.ij ij version 10.2 ij> connect 'jdbc:derby:lockTest'; ij> autocommit off; ij> set isolation rr; 0 rows inserted/updated/deleted ij> lock table a in exclusive mode; 0 rows inserted/updated/deleted ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE------------------------------------------------------------------------------------------------------------------------------------------- X |A |GRANT 1 row selected ij> select * from a where a = 3; A ----------- 3 1 row selected ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE------------------------------------------------------------------------------------------------------------------------------------------- X |A |GRANT IS |A |GRANT 2 rows selected ij> update a set a = 3 where a = 3; 1 row inserted/updated/deleted ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE------------------------------------------------------------------------------------------------------------------------------------------- X |A |GRANT IS |A |GRANT IX |A |GRANT 3 rows selected ij> commit; ij> quit;
