ACID in Supply Chain Transactions

Context

A supply chain platform such as FleetCart processes many inventory updates, shipment events, and order transactions at the same time. In this kind of system, database correctness matters as much as speed.

Core question

Explain what ACID means in a relational database and why it matters in a high-transaction supply chain system. Your answer should cover:

1. What each property — Atomicity, Consistency, Isolation, Durability — means
2. What can go wrong if one of these properties is weak or missing
3. Practical examples involving inventory allocation, order creation, payment capture, or shipment updates
4. How transactions in PostgreSQL help enforce these guarantees

Scope guidance

The interviewer is looking for a clear conceptual explanation, not a long theory lecture. Define each ACID property in simple terms, connect it to real operational risks like overselling stock or partial updates, and mention how transaction boundaries, commits, rollbacks, and concurrent access affect system reliability.

Atomicity

Atomicity means a transaction is treated as a single unit of work: either all statements succeed or none do. In a supply chain workflow, this prevents partial updates such as reducing inventory without creating the corresponding order record.

BEGIN;
UPDATE inventory
SET quantity_available = quantity_available - 5
WHERE sku = 'SKU-1001';

INSERT INTO orders (order_id, sku, quantity)
VALUES (501, 'SKU-1001', 5);
COMMIT;

Consistency

Consistency means every committed transaction must leave the database in a valid state according to defined rules, constraints, and business logic. This protects against impossible states such as negative inventory, invalid foreign keys, or shipments linked to non-existent orders.

ALTER TABLE inventory
ADD CONSTRAINT chk_quantity_nonnegative
CHECK (quantity_available >= 0);

Isolation

Isolation means concurrent transactions should not interfere in a way that produces incorrect results. In high-volume systems, this is critical for preventing race conditions like two workers allocating the same stock at the same time.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE inventory
SET quantity_available = quantity_available - 1
WHERE sku = 'SKU-1001';
COMMIT;

Durability

Durability means once a transaction is committed, the result is permanently recorded even if the system crashes immediately afterward. This is essential for preserving confirmed orders, receipts, and inventory movements in operational systems.

BEGIN;
INSERT INTO shipment_events (shipment_id, status, event_time)
VALUES (9001, 'dispatched', NOW());
COMMIT;

Transaction Control in PostgreSQL

PostgreSQL enforces ACID through transactional statements such as BEGIN, COMMIT, and ROLLBACK, along with constraints, locks, and isolation levels. These mechanisms work together to maintain correctness under concurrent load.

BEGIN;
UPDATE orders
SET status = 'cancelled'
WHERE order_id = 501;
ROLLBACK;