Running SQL safely

UPDATE orders SET status = 'cancelled'

Neither mistake was about knowing SQL. Both were about running a change against production with no way to check it, and no way back. This lesson is the routine that gives you both.

A transaction is a group of changes that the database treats as one. Either all of them happen, or none of them do. While it’s open, you can look at what you’ve changed and then decide.

Autocommit: the default that catches people

MySQL starts every connection with autocommit on. It means every statement you run outside a transaction is committed the moment it finishes. An UPDATE typed on its own is permanent before you’ve read its output.

START TRANSACTION switches that off until the next COMMIT or ROLLBACK. That’s why the routine below always starts with it.

Nobody else sees it until you commit

While your transaction is open, other connections, including QuickCart’s app, keep reading the last committed data. MySQL’s default setting for this, called REPEATABLE READ, guarantees it. So you can make the change, look at it, and back out, and no customer ever sees the middle.

The catch is that the rows you’ve changed are locked until you finish. Anything else that tries to write to those rows waits. So open the transaction, check, and finish it within a minute or two. Never leave one open while you go for coffee.

Some statements can’t be rolled back

Transactions cover changes to data: INSERT, UPDATE and DELETE. Changes to the structure of the database, called DDL, are different. MySQL commits before and after each one, even in the middle of your transaction.

That second point surprises people. If you run an UPDATE, then a CREATE TABLE, then ROLLBACK, the UPDATE stays. The CREATE TABLE committed it. You’ll see this happen in the lab.

The routine, every time

The undo script matters because a transaction only protects you until you commit. A mistake you notice an hour later, such as “those orders were paid after all”, needs its own way back. Writing it before the change means you’re not inventing it under pressure.

Reaching a database in a private subnet

QuickCart’s database lives in a private subnet with no public address, as the RDS lesson set up. Nothing on the internet can reach it, and that includes your laptop. You get there through something inside the VPC that can.

The modern way is Session Manager port forwarding. You pick an EC2 instance in the VPC that is allowed to reach the database, such as one of the app servers. Session Manager opens a tunnel from a port on your laptop, through that instance, to the database. Your laptop’s SQL client connects to 127.0.0.1 and ends up talking to the database.

There’s no inbound port open anywhere, no SSH key to lose, and every session is recorded against your AWS login. The older way was a bastion: a server in a public subnet with SSH open to the world, used as a stepping stone. Session Manager replaces it.

The command to open the tunnel is below. It uses three values. INSTANCE_ID is the instance’s ID from EC2 → Instances, and DB_ENDPOINT is the database’s Endpoint from RDS → Databases → your database → Connectivity & security.

aws ssm start-session --target INSTANCE_ID --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters '{"host":["DB_ENDPOINT"],"portNumber":["3306"],"localPortNumber":["3306"]}'

Starting session with SessionId: maya-0a1b2c3d4e5f67890
Port 3306 opened for sessionId maya-0a1b2c3d4e5f67890.
Waiting for connections...

Your session ID will differ. Leave this terminal open: the tunnel lasts as long as the command runs. In a second terminal, connect through it:

mysql -h 127.0.0.1 -P 3306 -u admin -p quickcart

Below are QuickCart’s orders. The left side is your session; the right is what everyone else sees, including the app. Three orders are pending and need cancelling.

First, run the UPDATE with no WHERE without starting a transaction, and try to roll it back. Then choose Start again, open a transaction, and do the same thing.

mysql> -- autocommit is on, as it is by default

What to notice: the two mistakes were the same statement. Without a transaction, the right-hand side changed instantly and nothing could bring it back. Inside one, only your side changed, and ROLLBACK made it as if it had never happened. The row count, 8 where you expected 3, was the warning both times.

Free — the lab runs MySQL in Docker on your own laptop. If you use an RDS database instead, that costs money while it runs.

You’ll set up a small orders table, save the rows you’re about to change, practise the no-WHERE mistake safely, watch a second session not see it, make the real change, and then undo it with your undo script. About 30 minutes.

You need Docker running on your laptop. Set up your computer covers installing it.

1. Start MySQL in a container

   TerminalCopy

   docker run --name quickcart-mysql -e MYSQL_ROOT_PASSWORD=practice-only -p 3306:3306 -d mysql:8.4

   Part	What it means
   docker run	start a new container from an image
   --name quickcart-mysql	call the container this, so later commands can refer to it
   -e MYSQL_ROOT_PASSWORD=practice-only	set an environment variable that the MySQL image reads on first start, to set the root password. This password is for local practice only. Never put a real one on a command line
   -p 3306:3306	make port 3306 inside the container reachable on port 3306 of your laptop
   -d	run it in the background
   mysql:8.4	the official MySQL image, version 8.4

   You should see

   The first time, Docker downloads the image, which takes a minute. Then it prints one long line of letters and numbers: the new container’s ID.

   MySQL takes about 20 seconds to get ready the first time. Check it’s up:

   TerminalCopy

   docker logs quickcart-mysql

   Part	What it means
   docker logs	print what a container has written to its output
   quickcart-mysql	the container’s name

   You should see

   Near the end, a line containing ready for connections and port: 3306. If it isn’t there yet, wait a few seconds and run the command again.

