RE: Do selects block truncates?
- From: Kurt Franke <Kurt-Franke@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, "Ukja.dion" <ukja.dion@xxxxxxxxx>
- Date: Fri, 21 Dec 2007 18:14:31 +0100
>
> >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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
