Backend shorts - Use database transactions

Database transactions have many use cases, but in this post we will be only looking at the simplest and most common one: all or nothing semantics aka atomicity.

If you issue multiple related database queries that modify data (INSERT, UPDATE, DELETE), then you should most likely use a transaction. Here is a quick example using Laravel:

function submitOrder(Request $request)
{
    // ....

    $order = new Order($someOrderData);
    $payment = new Payment($somePaymentData);

    DB::transaction(function () use ($order, $payment) {
        $order->save();
        $payment->save();
    }

    // ...
}

Maybe for a better understanding, here is what is going on:

function submitOrder(Request $request)
{
    // ....

    // YOU: Hey DB, I want to save a bunch of data.
    DB::transaction(function () {
        // DB: Ok, tell me everything you want to save.

        // YOU: This order right here.
        $order->save();

        // YOU: And this payment right there.
        $payment->save();

       // YOU: And that's all. Just those two.
    });
    // DB: Alright, order and payment have been saved.

    // ...
}

By wrapping the save() calls in a DB::transaction, we make sure that either all models are saved or none of them. Or said differently, our data is always in a consistent state, which is one of the primary tasks of a backend developer. There will never be an order without a payment.

What could go wrong?

Let's play through the scenario in which we did not use a transaction. Say the order was successfully saved but just when calling $payment->save() the DB goes offline, or the PHP process times out, or it crashes, or the whole machine crashes. You now have an order without a payment, which is probably not something that your application was designed to handle correctly.

The consequences will depend on the exact circumstances, but it is not something that you want to get an angry phone call at 8pm on a Sunday for.