How a Transaction is atomic?
TL;DR; The transaction log contains all information necessary to re-create or undo each transaction, depending on whether it contains a commit record for that transaction or not.



In SQL databases transaction atomicity is implemented most frequently using write-ahead logging (meaning that the transaction log entries are written before the actual tables and indexes are updated).

Queries in the strict sense of the word, that is, SELECT statements and other read operations that do not change the database state, are not logged as there is nothing to commit or roll back, and as such the concept of atomicity does not really apply here, at least at the DBMS level. The application consuming query results might need to enforce atomicity by discarding results of previous queries if one of the subsequent queries fail and cause the rollback.

When DML statements (INSERT, UPDATE, DELETE, MERGE) and other statements that change the database state get executed, the changes they make are first written into the write-ahead log (WAL). If all statements succeeds and a commit (explicit or implicit) is issued, this fact is also recorded in the WAL, and the log is persisted. This makes the changes durable. They may be written out to the actual table and index segments at some later time, during a checkpoint or log replay.

However, if one of the statements fails, there would be no commit record in the log for this transaction, and the DBMS will undo table and index changes made so far using the preceding log records of that transaction.

Similarly, if the DBMS process crashes, or the power goes out, there won't be a commit record for the in-flight transaction either, and the table or index changes that may have already been persisted will be undone during log replay.