Transactional Workloads: Keeping Data Consistent
When money moves, inventory changes, or orders are placed, every step must succeed — or none of them should. That's the world of transactional workloads.
What is a transactional workload?
A transactional workload is any system where data must be 100% accurate, right now.
Think of paying for coffee with your debit card. Three things happen: your bank subtracts $4.00, the café’s bank adds $4.00, and a receipt is created. If the café’s bank fails to add the money, your bank should NOT subtract it either. All steps succeed together, or none of them happen. That’s a transaction.
Systems that handle these kinds of “all-or-nothing” operations are called transactional workloads — also known as OLTP (Online Transaction Processing).
ACID: the four guarantees
Every transactional system follows ACID properties — four rules that ensure data stays correct:
| Property | What It Means | Tom’s Delivery Example |
|---|---|---|
| Atomicity | All steps in a transaction succeed, or none of them do | A delivery is marked “complete” AND the driver’s route is updated — or neither happens |
| Consistency | Data always moves from one valid state to another | The total number of packages in the system always matches what was loaded onto trucks |
| Isolation | Concurrent transactions don’t interfere with each other | Two dispatchers assigning drivers at the same time don’t accidentally assign the same driver twice |
| Durability | Once a transaction is committed, it’s permanent — even if the power goes out | Once Tom confirms a delivery, that record survives a server restart |
ACID analogy: the bank transfer
The classic example: transferring $100 from Account A to Account B.
- Atomicity: Subtract $100 from A AND add $100 to B — both steps or neither
- Consistency: Total money in the system stays the same (A + B = constant)
- Isolation: Another person transferring money at the same time sees consistent balances
- Durability: Once the transfer is confirmed, it’s saved permanently
If the system crashes between step 1 (subtract from A) and step 2 (add to B), atomicity ensures the subtraction is rolled back. The money doesn’t disappear.
Characteristics of transactional workloads
| Feature | Description |
|---|---|
| High volume, small operations | Thousands of inserts, updates, and deletes per second |
| Low latency | Each operation completes in milliseconds |
| Current data | Always shows the latest state (not historical trends) |
| Multiple concurrent users | Many users reading and writing at the same time |
| ACID compliance | Every operation follows the four ACID properties |
| Normalised schema | Data is organised to minimise duplication (normalisation) |
Real-world examples:
- Priya’s FreshMart: point-of-sale system recording every item scanned at checkout
- Tom’s Pacific Freight: dispatch system assigning drivers to deliveries
- Aisha’s food app: order placement (choose items → confirm → pay)
- Jake’s CloudPulse: user account creation, subscription management
OLTP vs the next module
Transactional workloads (OLTP) are about recording what’s happening right now. The next module covers analytical workloads (OLAP), which are about understanding what happened in the past to make better decisions.
| Feature | Transactional (OLTP) | Analytical (OLAP) |
|---|---|---|
| Purpose | Record current operations | Analyse historical data |
| Operations | Insert, update, delete individual records | Read large volumes, aggregate, summarise |
| Data | Current state (live data) | Historical snapshots (over time) |
| Users | Many concurrent users (clerks, apps, customers) | Fewer users (analysts, managers) |
| Speed priority | Fast individual operations (milliseconds) | Fast bulk queries (seconds to minutes) |
| Schema | Normalised (minimise duplication) | Denormalised (optimise for reading) |
Exam tip: OLTP keyword spotting
When the exam describes a scenario, look for these OLTP signals:
- “Recording sales as they happen” → OLTP
- “Multiple cashiers entering data simultaneously” → OLTP
- “Must ensure no duplicate bookings” → OLTP (isolation)
- “If payment fails, the order should be cancelled” → OLTP (atomicity)
If the scenario talks about “trends,” “reports,” “dashboards,” or “historical analysis” — that’s analytical (next module).
Flashcards
Knowledge check
Tom's dispatch system needs to assign a driver to a delivery. The system must: (1) mark the driver as 'busy,' (2) link the driver to the delivery, and (3) update the delivery status. If step 2 fails, what should happen?
Which ACID property ensures that two customers buying the last concert ticket at the same time don't BOTH get it?
🎬 Video coming soon
Next up: Analytical Workloads: Finding the Insights — from recording data to understanding it.