How a Transaction is atomic?

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!

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.

StackExchange Database Administrators Q#227563, answer score: 4