When Else Should Use Explicit Transactions for Single Statements
Kendra Little over at SQL Workbooks recently answered the question “When should I use explicit transactions for single statements?” I’m not going to re-hash what Kendra wrote, go and read it on her site. What I want to do is add another case for when I use explicit transactions for a single statement.
Firstly, what is an explicit transaction? It’s when you write BEGIN TRAN and COMMIT or ROLLBACK around your statements. It’s usually used when you want a bunch of statements to all commit or all rollback together, e.g. you’re entering relational data across many tables.
When you don’t create a specific transaction, SQL will still wrap your single statement inside it’s own Transaction (called an Autocommit Transaction) so the whole statement will need to succeed or rollback.
So when do I think you should use explicit transactions (on top of what Kendra has already highlighted)? When you’re working with production data. In one of my first fulltime jobs I wrote a script that worked great in dev and test environments, and then I ran it on production…and it didn’t work as planned. Instead of updating a handful of records in a table with a few million records, it updated a few thousand…with no easy way of finding them again.
Since then the first thing I do when I connect to a production server in SSMS is write “BEGIN TRAN”. I know some DBA’s have gone even further and changed the template in SSMS to always have BEGIN TRAN at the top.
When I execute the query I can then make sure the number of rows that are changed match with what I expected, and then I can commit or rollback the transaction.
The only catch? Don’t walk away from your desk. By having an explicit BEGIN TRAN, SQL will make sure it holds on to all of your locks until you either commit or rollback. Don’t be the dba who locked up the whole database while you went to lunch.
Do you have any other thoughts on when to use Explicit Transactions?