HomeArticlesDatabase Transactions and Concurrency: ACID Guarantees in Practice
Software Architecture

Database Transactions and Concurrency: ACID Guarantees in Practice

10 min read363 words
Database Transactions and Concurrency: ACID Guarantees in Practice — Sufi Khan Sulaiman

ACID (Atomicity, Consistency, Isolation, Durability) is the gold standard for database transactions. But ACID is not binary. The isolation level determines what guarantees you actually get, and most teams use the default without understanding the tradeoffs. After managing databases across Lorex, FLIR, and 1c Platform, I've learned that concurrency bugs are the hardest to diagnose because they're non-deterministic.

Isolation Levels

Read Uncommitted (can read uncommitted data, almost never used). Read Committed (only read committed data, PostgreSQL default). Repeatable Read (same row read twice returns same value, MySQL default). Serializable (transactions execute as if sequential, full isolation, lowest concurrency). Higher isolation means more locks, more waiting, less concurrency.

The Concurrency Anomalies

Dirty Read (reading uncommitted data, prevented by Read Committed+). Non-Repeatable Read (row changes between reads, prevented by Repeatable Read+). Phantom Read (set of rows changes between reads, prevented by Serializable). Write Skew (two transactions read overlapping data, make decisions, write non-overlapping data, combined result violates constraint, prevented by Serializable). Write skew is the most insidious because it's not prevented by Repeatable Read.

Optimistic vs Pessimistic Concurrency

Pessimistic: lock the data when you read it (SELECT FOR UPDATE). Good for high-contention. Optimistic: don't lock, check if data changed at commit time, retry if it did. Good for low-contention. At 1c Platform, we used optimistic for most operations and pessimistic for high-contention (flash sale inventory).

The Deadlock Problem

Deadlocks occur when two transactions hold locks the other needs. The database kills one (the victim). Prevention: consistent lock ordering (always lock resources in the same order), short transactions, lower isolation levels. The application must handle deadlock errors and retry.

The Outbox Pattern

To atomically update a database and publish an event, use the outbox pattern: write to the database and the outbox table in the same transaction. A separate process reads the outbox and publishes events. This ensures the event is published if and only if the transaction commits. No lost events, no phantom events.

Testing Concurrency

Concurrency bugs are non-deterministic. Catch them with load testing strategy pyramid, chaos testing strategy pyramid (inject delays and failures), and property-based testing (generate random transaction sequences and verify invariants). At 1c Platform, we ran 1000 random transaction sequences and verified final state consistency.

Sufi Khan Sulaiman

Sufi Khan Sulaiman

VP Technology & CTO with 25+ years building ecommerce platforms, enterprise systems, and AI solutions

Expertise across ecommerce strategy, cloud architecture, AI & machine learning, DevOps, and technology leadership. Led teams at FLIR Systems, Lorex Technology, 1c Platform, and Genetec.

Explore the Full Portfolio

This is the complete portfolio of Sufi Khan Sulaiman, a technology leader specialising in B2B commerce and digital automation. Start from the Home page for the overview, then move through two decades of career experience across FLIR Systems, Lorex Technology, and 1c Platform, and the full catalogue of project case studies spanning headless commerce migrations, AI recommendation engines, and multi-channel fulfilment systems.

The skills and certifications page maps the technical and leadership capabilities behind the work, while the articles and the knowledge base break down the thinking into actionable frameworks. For hands-on learning, the tutorials and applications sections cover practical builds from front-end fundamentals to full-stack web apps.

For consulting engagement, the expertise page outlines service offerings, the ecommerce hub covers platform architecture and automation strategy, and the ecommerce guide (PDF) is a downloadable 55-page field manual. When you are ready to talk, the contact page is the direct line.