When not to use transactions and where you can't use transactions
Transactions are great when everyone plays in. You group a set of activities together under a transaction and you start executing them. If anything bad happens along the way or something doesn't go as planned, invoke rollback and all is taking care of automatically by the infrastructure: everything is undone and the app returns to the state before the transaction started; recovery and consistency is a piece of cake with nothing to worry about.
I can't really use transactions for long running sequences of activities. I mean, technically is possible, but the app will not scale anymore. Imagine starting the transaction today, updating databases and doing transactional work for a few days; add to that a few human interventions (when user input is required) and make it a few more days longer and only after that commit the transaction. What just happened? For those number of days, the transactional work kept all the resources that it touched in a locked state waiting for the commit (or rollback) decision. All these resources could not be used by anybody else (with various degrees of freedom depending on your isolation level) for that many days. Transactions are really meant for short lived work.
Another aspect, partial rollback - the ability to rollback some work in the case of an event, but let the rest of the work that executed so far to continue towards a possible commit. This is not possible today with how transactions are implemented: have at least one little piece of work or resource declare itself in error and the whole transaction will rollback, no exceptions allowed.
And then, maybe the most major aspect, not everything participates in a transaction. It can be a resource, it can be an action that can't rollback. Implementing resources that are transactional is not trivial, meeting all the ACID properties is hard. Or in some cases, it just doesn't make sense to make something transactional, like sending an email. What to rollback there?. Chase the internet to catch the email before it reaches ones inbox, and if it got to the inbox, tell the reader to forget it?
In all these cases, something else needs to be used in combination with short lived transactions. That means that you need to provide code that un-does/cancels/compensates the work that was already done. You need to write most of the "undo" code since the rules need to be more relaxed than with transactions. The compensation logic varies depending on the application logic at that given time. In the transactions world, the ACID requirements govern everything and there are always only two states: an initial state before transaction and a state after the transaction commits; rollback will always move the app back to the initial state. With compensation, the number of consistency states depends on the app and are defined by the app.
Support for compensation exists today in Biztalk Server and Windows Workflow.