2. Open two sessions

   Open two terminal windows. In each one, run:

   TerminalCopy

   docker exec -it quickcart-mysql mysql -u root -p

   Part	What it means
   docker exec	run a command inside a container that’s already running
   -it	connect your keyboard and screen to it, so you can type
   quickcart-mysql	which container
   mysql -u root -p	the MySQL client, as the root user, asking for the password

   Type practice-only when it asks for the password. Nothing shows as you type.

   You should see

   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.

   A few more lines follow, then the mysql> prompt. Call them window 1 and window 2. Window 2 plays QuickCart’s app: it only ever reads.

3. Create the orders table

   In window 1, paste this. Each statement ends with a semicolon, and the client runs them one after another:

   SQLCopy

   CREATE DATABASE quickcart;
   USE quickcart;
   CREATE TABLE orders (
     id INT PRIMARY KEY,
     customer_id INT NOT NULL,
     status VARCHAR(20) NOT NULL,
     total DECIMAL(8, 2) NOT NULL
   );
   INSERT INTO orders (id, customer_id, status, total) VALUES
     (1001, 41, 'pending', 18.50),
     (1002, 17, 'paid', 22.00),
     (1003, 52, 'pending', 9.75),
     (1004, 8, 'delivered', 31.20),
     (1005, 23, 'paid', 14.00),
     (1006, 64, 'pending', 27.40),
     (1007, 30, 'delivered', 12.60),
     (1008, 12, 'paid', 19.90);

   Part	What it means
   CREATE DATABASE quickcart	make an empty database
   USE quickcart	run everything after this inside it
   CREATE TABLE orders	make a table with four columns
   id INT PRIMARY KEY	a whole number that’s unique for every order
   status VARCHAR(20)	text up to 20 characters: pending, paid and so on
   DECIMAL(8, 2)	a number with exactly two decimal places. Money is never stored as a floating-point number
   INSERT INTO … VALUES	add eight rows

   The last line of output should be:

   You should see

   Query OK, 8 rows affected (0.01 sec)
   Records: 8  Duplicates: 0  Warnings: 0

   Timings like (0.01 sec) will differ on your machine throughout this lab.

   In window 2, choose the same database:

   SQLCopy

   USE quickcart;

   You should see

   Database changed

4. Save the rows, and write the undo script first

   The job is to cancel the three pending orders. Before anything else, in window 1, keep a copy of exactly the rows you’re about to change, with their current values:

   SQLCopy

   CREATE TABLE orders_before_fix AS
   SELECT id, status FROM orders WHERE status = 'pending';

   Part	What it means
   CREATE TABLE orders_before_fix AS	make a new table from the result of a query
   SELECT id, status FROM orders	the ID and the current status of each row
   WHERE status = 'pending'	the same condition the real change will use

   You should see

   Query OK, 3 rows affected (0.02 sec)
   Records: 3  Duplicates: 0  Warnings: 0

   This comes before the transaction on purpose: CREATE TABLE is DDL, and it would commit anything already in a transaction.

   Now write the undo script, and don’t run it yet. Keep it in a text file next to your notes:

   SQLCopy

   UPDATE orders
   JOIN orders_before_fix ON orders_before_fix.id = orders.id
   SET orders.status = orders_before_fix.status;

   Part	What it means
   UPDATE orders	change rows in the orders table
   JOIN orders_before_fix ON … id = orders.id	only the rows whose ID is in the saved copy
   SET orders.status = orders_before_fix.status	put each one’s saved status back

5. Count the rows with the same WHERE

   SQLCopy

   SELECT id, status FROM orders WHERE status = 'pending';

   You should see

   +------+---------+
   | id   | status  |
   +------+---------+
   | 1001 | pending |
   | 1003 | pending |
   | 1006 | pending |
   +------+---------+
   3 rows in set (0.00 sec)

   Three. That’s the number the UPDATE must report. Anything else means stop.

6. Make the mistake, safely

   Now do what Noor did, but inside a transaction. In window 1:

   SQLCopy

   START TRANSACTION;

   You should see

   Query OK, 0 rows affected (0.00 sec)

   Then the UPDATE with its WHERE missing:

   SQLCopy

   UPDATE orders SET status = 'cancelled';

   You should see

   Query OK, 8 rows affected (0.00 sec)
   Rows matched: 8  Changed: 8  Warnings: 0

   8, not 3. That’s the moment to stop. First, look at what window 1 sees:

   SQLCopy

   SELECT status, COUNT(*) FROM orders GROUP BY status;

   Part	What it means
   SELECT status, COUNT(*)	each status, and how many rows have it
   GROUP BY status	one line per status, instead of one per row

   You should see

   +-----------+----------+
   | status    | COUNT(*) |
   +-----------+----------+
   | cancelled |        8 |
   +-----------+----------+
   1 row in set (0.00 sec)

