@BBigford said in SQL understanding - power outage:
@scottalanmiller said in SQL understanding - power outage:
@BBigford said in SQL understanding - power outage:
Here's what the person I heard it from said:
"likely easy to talk on the phone (i'm on a conference call, but have time this afternoon).
The term is quiescence, that you are looking for. This means that writes have been halted, and all data in buffer has been flushed. Part of the challenge here is this needs to be done not only at the app layer, the OS layer, the storage layer underneath that (and even at the drive level, as cheap consumer SSDs and SATA drives will have write buffers not protected).
Modern SQL commits writes first to the transaction log then to the database, and in the event of a power loss can "replay" the transaction log. The problem is if the log is large the can take a REALLY long time, and if you have systems underneath that ACK'd writes at the SCSI layer out of order (Consumer level SSDs) this might not properly recover."
Not really sure what data is sitting in the buffer during a power outage though. Meta data waiting to be written to tran logs? So then when a power outage happens and that buffer gets flushed, there's an inconsistency in those tran logs that were mid-write...
That's where atomic commits come in. They make sure that everything is quiesced before anything goes to the database. ZFS does the same thing for different tasks. This really isn't an issue in a properly designed database - unless you've added a caching layer yourself that doesn't honour the flush commands from higher up the stack. Then it is on your own head. You could always put your database into RAM and cause it to fail like that if you wanted.
I found out what he was talking about. Commit to tran log > Application acknowledge transaction > written to DB. The data that gets flushed shouldn't matter because most applications wouldn't acknowledge something that isn't commit. So if there was a power outage then the tran logs could just replay, app acknowledges, and so on.
I was missing something, and only thinking of tran logs and the database. Didn't think he had been talking about the front end.
Ah, okay. Yeah, if the application doesn't wait for the database, it's just being reckless.