Re: System Deadlock

In <1rgvudINNgo9@emory.mathcs.emory.edu> alex@lucas.MREC.AR (Maria Alejandra Perez Weiss) writes: >dear gurus, >I'll try to explain the situation I've just run into. >I had a big table(50000 rows) in a database with logs. >I run a delete(from isql qery language) on that table >which envolved a high percent of the rows (I know it wasn't a good idea) >Something like 30 minutes later I returned to the screen where >the isql was running. As it seemed to have no end, I tried to interrup it. >It wasn't so easy, so I tried with tmode -z. Nothing happened. >So I run tbstat and I found the following situation: >address number flags uniqid begin size used %used >80427c8c 1 U---C-L 757 100332 250 250 100.00 >80427ca8 2 U-B---- 754 10042c 250 250 100.00 >80427cc4 3 U-B---- 755 100526 250 250 100.00 >80427ce0 4 U-B---- 756 100620 250 250 100.00 >All the logs marked as being backed-up !! >I think the most important thing I have to mention is that the >logs backup device was set to ... /dev/null !!!! Quite right, they were backed up to /dev/null as soon as they filled. They are however, still in use. >Users >address flags pid user tty wait tout locks nreads nwrites >804019b4 ------D 982 informix console 0 0 0 8 2 >80401a14 ------D 0 informix console 0 0 0 0 0 >80401a74 ------F 983 informix 0 0 0 0 0 >80401ad4 ------F 984 informix 0 0 0 0 0 >80401b34 --R-X-- 0 coinvdba 0 0 0 45 10 >80401b94 --A---M 989 informix console 0 0 0 0 0 > 6 active, 30 total >The user was in rollback mode, and the online status log file had a warning >about a long transaction being rolled back. >I put the system off-line, and when trying to get it to quiescent mode >it entered recovery mode. This mode didn't EVER finish. The machine was >shutdown, and when the OnLine was up again (automatically by init) it >was again in recovery mode. NOTHING could be done, because everything >I tried (archive, add logs, can't remember what else) should be done >in quiescent mode, and there was no way to get it. >Thanks god it is a machine used only for development of applications. So, >after 2 days, we decided to re-initialize online and get everything from an >archive. >We have no problem now, everything is fine; but what would happened >if it was a real system ? >Was there any solution we couldn't see at all? This is the classic situation of the logical logs filling up before the Long Transaction Rollback could complete. The correct (and I believe only) recovery procedure is documented on page 2-143 of the Administrator's Guide (5.0): "If this occurs, you must perform a data restore. During the data restore, you *must* *not* roll forward the last logical log file. Doing so re-creates the problem by filling the logical log again." The latter statement also explains why Fast Recovery never completes. To reduce the probabibility of this occurence, reduce the LTXEHWM parameter (5.0) or increase the granularity of your Logical Log files (4.x). graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.