7. See that nobody else can see it

   In window 2, run the same query:

   SQLCopy

   SELECT status, COUNT(*) FROM orders GROUP BY status;

   You should see

   +-----------+----------+
   | status    | COUNT(*) |
   +-----------+----------+
   | pending   |        3 |
   | paid      |        3 |
   | delivered |        2 |
   +-----------+----------+
   3 rows in set (0.00 sec)

   The rows may come out in a different order. Window 2 still sees the real data. If this were production, customers would have noticed nothing.

   Back in window 1, undo it:

   SQLCopy

   ROLLBACK;

   You should see

   Query OK, 0 rows affected (0.00 sec)

   Run the count again in window 1. It now matches window 2: 3 pending, 3 paid, 2 delivered.

8. Make the real change

   In window 1, the whole routine, one statement at a time:

   SQLCopy

   START TRANSACTION;

   SQLCopy

   UPDATE orders SET status = 'cancelled' WHERE status = 'pending';

   You should see

   Query OK, 3 rows affected (0.00 sec)
   Rows matched: 3  Changed: 3  Warnings: 0

   Rows matched: 3 equals the count from your SELECT. Look at the result before committing:

   SQLCopy

   SELECT id, status FROM orders WHERE id IN (1001, 1003, 1006);

   You should see

   +------+-----------+
   | id   | status    |
   +------+-----------+
   | 1001 | cancelled |
   | 1003 | cancelled |
   | 1006 | cancelled |
   +------+-----------+
   3 rows in set (0.00 sec)

   Both checks pass, so commit:

   SQLCopy

   COMMIT;

   You should see

   Query OK, 0 rows affected (0.00 sec)

   Run the count in window 2 again. Now it shows 3 cancelled, 3 paid and 2 delivered. Only COMMIT made the change visible.

9. Run the undo script

   Support calls back: the payment provider has recovered, and those three customers were charged after all. The orders need to go back to how they were. Because you wrote the undo script first, this takes a minute. In window 1:

   SQLCopy

   START TRANSACTION;

   SQLCopy

   UPDATE orders
   JOIN orders_before_fix ON orders_before_fix.id = orders.id
   SET orders.status = orders_before_fix.status;

   You should see

   Query OK, 3 rows affected (0.00 sec)
   Rows matched: 3  Changed: 3  Warnings: 0

   Three again, the size of the saved copy. Check, then commit:

   SQLCopy

   SELECT id, status FROM orders WHERE id IN (1001, 1003, 1006);

   You should see

   +------+---------+
   | id   | status  |
   +------+---------+
   | 1001 | pending |
   | 1003 | pending |
   | 1006 | pending |
   +------+---------+
   3 rows in set (0.00 sec)

   SQLCopy

   COMMIT;

   The undo script runs inside a transaction too. It’s a change to production like any other.

10. See DDL commit behind your back

    This step shows the rule people learn the hard way. In window 1:

    SQLCopy

    START TRANSACTION;

    SQLCopy

    UPDATE orders SET total = 0 WHERE id = 1002;

    You should see

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    Now a CREATE TABLE, then roll back:

    SQLCopy

    CREATE TABLE notes (id INT);

    SQLCopy

    ROLLBACK;

    Check the order you changed:

    SQLCopy

    SELECT id, total FROM orders WHERE id = 1002;

    You should see

    +------+-------+
    | id   | total |
    +------+-------+
    | 1002 |  0.00 |
    +------+-------+
    1 row in set (0.00 sec)

    The rollback didn’t undo it. The CREATE TABLE committed the UPDATE before it ran, and ROLLBACK had nothing left to undo. Put the right value back:

    SQLCopy

    UPDATE orders SET total = 22.00 WHERE id = 1002;

    You should see

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    There was no START TRANSACTION before that one, so autocommit saved it straight away.

11. Clean-up step: Clean up

    Type exit in both windows. Then remove the container and everything in it:

    TerminalCopy

    docker rm -f quickcart-mysql

    Part	What it means
    docker rm	delete a container
    -f	stop it first if it’s running
    quickcart-mysql	which container

    You should see

    quickcart-mysql

    The database lived inside the container, so it’s gone too. If you also want the downloaded image back off your disk:

    TerminalCopy

    docker image rm mysql:8.4

    Part	What it means
    docker image rm	delete a downloaded image
    mysql:8.4	which one

    If you used RDS instead: in window 1, run DROP TABLE orders_before_fix; and DROP TABLE notes;, stop the tunnel with Ctrl+C, and delete the database by following the clean-up in Managed databases: RDS. It costs money until you do.