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.

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
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.
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
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.
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.
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.
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.
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
- PostgreSQL 18: Transactions, PostgreSQL Global Development Group
- PostgreSQL 18: Transaction isolation, PostgreSQL Global Development Group
- PostgreSQL 18 Documentation: 9.17. Sequence Manipulation Functions, The PostgreSQL Global Development Group
- PostgreSQL 18: Constraints, PostgreSQL Global Development Group




