Technology & Computing · Data & digital information

How a database keeps records consistent

See how transactions, built-in rules and isolation levels keep records right when a server crashes, two buyers want one seat or two updates collide.

4 short lessons · about 17 minutes · 4 sources · updated November 2026

What you will learn

  • Explain atomicity with a transfer between two accounts.
  • Distinguish committing a transaction from rolling it back.
  • Choose a uniqueness constraint for a duplicate-record problem.
  • Distinguish structural consistency from real-world truth.

Lesson 1 of 4 · 4 min

Why a bank transfer can't stop halfway

Before you readA bank is moving $100 from Alice to Ben. A program totals every balance in the bank while the move is in progress. Which total does the database make sure it sees?

Correct.Not quite. The answer is The same total as before the move.

It sees the same total as before, because an open transaction's changes are hidden from other work. This lesson shows how a transfer stays all-or-nothing.

Say a bank's database is moving $100 from Alice to Ben. It subtracts $100 from Alice, then adds $100 to Ben. Now the server crashes between the two. Alice is $100 poorer, Ben is no richer, and the money is nowhere.

Databases prevent this with a transaction. It bundles several steps into one all-or-nothing operation. If anything stops the transaction from finishing, none of its steps affect the database at all.

That property is called atomicity. With the debit and the credit inside one transaction, only two outcomes are possible. Both changes happen, or neither does. A half-finished transfer isn't on the list.

Atomic describes the result. Inside, the computer still works step by step. The PostgreSQL manual's version of this transfer takes four separate updates, and all four succeed or fail together.

Atomicity has a second half. While a transfer is in progress, nobody else can see it. Say another program is adding up every balance in the bank at that moment. If it saw Alice's debit without Ben's credit, its total would come out $100 short.

So the database hides an open transaction's changes from all other work. When the transaction finishes, its changes become visible all at once.

Check yourselfA database moves $100 from Alice to Ben inside one transaction. The server crashes after subtracting from Alice and before adding to Ben. What do the accounts show after the restart?

Correct.Not quite. The answer is Both balances are what they were before the transfer began.

If a failure stops a transaction from completing, none of its steps take effect. So the debit is undone along with everything else. The database doesn't finish the job later, and it doesn't leave Alice short. The transfer hasn't happened, and it can be tried again.

PostgreSQL marks the boundary with three commands. BEGIN opens a transaction, and everything after it is a draft. COMMIT ends the transaction and makes the whole draft take effect. ROLLBACK ends it by throwing the whole draft away.

You'd roll back when something looks wrong partway through, such as Alice's balance going negative. A commit is also a promise. Before the database reports the transaction complete, it has logged the changes to permanent storage, so a crash a moment later can't lose them.

Try to recallWhat's the difference between committing a database transaction and rolling it back?

A commit ends the transaction and makes all of its changes take effect together. A rollback ends it by discarding all of them, so none take effect.

Lessons in this topic

  1. Why a bank transfer can't stop halfway

    See how a database transaction bundles several changes so that all of them take effect or none do, and what commit and rollback mean.

    Essentials · 4 min

  2. How a database stops two people booking one seat

    See how rules built into the database block double bookings and bad values, and why a record that passes every rule can still be wrong.

    Essentials · 4 min

  3. Why a database sometimes tells you to try again

    See what one transaction can see of another's work, and why the strictest setting sometimes cancels a transaction and expects a retry.

    Deeper · 4 min

  4. What deleting a row does to the rows that point at it

    Learn the choices a database offers when you delete a record that others refer to, and how to pick one by asking what the link means.

    Deeper · 5 min

Three quick stories from this topic

Rollbacks leave ID gaps

A canceled PostgreSQL transaction undoes its changes but keeps the ID numbers it took

Rolling back a transaction cancels its changes to the tables. A number it took from an automatic counter is not handed back, so order or invoice numbers can skip. Returning the number would make other transactions wait to learn the outcome.

Why the counter doesn't rewind

If a counter could rewind, every transaction asking for the next number would have to wait until the one before it had committed or failed. PostgreSQL chooses never to block them.

No such thing as gapless

A crash can use up numbers too. The PostgreSQL manual says plainly that its sequences can't be used to get a series with no gaps.

Why databases ask for retries

The strictest database setting can cancel a transaction that did nothing wrong

Under serializable isolation, PostgreSQL lets overlapping transactions commit only if some one-at-a-time order would give the same result. When no order would, it rolls one of them back and expects the application to run it again.

Concurrency creates the difficulty

Two operations can each read information while the other is still working. Together they may break a rule even when each looks reasonable on its own.

Retry the whole transaction

After a serialization failure, the application must be ready to retry with current information. Strong consistency can mean refusing one attempt instead of accepting an unsafe combination.

A blank passes the check

In PostgreSQL, a rule that a price must be above zero still accepts no price at all

A check constraint passes unless its test comes out false. Comparing an empty value with zero gives no answer either way, so the blank gets in. Keeping blanks out takes a second rule, called not-null.

True, false or unknown

A check constraint is satisfied when its condition is true or when it evaluates to null, the database's marker for a missing value. Most comparisons that involve a null come out null.

Why the manual wants not-null

The PostgreSQL manual's tip is that in most database designs the majority of columns should be marked not null.

Sources

This topic is part of What happens behind the screen, a guided path through related topics.

  • Why one database query is fast and the next isn't

    See what an index does for a database search, which searches it can't help, and how to read the plan that shows whether the database used it.

    4 lessons · 25 min

  • What a database is

    See how tables, rows and keys organize records, how one SQL question pulls out an answer, why a spreadsheet stops being enough and when other kinds of database fit better.

    4 lessons · 16 min

  • Data compression

    See how a zip file squeezes out repetition, how JPEG and MP3 throw away what you won't notice, why busy video breaks into blocks and why no method can shrink every file.

    4 lessons · 16 min

  • The parts of a computer

    Open up a laptop or a phone and name what you find. See what the processor, memory, storage and the input and output parts each do, and how they work together when you open a file.

    4 lessons · 16 min

  • Why a computer can't write down one tenth

    Trace 0.1 into binary and back, and see why adding it three times doesn't give 0.3, why rounding first doesn't help, and what to do instead.

    4 lessons · 20 min

  • HTTPS and how a website proves who it is

    See what the padlock in your browser promises, what a website's certificate says, who vouches for it, and how a site gets one automatically.

    4 lessons · 24 min

All technology & computing topics · Take the quiz