Hi Tom,

Oracle puts a TM lock on a table even if no rows are affected by a DML statement:

TX_A>create table t (x int);

Table created.

-- just to show there are no locks on the table:

TX_A>alter table t move;

Table altered.

-- in another session (marked as TX_B) we now issue a statement which affects no rows:

TX_B>delete from t;

0 rows deleted.

-- now there's a TM lock:

TX_A>alter table t move;
alter table t move
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

TX_B>select type, lmode,
2 decode(lmode,2,'row-share',3,'row-exclusive'),
3 decode(type,'TM',(select object_name from dba_objects where object_id=id1)) name
4 from v$lock
5 where sid = (select sid from v$mystat where rownum=1);

TYPE LMODE DECODE(LMODE,2,'ROW-SHARE',3,'ROW-EXCLU NAME
------ ---------- --------------------------------------- --------------------
TM 3 row-exclusive T

TX_B>commit;

Commit complete.

TX_A>alter table t move;

Table altered.

It seems to me a bit counter-intuitive (which doesn't mean that it's "bad", of course) to retain an unnecessary lock (even a "weak" row-exclusive one) on a not-modified table ... so there's probably a reason that I can't see.

Do you happen to know a strong reason for this behaviour - or shall I classify it as "just a quirk" ?

--

For sure it's something to remember while coding - ie think about a code such as this:

delete from t where ...
if (sql%rowcount > 0) then
log deletions in logging table
commit;
end if;

This would leave the tm lock on the table preventing DDL possibly forever - the commit has to be moved after the if block, most definitely.

Thanks
Alberto