Re: truncate table

BigBoote66_at_hotmail.com schrieb:
> We faced a problem similar to this in our application (although in our
> case, it was dropping & rebuilding indexes on a live system with a
> decent amount of activity). We never found an adequate way of
> preventing queued transactions from interfering with our drop, but
> there are some work-arounds:
>
> 1). The "Just Keep Trying" approach. Do your truncate inside a pl/sql
> loop, trap the exception that indicates you failed to lock in exclusive
> mode, and keep trying (with some small wait time to prevent cpu burn)
> until you succeed. This worked for a while, but as the load on our
> system increased, it eventually became unusable.
>
> 2). Use a second resource (in our case, a dummy table) to serialize
> access to the table you want to truncate. For example, if the table
> you want to truncate is MyTable, create an empty table called
> MyTable_locker, and before you ever access MyTable in your regular
> code, acquire a Row Share lock on MyTable_Locker, like this:
>
> LOCK TABLE MyTable IN ROW SHARE MODE;
> [your statements affecting MyTable]
>
> Then, when you want to do your truncate, acquire an exclusive lock on
> MyTable_lock, then do the truncate:
>
> LOCK TABLE MyTable IN EXCLUSIVE MODE;
> Truncate table MyTable;
>
> This technique still isn't perfect. Even though there won't be any
> locks queued on MyTable when you invoke the truncate, the implicit
> commit by the truncate is not part of the truncate statement itself, so
> it's possible that the lock aquired by the other sessions could occur
> between the time you implicitly commit and when you actually start
> truncating.
>
> To get around this problem, we put a dbms_lock.sleep(.1) between the
> lock & the regular statements (don't put it between the lock & the
> truncate table, though):
>
> LOCK TABLE MyTable IN ROW SHARE MODE;
> dbms_lock.sleep(.1);
> [your statements affecting MyTable]
>
> This pretty much ensures that your truncate will kick in. (You still
> may want to include the "Keep Trying" logic in #1 above in order to
> account for the few rare cases where someone manages to sneak by).
> However, it also puts a .1 second delay into every statement that
> accesses MyTable. In our case, this wasn't prohibitive, but your
> mileage may vary.
>

Well , for such kind of maintenance from 9i onwards there is a beautiful feature introduced - ALTER SYSTEM QUIESCE RESTRICTED ( you have to have resource manager enabled for it, in my opinion , that *must* be done anyway in every bigger production environment ) may be it is not every situation appliable, but it is in some cases definitely very valuable.

Best regards

Maxim Received on Thu May 26 2005 - 10:49:00 CDT