Transactions, ACID, and indexes

Imagine that Erik sends his friend $20 through a payment application.

Underneath the interface, the system needs to perform at least two changes:

1. Subtract $20 from Erik’s balance.
2. Add $20 to his friend’s balance.

These two writes represent one logical action: transferring money.

But what happens if the first change succeeds and the system fails before the second one is completed?

Erik loses $20, but his friend never receives it.

And the database would now contain an invalid halfway state, the kind of outcome database transactions are designed to prevent.

What is a transaction?

A transactionConcept · lights on your maptransactionA group of database operations treated as one logical unit: either the whole unit completes successfully, or it has no lasting effect. If every step succeeds, the database commits the transaction; the changes become official and persistent. If one step fails, it rolls back; the changes are undone and the database returns to the previous valid state. is a group of database operations treated as one logical unit, and it should either complete successfully as a whole or have no lasting effect.

For the transfer, the database might conceptually perform:

If both balance updates succeed, the database commits the transaction.

A commit means: make all of these changes official and persistent.

If one step fails, the database rolls back the transaction.

A rollback means: undo the transaction’s changes and return to the previous valid state.

The central guarantee is: either all required changes happen, or none of them do.

Why separate writes are dangerous

Suppose the backend performs the changes independently:

If the database, application, or machine fails between the writes, only the first change may remain.

Databases can perform multiple writes; the problem is that these two writes were not protected as one transaction.

When they are placed inside the same transaction, the database does not expose the transfer as successfully completed until the complete unit can be committed.

A simplified SQL transaction

A transfer might resemble:

BEGIN starts the transaction.

The two UPDATE statements change the account balances.

COMMIT makes the complete set of changes official.

If something fails before the commit, the system can issue ROLLBACK;

The precise syntax varies between database systems, and real financial applications require additional validation and accounting records.

The important structure is: begin → perform related work → commit or roll back.

Validate before transferring

A transaction does not automatically prove that the transfer is allowed.

The application still needs business logic that checks questions such as:

• Is Erik authenticated?
• Is he authorized to use this account?
• Does the account have sufficient funds?
• Is the recipient valid?
• Is the amount positive?
• Has this transfer already been processed?
• Does the transfer exceed any limits?

The transaction protects the database changes as one unit.

But business logic determines whether those changes should be attempted in the first place.

Transactions and validation solve different problems.

What is ACID?

The formal name for a widely used family of transaction guarantees is ACIDRecognition — just know it existsACIDAtomicity, Consistency, Isolation, Durability. The formal name for a widely used family of transaction guarantees. Recognition level: when engineers say “this operation needs ACID guarantees,” they mean the stored data requires strong protections around multi-step changes, concurrent activity, rules, and failures..

ACID stands for:

• Atomicity
• Consistency
• Isolation
• Durability

When engineers say “This operation needs ACID guarantees,” they mean the stored data requires strong protections around multi-step changes, concurrent activity, rules, and failures.

Atomicity

Atomicity is the all-or-nothing guarantee. A transaction is treated as one indivisible unit.

For the transfer:

• Both balance changes commit.
• Or neither balance change commits.

There should be no permanently completed halfway transfer.

This is the idea most directly captured by the transaction example.

Consistency

Consistency means a transaction should move the database from one valid state to another valid state while preserving the rules the database enforces.

Suppose the database has constraints stating that:

• Every transfer must reference real accounts.
• Every transaction ID must be unique.
• Required values cannot be missing.

The database checks those rules when processing the transaction.

In ACID, consistency refers primarily to preserving defined database rules and invariants, not to every piece of cached or distributed data everywhere in the product being instantly identical.

That is a different use of the word “consistency,” which you will encounter later.

Isolation

Isolation governs what happens when several transactions run at the same time. Imagine that Erik has exactly $20 and attempts to send $20 to two different friends at nearly the same moment.

Both requests could initially read the same balance and conclude that enough money is available.

The database needs to coordinate those overlapping transactions so that they do not both spend the same funds.

