What is a database transaction (using MySQL)?

When working with databases, you might need to ensure that multiple changes are executed together or not at all. This article shows how easy it is to achieve this using MySQL transactions.

If you encounter such a situation, consider using transactions over custom implementations.

Basic description of database transactions #

You can think of a MySQL transaction as it would be a “group” of queries. Either all of them run successful or none of them will be executed, if you like. Also: They all will happen at the very same time. A transaction is something that you need to start at some point and then stop again, once all desired queries are added to the “group”.

When stopping a database transaction you can either save all successful queries or deny all changes of your “group”.

MySQL transaction in action #

Lets create a list of friends:

1
2
3
4
5

CREATE TABLE friends
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) UNIQUE
);

Now, let’s add two friends with the same name:

1
2
3

INSERT INTO friends (name) VALUE ("Golo");
INSERT INTO friends (name) VALUE ("Golo");
SELECT * FROM friends;

You will see an error on the second INSERT but also you’ll see that one entry has been created:

mysql> INSERT INTO friends (name) VALUE ("Golo");
Query OK, 1 row affected (0.001 sec)

mysql> INSERT INTO friends (name) VALUE ("Golo");
ERROR 1062 (23000): Duplicate entry 'Golo' for key 'friends.name'
mysql> SELECT * FROM friends;
+----+------+
| id | name |
+----+------+
|  1 | Golo |
+----+------+
1 row in set (0.000 sec)

Deleting this now would be some manual work for you. In this small example for sure you can do it, but in production systems it will just be a mess. So let’s empty the table again and see how transactions can help us:

1

DELETE from friends;

Now, before we change anything, let’s start the transaction:

1

START TRANSACTION;

There’s not much output created, we just get the feedback that our query is accepted:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)

After this, let’s run our three queries again:

1
2
3

INSERT INTO friends (name) VALUE ("Golo");
INSERT INTO friends (name) VALUE ("Golo");
SELECT * FROM friends;

You probably now get scared, because the ouput will be identical to our previous one:

mysql> INSERT INTO friends (name) VALUE ("Golo");
Query OK, 1 row affected (0.001 sec)

mysql> INSERT INTO friends (name) VALUE ("Golo");
ERROR 1062 (23000): Duplicate entry 'Golo' for key 'friends.name'
mysql> SELECT * FROM friends;
+----+------+
| id | name |
+----+------+
| 2 | Golo |
+----+------+
1 row in set (0.000 sec)

This is totally fine. This is kind of the moment when transactions will save your ass. Now, when you realise something has gone very different from what you expected, you can decide to anyways save it or undo all changes. For right now, let’s undo all changes and check the table again:

1
2

ROLLBACK;
SELECT * FROM friends;

I hope now you can breath again:

mysql> ROLLBACK;
Query OK, 0 rows affected (0.002 sec)

mysql> SELECT * FROM friends;
Empty set (0.001 sec)

As you can see, ROLLBACK is the moment when all changes are undone. “All changes” means everything from the moment we did START TRANSACTION, for sure.

Ensure data integrity with transactions #

You probably wonder, why when using transactions you could see something when running SELECT * FROM friends? I said all changes happen at the same time but the transaction wasn’t stopped yet but still you see changes? There’s a simple reason to it.

MySQL can deal with multiple, concurrent connections. When you login, you get your own connection / session to the database. You can login twice in parallel so you have two different connections / sessions.

That in mind, when you starting a transaction, in your current session you will see all changes at the time you send the respective query. Other connections will not see any of your actions.

Testing transactions with concurrent connections #

Open two connections to the very same database. In both of them, run SELECT CONNECTION_ID();:

Connection 1

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              13 |
+-----------------+
1 row in set (0.004 sec)

Connection 2

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              11 |
+-----------------+
1 row in set (0.001 sec)

As you can see, it’s different connections. Now in connection 1 let’s start a transaction, add our two friends again and check the table:

Connection 1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.001 sec)

mysql> INSERT INTO friends (name) VALUE ("Golo");
Query OK, 1 row affected (0.004 sec)

mysql> INSERT INTO friends (name) VALUE ("Golo");
ERROR 1062 (23000): Duplicate entry 'Golo' for key 'friends.name'
mysql> SELECT * FROM friends;
+----+------+
| id | name |
+----+------+
| 3 | Golo |
+----+------+
1 row in set (0.001 sec)

Ok nothing new so far. But let’s check what connection 2 can see:

Connection 2

mysql> SELECT * FROM friends;
Empty set (0.001 sec)

As you can see, the very same query gives another result. From the moment we save the transaction (using COMMIT instead of ROLLBACK), then both connections will see the same again:

Connection 1

mysql> COMMIT;
Query OK, 0 rows affected (0.007 sec)

mysql> SELECT * FROM friends;
+----+------+
| id | name |
+----+------+
| 3 | Golo |
+----+------+
1 row in set (0.001 sec)

Connection 2

mysql> SELECT * FROM friends;
+----+------+
| id | name |
+----+------+
| 3 | Golo |
+----+------+
1 row in set (0.001 sec)

Can I use transactions in code? #

Sure. In today’s world, where logging into production databases is mostly a no-go, it’s pretty normal to add transaction handling to your source code quiet often.

How exactly it’s done depends heavily on the environment you work with. It’s a well known topic, you can find many examples online and any LLM should be able to answer your question with ease.

Hint: To decide whether to COMMIT or ROLLBACK a transaction, you need to check each of your queries if they where successful or not. That means you need some form of error handling in your code to deal with this situation.