Re: WHY index can corrupted???`

There are 2 cases: case 1 (the "normal" case) The physical log before image (not logical) must be on disk before the changed page is written to disk. case 2 (a performance optimization) For some operations (updates of data rows) the logical log also contains the before image information and there is no physical logging needed. In this case the logical log must be on disk before the changed page. (These pages have 0x2000 set in the flags field of the page header.) This guarantees that every page can be recovered to the state of the last committed tx recorded on disk. To achieve that in fast recovery - the physical log is restored - the logical log is rolled forward to it's end - all now open transactions are rolled back (I'm not talking about fuzzy checkpoints which complicate things further. But the above two cases still exist.) As you can see, this only works if the database server knows what is on disk and what is not. If you use write cache and want data integrity, you must make sure that the hardware is always able to write it's cache to the disk. An emergency power supply or some battery backup could help to ensure that. If you don't have that, you should run oncheck after a crash and should be prepared to restore your database if necessary. Michael Jonathan Leffler wrote: > Michael Mueller wrote: > >> I agree we flush the phy log buf. But do we really flush the logical >> log buf (except when we do no physical logging in some specially >> optimized cases)? > > > There's a thing called log write-ahead protocol that, AFAIK, is required > to ensure recoverability. That means that the logical log information > must be on disk before the other changes are committed. > That applies absolutely at checkpoints (unless fuzzy checkpoint did > something I'm not aware of - a possibility), and maybe even before pages > get written out normally. At the least, that was the version 5 theory. > Anybody aware of when the rules changed. (I suspect that a few details > above are a bit slipshod - corrections or amendments welcome.) > >> Madison Pruet wrote: >> >>> Do you have a repro/case involved where this occurred? We are >>> supposed to be flushing the physical log buffer and the logical >>> log buffers to disk prior to writing the data page/index page to >>> disk. If there is a case where we aren't, then we should consider >>> it a bug that needs to be resolved. >>> >>> "Art S. Kagel" <kagel@bloomberg.net> wrote: >>> >>>> On Thu, 28 Aug 2003 09:51:41 -0400, Michael Mueller wrote: >>> > [...major snippage because the indentation is shot to pieces...] > > -- === Michael Mueller ================== Tel. + 49 8171 63600 Fax. + 49 8171 63615 Web: http://www.mm.kay-mueller.de http://www.planets.kay-mueller.de ======================================