Isolation helps concurrent transactions behave as though they were safely ordered, depending on the isolation level the database uses, and this same idea helps prevent two customers from successfully purchasing the final unit of inventory or the same airline seat.

Atomicity prevents halfway changes. Isolation protects overlapping changes from interfering incorrectly.

Durability

Durability means that once the database confirms a committed transaction, the data should survive failures the database is designed to tolerate.

If the transfer is committed and the database process restarts immediately afterward, the completed transfer should still exist.

Database systems use mechanisms such as persistent storage, logs, and replication to provide durability.

No system can survive every imaginable disaster without appropriate backups and infrastructure, but the practical promise is that an acknowledged commit is not supposed to disappear merely because the process stops or a machine restarts.

ACID as one complete promise

Return to Erik’s transfer.

Atomicity ensures that both balance changes happen together.

Consistency preserves the database’s defined rules.

Isolation protects the transfer from conflicting concurrent activity.

Durability preserves the transfer after it has been committed.

Together, these guarantees make transactions dependable enough for data involving:

• Money
• Inventory
• Reservations
• Account permissions
• Orders
• Financial records
• Other critical business state

Many relational databases provide strong transaction support, and some NoSQL databases do too, although the exact capabilities and tradeoffs vary.

ACID is not exclusive to one database family.

Transactions do not eliminate every failure

A transaction protects the database work performed inside it, but it does not automatically solve every part of a larger workflow.

Suppose the coffee backend:

1. Charges a card using an external payment API.
2. Stores the order in its own database.
3. Sends a confirmation email.

A database transaction cannot directly roll back work already completed inside another company’s payment system.

Coordinating changes across several systems is more difficult than coordinating changes inside one database, so the application may need idempotency, retries, webhooks, status fields, or other patterns from Module 6.

A useful boundary is: a database transaction reliably groups work performed within the transaction’s database boundary.

The second challenge: finding data quickly

Transactions help keep data correct.

But a database also needs to retrieve information quickly.

Imagine that the customers table contains 100 million rows and the application asks:

Without a useful supporting structure, the database may need to examine a large portion of the table until it finds the matching row.

Checking rows one by one is called a table scan or full table scan when the entire table is examined.

For small tables, this may be acceptable.

For large tables and frequent queries, it can become slow and expensive.

This is where indexes help.

What is an index?

A database indexConcept · lights on your mapindexAn additional data structure that helps the database locate rows more efficiently, like the index at the back of a book: look up the word and jump to the relevant pages instead of reading the entire book. Indexes can make important queries much faster, but they consume storage and add work to inserts, updates, and deletes. is an additional data structure that helps the database locate rows more efficiently.

A useful analogy is the index at the back of a book. Without it, finding every page discussing “authentication” might require reading the entire book, but with it you look up the word and jump to the relevant pages.

A database index does the same kind of job.

An index on the email column helps the database locate the row associated with erik@post.com without necessarily examining every customer row.

Creating an index

A simplified SQL statement might look like:

This asks the database to create an index based on the email column.

Later, when the database receives:

its query planner can consider using the index to locate the matching row, and the database decides whether the index is actually useful for the query.

Indexes are fast, not magical

An index can make a query dramatically faster, but it does not make performance completely independent of database size.

Many common relational indexes use tree-like structures that narrow the search step by step. Others use different structures designed for different query patterns.

Performance still depends on factors such as:

• The number of rows
• The type of index
• How many rows match the condition
• Whether the needed data is already in memory
• The columns requested
• The database’s workload
• The physical storage
• The way the query is written

A better mental model is: an index helps the database avoid searching irrelevant rows.

It often turns a large search into a much smaller one.

Indexes must match the query

But an index is useful only when it supports the way the data is queried.

An index on email may help WHERE email = 'erik@post.com'

But it may not help a query filtering only by WHERE status = 'confirmed'

That query may need an index involving status.

Teams choose indexes by examining real access patterns:

