It prevents you from saving anything in the database if there's any errors (unless you start using savepoints). If there's a database error, you are prevented from running any more sql queries until the transaction is rolled back. It keeps transactions open for longer than you want, which could increase blocking or deadlocks. You have to have one open database connection per web request.
Comments
I'm curious, why do you think that wrapping every single web request in a single transaction a terrible idea?
It prevents you from saving anything in the database if there's any errors (unless you start using savepoints). If there's a database error, you are prevented from running any more sql queries until the transaction is rolled back. It keeps transactions open for longer than you want, which could increase blocking or deadlocks. You have to have one open database connection per web request.
Mainly is performance issue, see this SO http://stackoverflow.com/questions/1103363
Sounds like this is pretty specific to some ORM tools!