Working with Salesforce you start growing to expect the platforms "magic" to handle things like rolling back failed transactions, especially if you've been working with triggers where an unhandled exceptional really will undo the whole transaction. But most people seem to forget that outside of triggers you don't get this kind of help.

This comes into play especially in visualforce controllers that work with multiple types of objects and use separate DML statements on them, your typical wizard page like so:

insert myObj;
insert myOtherObj;

This creates a bug that can be mind-bendingly hard to troubleshoot: a case where myObj was added to the database and the insert of myOtherObj failed with an unhandled exception. In other words, a potentially inconsistent database.  [Nevermind, I was wrong about unhandled exceptions not rolling back!]
Worse yet is the user experience when this happens, the user is presented with an error that the insert failed, but that message won't tell them in plain English what record failed and why (although that info will be embedded in the exception object shown). They also have no way of knowing if myObj was created or not.

First and foremost we need to be able to undo all related operations caused by apex triggers, workflow, and un-inserting our newly created object. There's only one way to do all three at once: Database.rollback. Firstly you'll need to set a savepoint before you do any DML with Savepoint dbSave = Database.setSavepoint(). After that's saved to a variable you can do all your DML, and if you need to rollback it's as simple as Database.rollback(dbSave);. Ta-da, all DML, even that you didn't explicitly ask for (like triggers doing more DML), is undone in one fell swoop.

Beyond that, if you use Database.rollback you'll at some point end with an interesting situation: myObj was inserted and then rolled back, as expected, but the problem comes when you retry the operation later and myObj already has an id assigned, but it's not in the database! You'd think this would just be a single line of apex: " = null;: Nope, the id field is never directly editable, and you'll get the message "Field is not writeable".

Luckilly there's a well hidden solution to this: the sObject's clone method. Interestingly enough if you pass in two parameters only, both false, it will do nothing other than null an sObject's id field, like so:

myObj = myObj.clone(false,false);

One last error handling trick: the ApexPages.addMessages method takes an exception object as an argument and converts the error into plain English and will display it inside any <apex:pageMessages /> elements in your page.

Putting it all together we end up with:

Savepoint dbSave = Database.setSavepoint();  
    insert myObj;
    insert myOtherObj;
}catch(DMLException e){ //undo all of the failed operation.
    myObj = myObj.clone(false,false);