Overview
Best practice since the dawn of time has been to allow the reversal of actions performed in the UI. In web applications, a full "undo stack" is too difficult (and rarely needed). So we use a simple "delayed trigger" for important actions such as delete.
Use of "Are you sure?" dialogues makes it more, not less, likely that users will do things by accident.
When implementing an "undo" (delayed trigger), it is vitally important to show the result of the action immediately. Not doing so makes it a confirmation dialogue in disguise and all benefit is lost.
When should you use an "undo" (delayed trigger)?
For anything that you can't reasonably recover from if you get it wrong. Delete, submit, etc.
Really? Don't we want to warn them first before they do something stupid?
Here's the rationale in a nutshell, based on large amounts of evidence (small example here if you're interested, but Google "habituation HCI"):
The vast majority of people intend to do what they do. We therefore want to facilitate what they want as smoothly as possible by not putting barriers in their way.
People are creatures of habit. If they see regular dialogues come up with "OK" in them, they will soon be hitting OK for every dialogue without thinking. Like merry leprechauns. One day, they will therefore hit an "OK" by mistake. And by then it's too late. Better therefore to provide those people who make that rare mistake with a way to reverse it.
(It follows from the above that if you can avoid habituating people to ignore dialogues in this way, then if you ever need them to read one you will meet with more success.)
How about we have a dialogue that has "cancel" as the default?
That's a stupid idea that violates points 1.
Also note Undo is part of a family of interaction design techniques that are guided by a version of the "80/20 principle": don't annoy the majority of people for the sake of the minority.