RE: Do selects block truncates?

>
> >this is never a bug but is the consequence of enforcing statement-level
> read consistency
> >which is always guranteed by oracle.
>
> Can you explain in more detail?
> Which relationship does read consistency mechanism have with the blocking
> of truncate by select operation?
> I don't know no concept of "select" blocking any kind of operation in
> Oracle.
> (except some internal lightweight locks like latch or buffer lock, library
> cache lock/pin, blah blah blah)
>

statement-level read consistency guarantees that the rows returned by a select statement are exactly the row which are there when the select statement is started.

there is no problem to handle this with dml like delete or update because those statements creates undo log entries which are then used by the select if in another session changes are done (and commited) and cause an ORA-01555 if the undo log entries are not longer there

truncate on the other hand does not cause any undo log entries for the removed rows which is of course a good thing because of the very high performance of a truncate. thus if a truncate would run while a select statement is active this select statement couldn't get the data rows as they did exist when it was started and thus the statement-level read consistency would be violated.

the technical solution is to use some locking mechanism to get this behaviour truncate (as other ddl statements) requestis an exclusive lock on the table (partition) and also a (or more ?) ddl lock on the data dictionary before doing an action to remove the data rows
this lock request is blocked unless all select statements on the truncation object are finished (may be by an enqueue ?)

regards

kf

--
http://www.freelists.org/webpage/oracle-l