• Which columns are frequently searched?
• Which columns are used in joins?
• Which results need sorting?
• Which queries are slow?
• How selective is the column?
• How often is the table written?

The goal is not to index everything but to support important queries efficiently.

Composite indexes

An index can contain more than one column.

Suppose the application frequently asks:

The team might create a composite index involving both columns:

The order of columns inside a composite index can matter.

You do not need to design these indexes yet. Recognize that indexes can be tailored to common combinations of filters and sorting requirements.

The cost of an index

Indexes are not free.

The database must store the index in addition to the original table data, and when a row is inserted, updated, or deleted, it may also need to update every affected index.

For example, inserting a new customer may require:

1. Writing the customer row.
2. Updating the email index.
3. Updating any name index.
4. Updating any other relevant indexes.

The table now supports faster reads, but each write has more work to perform. The writes pay for the reads.

Indexes therefore create a tradeoff: faster supported reads in exchange for additional storage and write work.

Too many indexes

If a table has many indexes, reads may benefit, but writes can become slower and more expensive because each write now has more index structures to maintain.

Indexes also consume disk space and memory, and unused indexes create maintenance cost without providing meaningful value.

Teams therefore monitor:

• Which queries are slow
• Which indexes are used
• Which writes are becoming expensive
• Whether query patterns have changed
• Whether an index should be added, changed, or removed

A common engineering question is: “Is there an index supporting this query?”

That question asks whether the database has an efficient route to the requested rows.

Unique indexes and constraints

Indexes can also help enforce uniqueness.

Suppose customer emails must be unique.

The database might use a unique constraint or unique index:

Now the database rejects a second customer row using the same email value.

This provides two benefits:

• The database can find customers by email efficiently.
• The database enforces the rule that two customers cannot share that email.

Indexes can therefore support both performance and integrity.

When the database ignores an index

The existence of an index does not mean the database will always use it.

If a query returns most of the rows in a table, reading the table directly may be more efficient than repeatedly following the index. For example, if nearly every order has status = 'confirmed', an index on status may not narrow the search very much.

The database’s query planner estimates different approaches and chooses an execution plan.

This is why slow-query investigation involves more than asking whether any index exists. Engineers also examine whether the index fits the query and whether the planner believes it is useful.

Transactions and indexes solve different problems

Transactions and indexes are both core database capabilities, but they serve different purposes.

A transaction protects the correctness of related changes.

An index improves the efficiency of locating data.

For Erik’s payment transfer:

• The transaction ensures that both account changes succeed together.
• Indexes help the database quickly locate Erik’s account and the recipient’s account.

One protects correctness while the other improves performance, and Erik’s transfer needs both.

Seeing the complete transfer

Imagine that Erik sends $20 to his friend.

The backend authenticates Erik, validates the amount, and begins a transaction, inside which the database uses indexes to locate both account rows efficiently.

It subtracts 2,000 cents from Erik’s balance and adds 2,000 cents to the recipient’s balance.

If both writes succeed and all constraints remain valid, the database commits the transaction, but if a write fails, it rolls back and neither balance change becomes official.

The complete flow combines several ideas:

• Keys identify the account rows.
• Indexes help locate them.
• Business logic determines whether the transfer is allowed.
• A transaction groups the writes.
• ACID guarantees protect the transaction.
• The commit makes the new state authoritative.

The mental model to remember

A transaction groups database operations into one logical unit.

A commit makes the transaction’s changes official.

A rollback removes the transaction’s incomplete changes.

ACID describes four important transaction properties:

• Atomicity: all or nothing
• Consistency: preserve defined rules
• Isolation: coordinate concurrent transactions safely
• Durability: preserve committed changes

An index is an additional data structure that helps the database locate rows more efficiently.

Indexes can make important queries much faster, but they consume storage and add work to inserts, updates, and deletes.

Indexes should be designed around real query patterns rather than added to every column.

You should now understand how databases protect multi-step changes and retrieve information efficiently even as tables become large.