How to actually handle database transactions (and why your ORM fails at it)

Transactions are one of those things that seem easy on the surface. You just wrap your code in a begin/commit sandwich and call it a day, right? Well... not exactly. There are a lot of ways to shoot yourself in the foot.

Today we are going to look at how to properly handle transactions. Let's take a look at how we can make it better using PHP and MariaDB.

Perfect code for handling transactions

Let's start with a practical thing: how should a transaction block actually look in your code?

$this->beginTransaction();

try {
    $this->doStuff();
    $this->commit();
} catch (\Throwable $e) {
    $this->rollback();
    throw $e;
}

Notice something specific here? Why is beginTransaction outside of the try/catch block?

Because there might be an outer transaction running, and beginTransaction itself can fail. If it fails, and you have it inside the try block, your code will jump to the catch block and execute $this->rollback(). But if it failed (assuming we have begin in a try-catch), you are now rolling back a transaction that doesn't belong to you! You're rolling back the outer transaction. That can lead to all sorts of bugs.

Rule of thumb: Commit or rollback ONLY after a successful begin transaction.

Outbox pattern

Let's imagine this scenario:

You are processing an order. Inside your transaction, you save the order to the database, and then you send a confirmation email to the user. What happens if the email service timeout and throws an exception but the email went through? Your database rolls back. The user gets an email saying "Order confirmed!" but the order doesn't actually exist in your system.

For this type of situation, we have something called an Outbox.

It's a simple (always in same the database) list of events to send after a successful transaction commit. The idea is to also save events in the same database, so when we commit or rollback, we also handle the async stuff.

$this->beginTransaction();

try {
    $this->saveOrder($order);
    
    // Instead of sending email here, we just save the intent to the DB!
    $this->outboxRepository->addMessage(
        type: 'send_order_email', 
        payload: ['order_id' => $order->getId()]
    );
    
    $this->commit();
} catch (\Throwable $e) {
    $this->rollback();
    throw $e;
}

Because the order and the outbox message are saved in the same transaction, it's 100% atomic. Either both happen, or neither happens. Then, you just have a separate background worker (like a cron job) that reads the outbox table and actually sends the emails.

Locks

We need to talk about processes touching the same data at the same time.

By default, when you SELECT something in MariaDB (InnoDB), you are just reading a snapshot. The database doesn't stop other people from reading or even modifying that same row.

If you are reading a row with the intention of updating it, you need to lock it. Otherwise, someone else might change it right after you read it.

Like you still see a snapshot of the data that is no longer true. You are driving your business logic on outdated information.

To do this, we use Pessimistic Locking, which in SQL translates to SELECT ... FOR UPDATE.

SELECT * FROM product WHERE name = 'computer' FOR UPDATE;

When you run this inside a transaction, MariaDB tells everyone else: "Hey, dont touch this this row for now. I'm about to change it." If another transaction tries to run a SELECT ... FOR UPDATE on the same row, it will be forced it to wait until your transaction either commits or rollsback.

You may ask why it's so important. Well, let me show you an example that will fail without a lock in the next section :)

Pitfalls of ORMs and transactions

ORMs usually don't SELECT ... FOR UPDATE by default. This is a massive problem when we only operate on ORM objects or we are updating the same information in different transactions. Imagine this code:

$this->beginTransaction();

try {
    $product = $this->getProductByName('computer');
    $product->decQuantity(1);
    $product->save();
    
    $this->commit();
} catch (\Throwable $e) {
    $this->rollback();
    throw $e;
}

And we assume that decQuantity is something like:

public function decQuantity(int $quantity) {
    $this->setQuantity($this->quantity - $quantity);
}

What SQL would the ORM probably make? Something like this:

SELECT * FROM product WHERE name = 'computer';
-- PHP calculates: 13 - 1 = 12
UPDATE product SET quantity = 12 WHERE name = 'computer';

ORMs usually check what fields were changed and only change those fields. But the problem is the quantity = 12. We are setting an absolute value, not a relative change, and the ORM didn't lock that field for update.

So imagine what happens when two people buy the computer at the exact same millisecond:

Two items were sold, but the inventory only decreased by 1. This is called the "Lost Update" anomaly.

To fix this, you have two simple options:

1. Force the ORM to lock the row. Most ORMs have a way to do this (e.g., in Doctrine it's $em->lock($product, LockMode::PESSIMISTIC_WRITE)). This forces a SELECT ... FOR UPDATE.
2. Use raw SQL for relative updates. Instead of calculating the math in PHP, let the database do it atomically: UPDATE product SET quantity = quantity - 1 WHERE name = 'computer'

So, locks are quite essential for proper consistency of the data. If you are selecting something with an intent to later base an update statement on that select, pleeease use select for update. You will save yourself a lot of debugging.

What about nested transactions?

Nested transactions are situations where you start one transaction, then execute some other method inside that transaction that also invokes another transaction. Maybe another team created a service you are using and they thought, "better to do it in a transaction to ensure atomicity."

If you've been coding in PHP for a while, you probably know that PDO hates nested transactions. If you call beginTransaction() when a transaction is already active, PDO will just throw an exception at your face.

So how do we handle this?

Most good frameworks solve this under the hood using a transaction counter or SQL SAVEPOINTs. If you are building your own wrapper or using a custom kernel, the simplest way is to keep a counter:

public function beginTransaction(): void 
{
    if ($this->transactionDepth === 0) {
        $this->pdo->beginTransaction();
    }
    $this->transactionDepth++;
}

public function commit(): void 
{
    $this->transactionDepth--;
    if ($this->transactionDepth === 0) {
        $this->pdo->commit();
    }
}

public function rollback(): void 
{
    if ($this->transactionDepth > 0) {
        $this->pdo->rollBack();
        $this->transactionDepth = 0;
    }
}

This way, only the outermost transaction actually commits to the database.

Also that's why the correct way of invoking new transactions is so critical. If you mess this up, and the amount of begins will not align with the right amount of commits and rollbacks, you may end up in a situation where you "committed but nothing appeared in the DB" or, even worse, you wanted to roll back a transaction that didn't begin.

The silent killer: Deadlocks

Even if you use SELECT ... FOR UPDATE perfectly, you can still crash your system with deadlocks.

Imagine Transaction A wants to update Product 1, then Product 2. At the exact same time, Transaction B wants to update Product 2, then Product 1.

Notice the reversed order of the updates!

The problem was that both transactions are waiting for the other one. Teoreticly they can wait forever. MariaDB will eventually detect this loop and brutally kill one of the transactions. In such case, it would be nice to have the outbox described before. So we don't have any side-effects after rollback invoked by the database itself.

The solution? Always lock your rows in the exact same order. If you need to update multiple rows in a batch, sort them by their IDs first and lock them numerically (e.g., ID 1, then ID 2). Of course, ensuring this is extremely hard in large applications. So even if you try to do everything right, you will probably still see some deadlocks.

Conclusion

Database transactions seem like a basic concept you learn on day one, but they are full of hidden traps when you use them in real-world applications.

The key takeaway is that you should do whatever you can to prevent any inconsistencies and deadlocks, but also craft your code so that it is prepared for such inconsistencies to happen. Your application should have a way to manage unexpected problems.