§ 1.7.1section 8 of 25·~10 min
OLTP: transactions and ACID
An OLTP system processes a large number of small, routine transactions as they happen. A transaction is one business operation that reads or changes a few rows: transfer money, book a flight seat, place an order, update an address, record an ATM withdrawal, decrement stock after a sale.
The goal: run each transaction quickly and correctly while many users use the system at the same time.
One order, several tables
Asha buys a ₹2,000 pair of shoes. Behind the Pay button:
- Check her account and the order details.
- Check the shoes are in stock.
- Insert the order and its line items.
- Reserve the stock.
- Record the payment result.
- Confirm the order to Asha.
That touches several tables, while thousands of other customers do the same. If one pair is left and two people tap Pay together, only one of them may get it. Making that true is the database's job.
What OLTP systems are built for
- Low latency: each transaction finishes in milliseconds to a few seconds.
- High throughput: hundreds or thousands of transactions a second, depending on hardware, schema and how much each transaction does.
- Many concurrent users reading and writing the same tables. Locks, isolation levels and MVCC (multi-version concurrency control) keep them out of each other's way.
- Current, detailed data: today's balance, this order's status, the stock right now.
- Strong guarantees: money and stock must stay right even when a server crashes. That's ACID, below.
Most requests mix the four basic operations, each aimed at a small, well-defined set of rows:
| Operation | Example SQL | Purpose |
|---|---|---|
| Create | INSERT INTO customers ... | Register a new customer |
| Read | SELECT ... WHERE customer_id = 101 | Show a customer's details |
| Update | UPDATE customers SET ... WHERE customer_id = 101 | Change an address |