In partnership with
Transaction might be the best SQL database feature.
It makes database operations much more reliable.
Let’s say you need to pay your buddy.
What does the SQL query look like?
It probably takes two steps.
Subtract from your (user A’s) account.
Add to your buddy's (user B’s) account.
What if the operations fail?
Like a network error, or your phone explodes. Things can always go wrong.
If both fail, we could probably just retry later.
But what would happen if the first query succeeds and the 2nd doesn’t?
Well, things won’t go well.
The problem here isn't the failure.
But the possibility that one query runs without the other.
Transaction prevents this.
What is a transaction?
Rather than running two SQL statements separately, we take an all-or-nothing approach.
We make sure that either all the statements run, or none of them do.
Let’s see how this works in practice.
How to create a transaction?
There are three steps:
1. BEGIN
To create a transaction, use begin:
BEGIN;Any statements after that are put into a transaction. Let's put our previous queries here.
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Sasuke';
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Itachi';Actions inside a transaction are temporary.
They are, in some sense, isolated.
If you run this query inside the transaction
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Sasuke';The balance in the transaction and in the database will be different:
If you read the account balance from a new database connection, you will see that the value is still 200 (for the default isolation level).
That's exactly how we want it to behave.
2. COMMIT
To make the changes permanent, use COMMIT:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Sasuke';
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Itachi';
COMMIT; --hereThis makes the changes permanent. This makes it visible to other concurrent transactions as well.
3. ROLLBACK
When there's an error, the transaction is aborted.
But, usually, you want more control. You want to be able to decide when to undo the changes.
To do that, use ROLLBACK.
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Sasuke';
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Itachi';
--if something goes wrong
ROLLBACK;
--else
COMMIT;(Usually, it’s handled in application code instead of SQL. This is just a demo.)
Every change inside the transaction will be reverted.
Ship Docs Your Team Is Actually Proud Of
Mintlify helps you create fast, beautiful docs that developers actually enjoy using. Write in markdown, sync with your repo, and deploy in minutes. Built-in components handle search, navigation, API references, and interactive examples out of the box, so you can focus on clear content instead of custom infrastructure.
Automatic versioning, analytics, and AI powered search make it easy to scale as your product grows. Your docs stay accurate automatically with AI-powered workflows with every pull request.
Whether you're a dev, technical writer, part of devrel, and beyond, Mintlify fits into the way you already work and helps your documentation keep pace with your product.
To recap:
Transactions make the database more reliable by taking an all-or-nothing approach.
Use transactions in operations that are important and need to be run together.
Use BEGIN to start a transaction, COMMIT to commit the changes, and ROLLBACK to undo the changes.
Fee from Anime Coders
PS: My fav anime art of the week
