SQL Tip: How to Avoid Fundamental Mistakes by Using Transactions

When you plan to apply a big change to a database table, you should always triple check your queries, so nothing will get screwed.

I always use the following approach, in order to preview data before and after a change that’s going to be made over records without having this change committed, so I can refine my queries if I did something wrong:

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13

START TRANSACTION;

-- select the data before change
SELECT * FROM table_name WHERE some_id=5;

-- change the data
UPDATE table_name SET quantity=quantity+1 WHERE some_id=5;

-- select the data after change, and see if everything went alright
SELECT * FROM table_name WHERE some_id=5;

-- undo the change
ROLLBACK;

SQL Server

1
2
3
4
5
6
7
8
9
10
11
12
13

BEGIN TRAN;

-- select the data before change
SELECT * FROM table_name WHERE some_id=5;

-- change the data
UPDATE table_name SET quantity=quantity+1 WHERE some_id=5;

-- select the data after change, and see if everything went alright
SELECT * FROM table_name WHERE some_id=5;

-- undo the change
ROLLBACK TRAN;

Description

  1. A transaction is opened
  2. All current rows are selected, to preview data before change
  3. The change is being done
  4. All altered rows are selected, to preview data after change
  5. The transaction is rollbacked, the change is undone

After you’re sure the change is OK, you can perform it without a transaction, or use COMMIT; (MySQL) / COMMIT TRAN (SQL Server).