I know that in terms of efficiency it is better to have one big query instead of many small ones, as it is better to reduce the amount of connections with the database to the minimum (especially in context of intense network traffic).
So my question is, besides isolating and atomizing a set of database manipulations (and doing a roll-back if one of them fails) does a transaction act like a big query? I mean, if you need to do many database manipulations, can you wrap it in a transaction and turn it into a single big operation?
No, if I understand what you're asking. You can think of a transaction as setting a checkpoint in the database history so if any of the individual operations within the transaction fails the database state can be restored to the checkpoint. That's a big simplification but conceptually that's how transactions work.
Within a transaction the individual queries are executed separately.
And it's not always better or more efficient to have "one big query instead of many small ones." That depends on what the queries are. It is more efficient to ask for a set of rows in one big query rather than ask for them one at a time. I've actually seen code that does this (pseudocode):
SELECT id FROM people WHERE ... ORDER BY lastname;
for each (id) {
SELECT firstname, lastname, phone from people WHERE id = {id};
...
}
That would be a lot more efficient as
SELECT id, firstname, lastname, phone FROM people WHERE ... ORDER BY lastname;