You are right about transaction semantics, but it doesn't matter typically if you're doing nested SELECTs to display data in a table, which is why people typically nest db_foreach.
This doesn't making nesting them a good idea, but it doesn't make nesting them particularly dangerous either. Just not the right way to write queries to display data.
All db_* API procs grab handles automatically, and there are many cases where it makes perfectly good sense to do so. Shipping OpenACS defaulted to one pool would break a large amount of perfectly well-behaved code.
