So, what happens if you make a horrible mistake when you are modifying the data within your database? Is the only course of action to resign? Fear not! Some database engines have a capability known as transaction rollback that will save not only your neck, but your data as well.
The principle of rollback is quite a simple one. For each modification to rows of data within the database, a copy of the row prior to modification is written into a log that records all the modifications made. Once you have decided that these changes are indeed correct, you can opt to commit the changes to the database. If the committed changes are actually wrong, then you’re in trouble: you can clear out your desk and dust off your resume.
However, if by some sheer luck you check the modified rows and see that they are wrong before you commit the changes, you can rollback the modifications you have made, returning the rows to the values they held before you started modifying them. Your job is safe.
Even better, the changes you made within the transaction were not visible to anyone else looking at the database at that time. So no one need know of your mistake, and your reputation is safe.
Most databases automatically commit data upon disconnection from the database unless a rollback is explicitly issued. Therefore, if the software that is being run is not performing suitable error checking on the modifications it is making, it may disconnect and inadvertently commit wrong data to the database. There is a moral in this—always check for errors!
Some database systems don’t have a feature as sophisticated as rollback or undo. In these cases, it is even more important that before unleashing dramatic data manipulation SQL on your database, you make a backup. Backups are always a good idea, even in databases that do support transactions.