I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query. Therefore a transaction then becomes nothing but a set of SQL queries executed at the same time. But the fact that each query multiple operations, I am really not getting as to how the previously executed queries(in the same set) roll back if an error occur in a later query. What am I missing here? Thank you!
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 (
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.
A transaction is atomic when it's encapsulated like this:
BEGIN TRAN UPDATE dbo.Users SET Reputation = Reputation + 1 WHERE Id = 26837; UPDATE dbo.Posts SET Score = Score + 1 WHERE Id = 227563; COMMIT
In that case, both of the updates on the two separate tables will commit together, or they will roll back (fail) together. Atomicity means this is the smallest unit of a transaction that will commit.
However, if later, you try to run a separate query outside of that scope, that would be a separate transaction.
If you try something like this:
BEGIN TRAN UPDATE dbo.Users SET Reputation = Reputation + 1 WHERE Id = 26837;
And then in your application, you do other kinds of work, like C# code, and then come back to the database later and do this:
UPDATE dbo.Posts SET Score = Score + 1 WHERE Id = 227563; COMMIT
Then both the earlier Users update and your subsequent Posts update will both succeed, or fail, together.
However, in real life, you can't hold a transaction open like that for an extended period of time because you'll be blocking other people from doing work in the database. That's why you'll often see advice like, "Keep your transaction short and sweet," meaning, get in, get your work done, and get back out. Don't explicitly hold transactions open.