Re: Bring Online down in middle of transaction rollback?

Cosmo Lee wrote: > > What is the effect of bringing Online down in the middle of a > transaction being rolled back? Is it meant to recover gracefully from > this kind of thing? If so, in reality, does it actually do so (recover > gracefully, that is)? > > I've got a client who pulled a bone-headed move of running two huge > deletes at the same time. After eight hours, the 800,000 row locks have > been exhausted, and all users are locked out of the database. So, we're > going to have to wait another eight hours, at least, for the > transactions to roll back. > > I'm wondering if there are any ways to speed this process. One thought > that came to mind was to bring Online down, add more locks, then bring > it back up. My concern is whether Online is meant to recover gracefully > from being shut down in a middle of a transaction rolling back. 800,000 > rows is a lot to modify, so I'm a bit queasy about interrupting that > kind of transaction. Anybody w/ experience like this? > > Is there some other way that I could speed this process to get users > access to the DB more quickly? > > As far as I can tell, there is not way to add more locks while Online is > running. Please tell me if I'm incorrect. If all else fails shutting down you should be OK. The startup recovery process will rollback the transaction correctly but it may not be much faster than waiting. The only risk is the logical and physical log buffers which are flushed to disk on a normal shutdown so you are safe. Art S. Kagel