Practical uses for Rollback

when do you usually use a rollback? how safe is it? in what situation do you usually recommend using a rollback, and when is it more advisable just using a backup?

rollback is used with transaction processing where you have a number of database updates that either all have to be applied or none applied.

The code will run each of ther database updates one after the other and at the end if they all worked successfully then you do a commit to process them. If at any point along the way you determine that the updates should not take place then instead of doing a commit you do a rollback to undo the part transaction.

You can’t restore from a backup in this instance because there might be thousands of transactions all being run at once and it could very well be the commit of one of the others that means that you need to rollback this transaction. The rollback only undoes the one incomplete (not committed) transaction and not all of the transactions currently running and certainly not any transactions already committed.

Note that you need to be using a database engine that supports transactions to use commit and rollback - they don’t work with myisam for example because that one doesn’t support transactions. InnoDB is one database engine you can use with mySQL to do transaction processing.