What are database transactions?

Overview

In simple words, a transaction or a unit of work is a set of database operations that we want to treat as  "a whole." It has to either happen completely or not at all.

To ensure the correctness of a transaction, a database must be atomic, consistent, isolated, and durable. These four properties are commonly known under the acronym ACID.

Atomicity

More often, but not always, a transaction is made of multiple SQL statements. The atomic property states that all the statements must either be complete entirely or have no effect whatsoever. No partial execution should be permitted. The idea is that a transaction must always leave the database in a consistent state. This leads us to the second property.

Consistency

From the previous explanation, we understand that the transaction must only bring the database from one valid state to another. This property ensures that there are no database constraints violations.

Isolation 

The isolation property guarantees that the uncommitted state changes are not visible or do not affect other concurrent transactions.

Durability

This property states that a committed transaction must permanently change the state of the database, even in case of a system failure like a power outage or crash. This implies that a successful transaction must always be recorded in non-volatile memory and/or a persisted transaction log.

Code example

A typical example to understand transactions is bank transfers.

Let's say we have two clients in our database, Sarah and John. The first one wants to transfer an amount of 20 dollars to the second client. Here's the process so far:

1. Decrease Sarah's total amount by 20 dollars
2. Increase John's total amount by 20 dollars

But the problem is that both of these two operations must be fully completed (commit) or not at all (rollback).

Let's make sure our database works properly:

Now we'll proceed with our operations above.

The first operation looks like this:

Let's implement the second operation:

The operation is completed. We run two separate operations in this process. The best thing to do is to run both of these as ONE operation. This leads us to transactions.