Database Transactions: Where commitment isn’t scary!

Unlocking the power of consistency.

Picture this: You are at the helm of a production database for your flourishing financial application. The user base is expanding quick and you think you are on the verge of creating the next unicorn (FTX jokes notwithstanding). But then, a sudden power hiccup hits your database instance. Everything appears normal and yet a few users start complaining about incorrect balances in their accounts. Turns out, the power on your database instance went out in between debiting and crediting queries, exposing a flaw in your approach (I will spare you the Sam Bankman-Fried jokes). ]In an instance like this you wonder that it would be great if there was a way to perform both actions or neither of them. Enter transactions, the superhero of the database world.

The above scenario underscores the need for atomicity — the notion that an action should be “all or nothing”, and this is exactly where transactions shine. Database transactions allow you to bundle multiple actions into one mighty unit of work. While the above example may seem like an outlier, really any kind of failure between the above queries would result in the undesired state. For example, consider an engineer accidentally pushing a bug into your application that disrupts the flow between these critical queries. The second query refuses to execute, leading to potential issues. (Insert funny joke about customers here — because who doesn’t like a good laugh amidst a database crisis?)

Transactions can be described by their ACID (Atomicity, Consistency, Integrity and Durability) properties. Let’s look at how each of them can be described:

1. Atomicity: Transactions ensure that every move is decisive. Either all actions are executed flawlessly or everything is rolled back to its original state.
2. Consistency: In ACID terms, this essentially means that a transaction brings a database from one valid state to another. It prevents the database from entering an inconsistent state where data integrity is compromised
3. Isolation: Isolation deals with the concurrency of transactions. It ensures that the data manipulated by a transaction is not visible to other transactions until the transaction is committed to the database.
4. Durability: With transaction this just means that a transaction’s changes are permanent. The database stores these changes to disk ensuring that they are persistent.

Now you can see why managing a database without a transaction would get cumbersome and extremely complicated.

States of a transaction:

Now that you know why we need a transaction, let’s talk about the states within one:

1. Active: This is the first state of a transaction and is the state that is seen when the individual actions within the database transaction is taking place. A transaction is active as long as they are interacting with the database.
2. Partially Committed: Once all steps finish you get to this stage. But the data is still in memory and has not been written to the disk yet. A change has been executed but not yet committed to memory.
3. Committed: In this state all the transaction updates are permanently written to the database. It means all the data to write the transaction has been stored on disk. When this is achieved the transaction can be finalized correctly.
4. Failed: If a transaction fails or has been aborted after it reaches the partially committed state it reaches this state. This is where the rollback part of the failed transaction happens.
5. Terminated: After a transaction is failed or committed it reaches the terminated state. This means that the changes are completely applied or rolled back. This marks the end of this cycle.

Image indicating database transaction states

Let’s write one

Here is a simple SQL transaction describing the above example:

-- initializing the transaction

START TRANSACTION;

-- deducting money from user A

UPDATE users
SET balance = balance - 100
WHERE id = 1;

-- add money to user B

UPDATE users
SET balance = balance + 100
WHERE id = 4;

-- commit the change once this is done

COMMIT;

Often we use ORM’s or query builders with our applications to avoid writing SQL strings within our codebases. In those cases, it is very likely that the library you are using will provide you with a similar interface. But while you may not be writing raw SQL the above gives you an idea of what is going on under the hood.

And that’s it:

That is all you need for a basic understanding about database transactions. It is a core component of working with database management systems and a fundamental understanding of the concept can boost your confidence while manipulating your database. Now you can go out into the world and show off your shiny new knowledge about transactions.

But hold on, what about NoSQL databases. Do they support transactions? Well yes some of them do depending on the type of NoSQL database and the vendor. Take a look at MongoDb and AWS DynamoDb’s documentation, two of the most popular NoSQL databases on the market that do support transactions.

Please subscribe to my newsletter here to get these articles as soon as they are out: https://programmerbytes.substack.com/