On Wed, 27 Jan 2016 08:51:16 +0000 Bernard McNeill <bm.email01 at gmail.com> wrote:
> Using the standard defaults (which avoid WAL), is there any > possibility whatsoever of that last SQL transaction being lost? I have an unusual answer: Yes, and it doesn't matter. Let's suppose, as you did, that the application got back "Commit Successful" followed quickly by a power failure. You want to know, could the transaction be lost anyway? But I ask you, what action could the application possibly take, in that subsecond interval, that it matters? The failure could have happened an instant earlier, just before "Commit Successful", and it would have to recover from that. I can think of no scenario in which the committed-but-lost transaction is very different from the uncommitted-and-lost transaction. There is no God's-eye view of application state. The important service provided by the DBMS is not "what's committed is definitely saved", but rather that "what's committed is definitely *consistent*". If on recovery the DBMS finds that, contrary to its prior report, the state of the database does not warrant including the final transaction, it rolls it back, and presents the data in an internally consistent state. The application doesn't have to cope with the ledger being out of balance and suchlike. --jkl
