Testing SQL with transactions

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Have you ever accidentally deleted the entire contents of a table and had to go explain to the DBA what you did and why they now need to spend their afternoon restoring everything from backups? If you say no then you obviously have never written SQL, because we've all done something similar at some point in our careers.

The thing is though there are simple ways to prevent this. For example restricting permissions for a user(type), to not giving direct access to production databases. More than that though what amazes me is that there is a safe simple way to test your SQL scripts which many new developers seem oblivious to.

I am of course talking about SQL transactions. By wrapping some potentially dangerous sql in a transaction block with a rollback we can test what the behavior of our scripts will be without actually committing those changes.

SQL Server

Here is the SQL server transaction syntax:

DELETE FROM dbo.users


And here is the MySQL syntax:

DELETE from users;

Now obviously there are other ways your sql could cause issues such as tying up resources etc, but at least with rolling back transactions you should not lose existing data. Please if you are a new developer tasked with writing sql OR a manager with a new junior developer make sure to go over transactions.

Further Uses

This is just scratching the surface of how useful and important transactions are and if you have never used them before then I urge you to go and learn about them as they are a truly powerful tool.

p.s.Other databases (e.g. PostGres) also have transactions I'm just not familiar what their syntax looks like.

Reader Comments

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing