ACID transaction

ACID transactions are a set of four key properties—Atomicity, Consistency, Isolation, and Durability—that guarantee safe and reliable data processing in a database.

Note:

The rule, more precisely stated: it’s not “ACID → always Postgres,” it’s “does my access pattern require coordinating multiple related writes atomically, with strong consistency, across potentially multiple entities?” If yes → a relational DB with ACID transactions (Postgres, MySQL) is the natural fit. DynamoDB can do transactions, but you reach for it when your dominant need is horizontal scale on simple, single-item/single-partition access patterns — and you accept its transaction feature as a secondary tool, not your primary reason for choosing it.

Interview lines
”For the booking flow, my access pattern requires atomically checking ticket availability and creating the booking record as one unit — if either step fails, neither should persist. That’s a strong consistency requirement with multi-step coordination, so I’d reach for Postgres and its native ACID transactions rather than DynamoDB, since DynamoDB’s transaction support is more constrained and its core strength — horizontal scale via single-item access — isn’t actually what this flow needs.”

Eg:

BEGIN Check ticket status = ‘available’

UPDATE ticket SET status = ‘booked’ INSERT INTO bookings (userId, ticketIds, totalPrice, status=‘confirmed’)

COMMIT