Suppose two people try to buy the final ticket at the same time. Each application reads “one ticket available” before either records the sale. Putting the statements in a transaction is necessary, but the exact read and update behavior still determines whether both requests can claim success.
ACID describes four transaction properties: atomicity, consistency, isolation and durability. Isolation is the property most directly concerned with concurrent operations, and databases offer several isolation levels with different behavior and cost. This reference builds on the original transaction chapter and explains why transaction boundaries, constraints, update conditions and retry logic must work together.
The four ACID properties
Atomicity means a transaction's supported changes succeed together or are rolled back together. A transfer should not subtract money from one account without recording the corresponding credit. The guarantee depends on using transactional storage and keeping the related work inside the transaction boundary.
Consistency means the transaction preserves the database's declared rules and the application's required invariants. Constraints can enforce positive quantities, valid references and uniqueness. The database cannot infer every business rule, so application design must still express rules such as the correct total of a financial transfer.
Isolation controls how concurrent transactions interact. A transaction may see different snapshots or wait for another transaction depending on the engine and level. A higher level is not simply a switch that removes the need to understand the query's behavior.
Durability means committed changes survive according to the database's persistence guarantees and configuration. It is not a replacement for backups, replication planning or tested recovery. A committed accidental delete can be durable and still be something you need to recover from.
Read phenomena in plain language
A dirty read observes another transaction's uncommitted change. If that transaction rolls back, the observed value never becomes a committed fact. Read-uncommitted behavior varies by engine; PostgreSQL treats that requested level like read committed, while SQLite's special shared-cache circumstances differ from its usual behavior.
A nonrepeatable read occurs when rereading a row sees a committed change made by another transaction. A phantom concerns a repeated predicate query returning a changed set of matching rows, such as a new qualifying booking. These descriptions help explain isolation, but engine implementations do not always map neatly onto a simplified textbook table.
Read committed commonly gives statement-level views of committed data. Repeatable read can provide a stable transaction snapshot in some engines, but it does not guarantee every possible business invariant. Serializable aims for outcomes consistent with transactions running in some serial order, often by blocking or aborting conflicting work.
An abort at serializable isolation is a protection mechanism. The application should retry the complete logical transaction with an appropriate bounded policy, not continue halfway through using stale values from the aborted attempt.
Engine differences and transaction design
PostgreSQL, MySQL InnoDB, SQL Server and SQLite implement concurrency differently. Default levels, snapshot behavior, locking and supported commands vary. SQL Server's snapshot options depend on database configuration. MySQL's behavior also depends on the storage engine. Record the actual environment when investigating a concurrency problem.
Keep transactions short and focused. Waiting for a user response or calling a remote service while holding database locks can increase contention. A database rollback cannot unsend an email or undo a completed payment at another service. Coordinate external effects through a deliberate application workflow, such as an outbox, when reliability requires it.
Constraints remain useful at every isolation level. A unique reservation key can prevent duplicate logical requests, and an atomic conditional update can enforce a stock boundary. Isolation and constraints complement each other; choosing a stricter level does not remove the value of expressing the rules explicitly.
Check the PostgreSQL isolation documentation for its guarantees and retry requirements. Use the corresponding engine manual for a production database rather than assuming names alone establish identical behavior.
Practice: test a competing purchase
Open two database sessions against a test database. Give the event one available ticket. In each session, attempt the conditional update in a short transaction and record the affected-row result or conflict error. Observe waiting behavior and the final stock value.
The correct application must not confirm two purchases. It must treat zero affected rows as unavailable, handle a retryable conflict according to its policy, and confirm a successful purchase only after the required transaction commits. Add an order record in the same transaction in a fuller exercise.
Then test a repeated request from the same customer. Concurrency safety and request deduplication are separate requirements. A unique request token can prevent a retried network request from creating a second order after the first already committed.
Document what happens when the client loses the connection during commit. The outcome may be uncertain to the client even though the database has a definite state. Looking up the request token is safer than blindly charging or reserving again.
SQL ACID Properties and Transaction Isolation Examples
Each example shows the code, the output it produces and what each part does.
Update stock with a condition
Run this sample in a disposable database. The SQL uses a condition in the update so availability is checked as part of the modification rather than trusted from an earlier application read.
The application must inspect the update's affected-row count. One changed row means this update obtained a ticket. Zero changed rows means no matching available stock was changed. For SQL Server, use its BEGIN TRANSACTION spelling and appropriate driver result handling. Under some isolation settings concurrent conflicts can produce an error that requires a retry rather than a zero-row result.
CREATE TABLE ticket_stock (
event_id INTEGER PRIMARY KEY,
available INTEGER NOT NULL CHECK (available >= 0)
);
INSERT INTO ticket_stock VALUES (1, 1);
BEGIN;
UPDATE ticket_stock
SET available = available - 1
WHERE event_id = 1 AND available > 0;
COMMIT;
SELECT event_id, available FROM ticket_stock;event_id | available
1 | 0SQL ACID Properties and Transaction Isolation FAQ
Does a transaction automatically stop overselling?
No. A transaction groups statements, but two transactions can still both read the same stock level. Its statements must enforce availability under the engine's concurrency rules, for example with UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0 and a check of the affected row count.
Should every query use serializable isolation?
Choose guarantees from the operation's requirements, then account for waiting, retries and workload. A universal setting is not a substitute for design.
Does rollback undo everything the application did?
It undoes supported database changes in that transaction. External actions require their own coordination and recovery strategy.
What is a dirty read?
Reading another transaction's changes before that transaction has committed. If it then rolls back, you've used data that never officially existed. Only the Read Uncommitted level allows dirty reads, and PostgreSQL doesn't allow them at all.
What is the difference between Repeatable Read and Serializable?
Repeatable Read guarantees that rows you've read won't change if you read them again in the same transaction. Serializable goes further: the result must be the same as if the transactions had run one after another, which prevents subtle anomalies, at the cost of more waiting or retries.
SQL ACID Properties and Transaction Isolation Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
The conditional stock update affects zero rows. What should the application do?
SQL ACID Properties and Transaction Isolation Quiz
2 questions to check what you learned.
Which ACID property groups supported transaction changes into all-or-nothing work?
- Alphabetical order
- Atomicity
- Pagination
What should happen after a retryable serialization failure?
- Continue from the middle with values read before the failure
- Assume the database already confirmed the business operation
- Retry the complete logical transaction under a bounded retry policy