Transactions and Concurrency: what you should know
There are concepts that make the difference between a junior and a senior software engineer. Concepts that would fill several books. Here I try to close that gap a little between what we know and how it all connects.
For the last few weeks I’ve been mulling over how operations and concurrency work. What guarantees that a critical operation won’t break the system?
Transaction Fundamentals
Nothing. There is no configuration that’s right for every case. What does exist is understanding how transaction states behave when they work together. A transaction is a consistency contract. When we don’t understand what happens at the levels below, we leave the system’s integrity to chance.
This becomes obvious when we have to work on financial services, cryptocurrencies, inventory management, reservations or distributed systems.
ACID Transactions
When databases started being used in critical systems, failures left data in inconsistent states, causing costly errors. In the 80s, Jim Gray and other researchers defined the ACID properties: Atomicity, Consistency, Isolation and Durability. Four rules that guarantee that, even in the face of failures, data stays correct.
Atomicity
It’s the “all or nothing” idea. A transaction can have 10 steps, but if step 9 fails, the other 8 are undone as if they had never existed. This is achieved through logs (Write-Ahead Logging). The database writes down what it’s going to do before touching the actual data. If there’s an error, it uses that log to apply a Rollback and return to the initial state.
Consistency
It guarantees that a transaction only takes the database from one valid state to another. The core of this property is enforcing business rules. The tools involved are Triggers, Foreign Keys, Check Constraints and data types. For example. If you try to set a negative volume and there’s a rule forbidding it, the transaction bounces.
Isolation
This is where things get interesting. When several transactions try to access and modify the same resource at the same time, they can’t simply run in parallel without coordination. If they did, we’d end up with inconsistent states. That’s why a transaction has to wait its turn while another one finishes its operation on that same data.
Concurrency happens when the execution of two or more pieces of code behaves as if they were running at the same time.
To deal with concurrency, coordination is achieved with mutual exclusion primitives. At a low level we’re talking about mutexes: mechanisms that guarantee that only one thread or process accesses a critical resource at a time (if you want to know more). In databases, the concept translates into locks, which apply that exclusion on rows, pages or tables. It’s not optional cooperation; the engine enforces these rules to protect integrity.
| Locking Type | Description | Characteristics |
|---|---|---|
| Pessimistic Locking 🙁 | “I put a lock on the data until I’m done” | Nobody else touches anything. It’s safe but slow. |
| Optimistic Locking 🙂 | “I don’t lock, I assume nobody else will touch it” | At the end, I check whether someone changed something. If someone did, I abort and retry. It’s much faster for high-traffic systems. |
Now then, when and how are these locks applied? That’s where isolation levels come in: settings that define how strict the database will be when applying locks between concurrent transactions. Each level makes different trade-offs between consistency and performance:
| Level | Description | Characteristics |
|---|---|---|
| Read Uncommitted | The “Wild West” | A transaction can see changes from another one that haven’t been committed yet. Risk: Dirty reads. |
| Read Committed | PostgreSQL’s default | You can only read data that has already been committed (Committed). |
| Repeatable Read | Prevents non-repeatable reads | Guarantees that if you read a record twice in the same transaction, you’ll see the same thing, even if someone else modified it in between. |
| Serializable | Maximum safety level | Transactions run as if they were standing in a single-file line, one after another, even though they happen in parallel. It’s the safest but the slowest. |
Durability
It’s the promise that, once the system tells you “OK, saved”, that information won’t be lost even if the power goes out or the server blows up right afterwards.
Changes are written to non-volatile storage (hard drive/SSD) and confirmed in the transaction log. Even after a catastrophic failure, on restart the database reads the log and finishes applying whatever was left pending.
Beyond ACID
Nowadays, these concepts are the foundation of relational databases. However, understanding this is just the first step. When you make the jump to modern architectures with microservices and NoSQL databases, the engine can no longer guarantee ACID across the whole system, because your data now lives on separate servers and the rules of the game change.
When distributing information, ACID’s rigidity is often traded for a more flexible approach that prioritizes the system never going down. We call this new paradigm BASE:
| BASE Property | Description |
|---|---|
| Basically Available | The system guarantees it will always respond to requests, even if some nodes or servers fail. |
| Soft state | Since data is being synchronized between servers, the system’s state can change over time, even if there are no new write operations. |
| Eventual consistency | The promise that, if we stop sending changes and wait long enough, all servers will eventually have the same version of the data. |
Going from ACID to BASE means accepting that your system will live with some temporary inconsistency in exchange for being incredibly fast and resilient to massive outages. At this point data integrity stops coming from the engine’s configuration and becomes design patterns you have to implement in code (like the Saga pattern or Two-Phase Commit) to tie up the loose ends.
But that… is a story for another post.
Let's stay in touch: learn more about me or chat on Twitter / X · Instagram .