Re: truncate table

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. Received on Thu May 26 2005 - 09:11:39 CDT