Is there a breaking point when a database transaction will get corrupted?

Question

I'm interested in how database transactions "commonly" are implemented in a database system, like for instance MySQL.

Assuming the actual writing of data to the physical database storage isn't an atomic operation (speaking in terms of clockcycles now), shouldn't I be able to corrupt a transaction by for instance ripping the power cable at some carefully chosen moment?

Answer 1

If the database system is carefully written, there should be no point in time where power outage can corrupt data, and when power outage occurs, no committed data should ever be lost.

The rdbms writes data first to a transaction log before actually updating the data. After a crash, it replays the log, copying any pending changes from the log into the database, and rolling back any transactions which have not been completed in the log. Commit is reported as successful only after the hard disk has reported a completed write operation to the log.

Answer 2

It is common that the DB writes to logs while updating. It can then tell it didn't manage to complete its operation as one transaction and use the logs to ROLL BACK the changes.

Answer 3

Databases follow the ACID properties. No matter when something explodes, there's a way to at the very least roll-back to a known correct state.