Re: Why can't I truncate table?

The problem with truncate: It's a DDL statement. When executed, it commits all previous transactions of the session, i.e. it releases lock set in lock table statement. Then it tries to lock table in shared mode to truncate table, but other session already has lock on this table to insert record. Delete statement is a DML statement, so it is executed within a transaction. Actually, truncate shouldn't behave this way either, it looks like a bug.

In article <7cgpan$jpj$1_at_news.vassar.edu>,   Bill Wine <biwine_at_vassar.edu> wrote:
> I want to lock a table to prevent changes, do some read-only
> processing on rows in the table, then truncate the table,
> and commit, e.g.
>
> 1. lock table xyz in exclusive mode;
> 2. select ... from xyz;
> 3. truncate table xyz;
> 4. commit;
>
> I find (Oracle 7.3.3.4) that if another user attempts to insert
> into xyz after step 1, but before step 3, the truncate fails with:
>
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> The other user's insert then succeeds.
>
> If I 'delete from xyz' instead of 'truncate xyz' it works as expected.
>
> Why can't the process that locks the table in exclusive mode then
> do a truncate when another process is waiting to insert?
>
> Thanks for your help.
>
> Bill
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Mar 14 1999 - 18:42:39 CST