As a developer, you’re probably aware there are lots of good reasons to use transactions.  Rollbacks, atomicity, and isolation are Good Things™ when you want your system to behave in a predictable and reliable manner.

So I’m surprised when I see a developer who uses transactions throughout his code neglect to use a transaction when updating a production database from a SQL client.

Look at this guy working without a net:

Password for user dbadmin:
psql (8.4.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  1 | Shirley Jones  | 12000.47
  2 | David Cassidy  | 33719.15
  4 | Susan Dey      | 55100.23
  3 | Danny Bonaduce |   100.23
(4 rows)

proddb=> delete from accounts where balance > 1000.00;
DELETE 3
proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  3 | Danny Bonaduce |   100.23
(4 rows)

Shut off the smoke alarm!

Oh crap!  I meant to delete all accounts with balance less than $1000, not accounts with balance greater than $1000! Guess I gotta call the DBA and restore from backup.

Maybe it shouldn’t be surprising that developers don’t think to use transactions when they’re updating the database from a SQL client.   Many otherwise great tutorials don’t even mention transactions when they’re teaching you how to do inserts, updates, and deletes.  Some even warn you to be really careful when updating the DB.

With transactions, you don’t have to be really careful. Use “BEGIN” to start a transaction, “ROLLBACK” to undo all the statements you executed after you started the transaction, and “COMMIT” when you’re happy with the statements.

Here’s the same bad edit, this time using a transaction:

proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  1 | Shirley Jones  | 12000.47
  2 | David Cassidy  | 33719.15
  4 | Susan Dey      | 55100.23
  3 | Danny Bonaduce |   100.23
(4 rows)

proddb=> begin;
BEGIN
proddb=> delete from accounts where balance > 1000.00;
DELETE 3
proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  3 | Danny Bonaduce |   100.23
(4 rows)
proddb=> -- OOOPS!
proddb=> rollback;
ROLLBACK
proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  1 | Shirley Jones  | 12000.47
  2 | David Cassidy  | 33719.15
  4 | Susan Dey      | 55100.23
  3 | Danny Bonaduce |   100.23
(4 rows)

Nice!  Transactions and rollbacks gave me an ‘Undo” button. Let me try that again.

proddb=> select * from accounts;
 id |     owner      | balance
----+----------------+----------
  1 | Shirley Jones  | 12000.47
  2 | David Cassidy  | 33719.15
  4 | Susan Dey      | 55100.23
  3 | Danny Bonaduce |   100.23
(4 rows)

proddb=> begin;
BEGIN
proddb=>  delete from accounts where balance  < 1000.00;
DELETE 1
proddb=> select * from accounts;
 id |     owner     | balance
----+---------------+----------
  1 | Shirley Jones | 12000.47
  2 | David Cassidy | 33719.15
  4 | Susan Dey     | 55100.23
(3 rows)

proddb=> -- looks good!
proddb=> commit;
COMMIT

mmm

When you’re updating production databases with your favorite SQL client, remember to use transactions to save your bacon.