Re: Arcane bits

> Speaking of arcane bits, for all Informix 4.1 users out there, be very > careful about exceeding the LOCKS setting in tbconfig. All hell breaks > loose when you. > > Was working on a production system with about 40,000 rows, and three > indexes. Ran the little statement: > > update foo > set print_date = today > where print_date = 0 > and ctype = "N"; > > There were maybe 10,000 rows that this would affect. The system put one > lock on each row, plus 3 more locks for the indexes, for a total of about > 40,000. LOCKS was only set to 30,000. Aye, yi, yi! What a mess! When > finally had to drop back a day in backups to restore from. Utilities like > tbcheck didn't seem to affect the necessary repairs. > > Through hindsight, it's obvious I should have started a transaction, put an > exclusive lock on the table, and then did my update. But hey, this was a > production system and there was a process out there that could have been > adding rows while I worked. > > Moral: > Bump up your LOCKS setting to the largest practical number you can. > (see your local dealer for further information.) This sounds much more likely to have been due to filling the current logical logs than using all the locks. Don't get me wrong it could also be a problem with locks but in testing here we normally have our lock value set artificially low so as to trap any programs which are not locking correctly and we dont get this problem even if we do use all our locks. What we do get is starting a long transaction that fills all but the last logical logs and then is unable to rollback because there isn't enough remaining log space in the last logical log. This kills the database and makes it unrecoverable except from backup tapes. I beleive 4.1 has a couple of undocumented environment variables that can be used to alleviate but not entirely cure this problem. What you may have had was the situation of a rollback being started because you had used all your locks. The rollback may then have filled up the rest of the logical logs causing the total crash. Cheers, Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------