uppose a database contains the following bank accounts.
Account IDOwnerBalance
| A-100 | Isaac | 1,000,000 |
| B-200 | Sophie | 500,000 |
To transfer 100,000 from Isaac to Sophie, the system must perform at least two changes:
Subtract 100,000 from account A-100
Add 100,000 to account B-200
A simplified SQL implementation might look like this:
UPDATE accounts
SET balance = balance - 100000
WHERE account_id = 'A-100';
UPDATE accounts
SET balance = balance + 100000
WHERE account_id = 'B-200';
What happens if the first UPDATE succeeds but the server stops before the second statement runs?
The money has left Isaac’s account, but it has not reached Sophie’s account.
There is another problem. What happens when two users read and update the same account at nearly the same time? Both may calculate a new balance from the same old value, and one update may overwrite the other.
A database does not normally process one request at a time. Web requests, background jobs, administrators, integrations, and batch programs may all read and modify the same data concurrently.
Concurrency improves throughput, but it also introduces risks:
- only part of a business operation may be saved
- uncommitted data may become visible
- one update may overwrite another
- the same query may return different results inside one operation
- transactions may wait for one another indefinitely
- aggregated results may combine data from inconsistent points in time
Transactions and concurrency control exist to manage these risks.
A transaction groups several database operations into one logical unit of work.
Concurrency control coordinates overlapping transactions so that they still produce correct results.
The two ideas are inseparable.
A transaction defines the boundary of a business operation. Concurrency control determines how those boundaries may overlap without corrupting the database.
A transaction is a logical unit of work
A transaction is a sequence of database operations that moves the database from one valid state to another.
Consider the transfer again.
1. Check the source account balance
2. Subtract the transfer amount
3. Add the amount to the destination account
4. Record the transfer history
These may be separate SQL statements, but they represent one business operation.
Either all of them should succeed, or none of them should remain.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 100000
WHERE account_id = 'A-100'
AND balance >= 100000;
UPDATE accounts
SET balance = balance + 100000
WHERE account_id = 'B-200';
INSERT INTO transfer_history (
from_account_id,
to_account_id,
amount
) VALUES (
'A-100',
'B-200',
100000
);
COMMIT;
If an error occurs before completion, the changes should be undone.
ROLLBACK;
Using a transaction means treating several SQL statements as one success-or-failure unit.
Every required operation succeeds
→ COMMIT
At least one required operation fails
→ ROLLBACK
This boundary is defined by business meaning, not merely by the number of SQL statements.
COMMIT, ROLLBACK, and SAVEPOINT
COMMIT
COMMIT confirms the changes made by the current transaction.
COMMIT;
After a successful commit, the transaction’s result becomes durable and can become visible to other transactions according to the database’s isolation rules.
A committed result should survive a database restart or system failure.
ROLLBACK
ROLLBACK cancels the changes made by the transaction.
ROLLBACK;
A rollback may be triggered by:
- application errors
- constraint violations
- deadlocks
- serialization failures
- timeouts
- explicit business validation failures
The database returns the affected data to the state that existed before the transaction began.
SAVEPOINT
A savepoint marks a position inside a transaction.
START TRANSACTION;
UPDATE orders
SET status = 'PAID'
WHERE order_id = 1001;
SAVEPOINT payment_completed;
INSERT INTO reward_history (
order_id,
point
) VALUES (
1001,
500
);
ROLLBACK TO payment_completed;
COMMIT;
ROLLBACK TO cancels work performed after the savepoint without ending the whole transaction.
Savepoints are technically useful, but they should not be confused with a sound business model.
The database may allow a partial rollback, but the business operation may not allow partial success. A payment marked as complete while its required accounting record has been discarded may still be an invalid state.
Technical possibility does not automatically imply business correctness.
ACID describes four essential transaction properties
Transaction behavior is commonly summarized by the acronym ACID:
A: Atomicity
C: Consistency
I: Isolation
D: Durability
The four properties are often taught separately, but in practice they work together.
Atomicity: all operations succeed, or none remain
Atomicity means that a transaction is treated as an indivisible unit.
The transfer must not leave the database with only the withdrawal completed.
Withdrawal succeeds + deposit succeeds
→ Transfer succeeds
Withdrawal succeeds + deposit fails
→ Withdrawal is undone
Withdrawal fails
→ The remaining transfer operations do not complete
Atomicity is often summarized as:
All or nothing.
When a transaction fails, the database needs enough information to reverse its incomplete changes.
This is associated with UNDO information.
Previous value
→ Used to restore the database after an incomplete transaction
Atomicity prevents intermediate failure from becoming a permanent partial result.
Consistency: defined rules remain valid
Consistency means that a transaction takes the database from one valid state to another valid state.
A system may define rules such as:
An account balance must not be negative.
An order must reference an existing customer.
An order quantity must be greater than zero.
An email address must be unique.
A completed shipment cannot return to the CREATED state.
Some of these rules can be protected directly by database constraints.
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
balance BIGINT NOT NULL,
CONSTRAINT chk_account_balance
CHECK (balance >= 0)
);
Referential integrity can be protected with a foreign key.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
The database, however, does not automatically understand every business rule.
Rules such as these may require application logic or additional database structures:
A customer may use a promotional coupon at most three times per day.
A shipped order cannot be canceled without a return process.
An approval above a defined amount requires a senior approver.
ACID consistency does not mean that the database independently knows what is correct for the business.
It means that the transaction, constraints, and application rules must be designed so that invalid states are not accepted.
Isolation: concurrent transactions should not interfere incorrectly
Isolation means that concurrent transactions should behave as though they are executing independently.
Suppose two transactions withdraw money from the same account.
Transaction T1
Withdraw 100,000
Transaction T2
Withdraw 50,000
Both transactions read the current balance of 1,000,000.
They calculate:
T1 result: 900,000
T2 result: 950,000
T1 stores 900,000. T2 then stores 950,000.
The final balance becomes 950,000, even though a total of 150,000 should have been withdrawn. The correct balance is 850,000.
T1’s update has disappeared.
Isolation is implemented through mechanisms such as:
- locking
- timestamps
- validation
- serialization checks
- multiversion concurrency control
Stronger isolation improves safety but may reduce concurrency. Weaker isolation may improve throughput but allow more anomalies.
For this reason, database systems provide multiple isolation levels rather than one universal setting.
Durability: committed results survive failure
Durability means that once a transaction has committed, its result must survive system failure.
Suppose the application returns “Payment completed” and the database server immediately loses power.
After the server restarts, the payment must still exist. Otherwise, a commit would not be trustworthy.
Database engines commonly record changes in a transaction log before the modified data pages are permanently written.
This principle is known as Write-Ahead Logging, or WAL.
1. Record the change in the transaction log
2. Persist the required log records
3. Confirm the commit
4. Write the corresponding data pages later if necessary
After a crash, committed changes that were not fully written to the data files can be restored from the log.
This is associated with REDO information.
UNDO
Reverse changes from incomplete transactions
REDO
Reapply committed changes that were not fully written to data pages
Atomicity and durability are therefore closely related to database recovery.
Why concurrent execution is necessary
The simplest way to avoid concurrency problems would be to execute every transaction serially.
Run all of T1
→ Run all of T2
→ Run all of T3
That approach would waste resources.
While one transaction waits for disk I/O, network communication, or another internal operation, the database can use the available CPU and storage capacity to process other work.
Concurrent execution improves:
- system throughput
- resource utilization
- response time
- support for many users
- tolerance of long-running operations
Concurrency itself is not the problem.
Uncontrolled ordering between conflicting operations is the problem.
The goal of concurrency control is to allow as much overlapping work as possible while producing a result that remains logically correct.
A schedule describes the order of transaction operations
When several transactions run concurrently, their read and write operations may be interleaved.
The resulting execution order is called a schedule.
Consider two transactions:
T1
R1(X)
X = X - 100
W1(X)
T2
R2(X)
X = X + 50
W2(X)
The notation means:
R1(X)
Transaction T1 reads X
W1(X)
Transaction T1 writes X
Serial schedules
A serial schedule completes one transaction before starting the next.
R1(X)
W1(X)
COMMIT T1
R2(X)
W2(X)
COMMIT T2
Serial schedules are easier to reason about because their operations do not overlap.
Their limitation is reduced concurrency.
Non-serial schedules
A non-serial schedule interleaves operations from several transactions.
R1(X)
R2(X)
W1(X)
W2(X)
This may improve throughput, but it can also produce incorrect results.
Not every non-serial schedule is invalid.
If the interleaved execution produces the same effect as some correct serial execution, the schedule can still be considered safe.
Serializability
A non-serial schedule is serializable when its effect is equivalent to a serial schedule.
Actual execution
Operations from T1 and T2 are interleaved
Observable result
Equivalent to running all of T1 before T2,
or all of T2 before T1
Serializability is a central correctness criterion in concurrency control.
Transactions may run concurrently internally, but their result should behave like a valid serial ordering.
Conflicting operations
Two operations from different transactions conflict when:
- they access the same data item, and
- at least one of them is a write.
The following operations do not conflict:
R1(X), R2(X)
Both transactions only read X.
The following pairs conflict:
R1(X), W2(X)
W1(X), R2(X)
W1(X), W2(X)
Changing the order of a conflicting pair may change either the value observed or the final stored result.
Precedence graphs and conflict serializability
A precedence graph, also called a serialization graph, can be used to test conflict serializability.
The process is:
- Create one node for each transaction.
- For each conflicting pair, draw an edge from the transaction whose operation occurs first to the transaction whose operation occurs later.
- Check the graph for a cycle.
If the graph is acyclic, the schedule is conflict-serializable.
If the graph contains a cycle, the schedule is not conflict-serializable.
For example:
W1(X) occurs before R2(X)
→ T1 → T2
W2(Y) occurs before R1(Y)
→ T2 → T1
This creates a cycle:
T1 → T2 → T1
No valid serial ordering can satisfy both directions.
Precedence graph questions are common in database theory and certification exams because they connect individual read-write conflicts to the broader idea of serializability.
Common anomalies caused by concurrent transactions
Without adequate concurrency control, overlapping transactions can create several well-known anomalies.
Lost update
A lost update occurs when one transaction overwrites the result of another.
Assume an initial balance of 1,000,000.
T1: Withdraw 100,000
T2: Withdraw 50,000
The execution is:
T1 reads 1,000,000
T2 reads 1,000,000
T1 writes 900,000
T2 writes 950,000
The final value is 950,000.
T1’s update has been lost.
This often occurs when an application reads a value, calculates a new absolute value, and then writes it back.
SELECT balance
FROM accounts
WHERE account_id = 'A-100';
-- Calculate a new value in application code
UPDATE accounts
SET balance = 950000
WHERE account_id = 'A-100';
When possible, the operation can be expressed atomically in SQL.
UPDATE accounts
SET balance = balance - 50000
WHERE account_id = 'A-100'
AND balance >= 50000;
More complex operations may still require explicit locks, version checks, or stronger transaction isolation.
Dirty read
A dirty read occurs when one transaction reads a change that another transaction has not committed.
T1 changes the balance from 1,000,000 to 900,000
T2 reads 900,000
T1 encounters an error and rolls back
The value read by T2 never became a valid committed value.
Value observed by T2
→ Temporary, uncommitted state created by T1
If T2 uses that value to make further decisions, the incorrect state may spread to additional data.
Cascading rollback
Suppose T2 reads an uncommitted value written by T1.
T1 changes X
T2 reads the changed X and updates Y
T1 rolls back
T2’s work was based on a value that no longer exists. T2 may also need to be rolled back.
This chain reaction is called a cascading rollback.
Strict locking protocols prevent other transactions from reading uncommitted writes, reducing the possibility of cascading rollbacks.
Non-repeatable read
A non-repeatable read occurs when a transaction reads the same row twice and gets different committed values.
T1 reads the customer tier → SILVER
T2 changes the tier to GOLD
T2 commits
T1 reads the customer tier again → GOLD
The same row produced a different result within T1.
The difference from a dirty read is that T2’s change was committed.
Dirty read
Reads an uncommitted value
Non-repeatable read
Reads a different committed value on the second access
Phantom read
A phantom read occurs when the set of rows matching a condition changes during a transaction.
T1 executes:
SELECT *
FROM orders
WHERE total_amount >= 100000;
The query returns ten rows.
T2 inserts another matching order and commits.
INSERT INTO orders (
order_id,
total_amount
) VALUES (
2001,
150000
);
T1 runs the same query again and receives eleven rows.
No previously read row necessarily changed. A new qualifying row appeared.
Non-repeatable read
The value of an existing row changes
Phantom read
The set of rows matching a condition changes
Preventing phantoms may require protecting an index range or predicate, not merely locking rows that already exist.
Inconsistent analysis
Suppose T1 calculates the total balance across multiple accounts while T2 transfers money between those accounts.
A valid transfer should not change the combined balance.
The interleaving might be:
T1 reads account A before the transfer
T2 subtracts money from A
T2 adds money to B
T2 commits
T1 reads account B after the transfer
T1 may count the transferred amount twice.
The calculated total reflects no single real point in time.
This problem is known as inconsistent analysis.
A consistent snapshot or an appropriate isolation mechanism is needed when several rows must be analyzed as one coherent view.
Locks coordinate access to data
Locking is a concurrency-control mechanism that grants transactions controlled access to data.
The two basic lock modes are shared and exclusive locks.
Shared locks
A shared lock, or S lock, is associated with reading.
Several transactions may hold shared locks on the same data item.
T1 acquires an S lock
T2 may also acquire an S lock
Multiple readers can proceed together because they are not changing the value.
Exclusive locks
An exclusive lock, or X lock, is associated with modification.
When a transaction holds an exclusive lock, other transactions generally cannot acquire either a shared or exclusive lock on the same item.
T1 acquires an X lock
T2 requests an S lock
→ waits
T3 requests an X lock
→ waits
Basic lock compatibility
Existing lockRequested S lockRequested X lock
| S lock | Allowed | Wait |
| X lock | Wait | Wait |
Several readers are compatible.
A writer conflicts with both readers and other writers.
Real database engines may also use:
- intention locks
- update locks
- gap locks
- key-range locks
- predicate locks
- metadata locks
The exact lock set depends on the database product and access pattern.
Lock granularity
Lock granularity is the amount of data protected by a lock.
Possible levels include:
- database
- table
- page
- row
- index key
- key range
A coarse-grained lock covers more data.
Table-level lock
→ Fewer locks to manage
→ Lower lock-management overhead
→ Less concurrency
A fine-grained lock covers less data.
Row-level lock
→ More concurrent access
→ More locks to manage
→ Higher lock-management overhead
The trade-off can be summarized as:
Larger lock granularity
Lower management cost
Lower concurrency
Smaller lock granularity
Higher management cost
Higher concurrency
Some systems may perform lock escalation, replacing many fine-grained locks with a larger lock to reduce management overhead.
Two-phase locking controls when locks may be acquired and released
Two-Phase Locking, or 2PL, divides lock activity into two phases.
Growing phase
The transaction may acquire locks but may not release them.
Acquire an S lock
Acquire an X lock
Acquire another lock
Shrinking phase
The transaction may release locks but may not acquire new ones.
Release a lock
Release another lock
Once a transaction begins releasing locks, it cannot return to the growing phase.
Growing phase
Continue acquiring locks
Lock point
Shrinking phase
Continue releasing locks
Two-phase locking guarantees conflict serializability.
Basic 2PL, however, does not automatically prevent cascading rollbacks, and it can still create deadlocks.
Strict two-phase locking
Under Strict 2PL, exclusive locks are held until the transaction commits or rolls back.
Acquire X lock
Modify the data
COMMIT or ROLLBACK
Release X lock
Other transactions cannot observe or overwrite the uncommitted changes.
Strict 2PL helps prevent:
- dirty reads
- dirty writes
- cascading rollbacks
It is an important practical locking discipline used by many transaction-processing systems.
Deadlock occurs when transactions wait for one another
Consider two transfers that need the same account rows in different orders.
T1
Lock account A
Request a lock on account B
T2
Lock account B
Request a lock on account A
The execution becomes:
T1 acquires an X lock on A
T2 acquires an X lock on B
T1 requests an X lock on B
→ waits for T2
T2 requests an X lock on A
→ waits for T1
Neither transaction can continue.
This is a deadlock.
Four necessary conditions for deadlock
Four conditions are commonly associated with deadlock.
Mutual exclusion
A resource can be used by only one transaction at a time.
Hold and wait
A transaction holds one resource while waiting for another.
No preemption
A resource cannot simply be taken away from the transaction that holds it.
Circular wait
Transactions form a waiting cycle.
T1 waits for T2
T2 waits for T3
T3 waits for T1
Preventing at least one of these conditions prevents the deadlock from forming.
Deadlock handling strategies
Prevention
Deadlock prevention changes resource-acquisition rules so that one of the necessary conditions cannot hold.
A common application-level technique is to acquire locks in a consistent order.
Always lock the lower account ID first
Every transaction needing accounts A and B locks A before B.
Consistent ordering reduces the possibility of circular wait.
Avoidance
Deadlock avoidance grants resources only when the resulting state remains safe.
The Banker’s algorithm is a well-known operating-system example.
In transactional databases, however, it is often difficult to know every future lock requirement in advance, so avoidance is less commonly applied in that exact form.
Detection and recovery
The database may allow deadlocks to occur and detect them afterward.
A wait-for graph represents dependencies.
T1 → T2
T1 is waiting for T2
A cycle indicates a deadlock.
The database chooses a victim transaction, rolls it back, and releases its locks.
T1 and T2 are deadlocked
Choose T2 as the victim
ROLLBACK T2
Release T2's locks
Allow T1 to continue
Victim selection may consider:
- amount of work already performed
- number of locks held
- estimated rollback cost
- transaction age
- priority
- number of previous restarts
Timeout
A simpler strategy fails a transaction if it waits too long.
Timeouts are easy to implement but cannot reliably distinguish a true deadlock from a legitimate long wait.
Isolation levels balance consistency and concurrency
Running every transaction in fully serial order would prevent many anomalies, but it would also reduce throughput.
Database systems therefore provide isolation levels that let applications choose an appropriate balance.
The standard isolation levels are:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
READ UNCOMMITTED
This is the weakest standard isolation level.
A transaction may read changes that another transaction has not committed.
Possible anomalies include:
- dirty reads
- non-repeatable reads
- phantom reads
It may provide high concurrency, but the observed data may not represent a valid committed state.
For that reason, its use in business systems is generally limited.
READ COMMITTED
At this level, a transaction reads only committed data.
Dirty reads are prevented.
A row may still change between two reads inside the same transaction if another transaction commits an update.
Possible anomalies include:
- non-repeatable reads
- phantom reads
Many database systems use READ COMMITTED as a default isolation level.
REPEATABLE READ
At this level, a row read by a transaction should continue to appear with the same value when read again.
It prevents:
- dirty reads
- non-repeatable reads
In the standard conceptual table, phantom reads may still occur.
Actual database behavior may be stronger. Some engines use MVCC snapshots or range-locking techniques that also prevent certain phantom scenarios.
The product’s implementation matters.
SERIALIZABLE
SERIALIZABLE is the strongest standard isolation level.
Concurrent transactions must behave as though they were executed in a valid serial order.
It aims to prevent:
- dirty reads
- non-repeatable reads
- phantom reads
- broader serialization anomalies
The cost may include:
- more blocking
- more transaction aborts
- more retries
- lower throughput
- greater deadlock risk in lock-based implementations
Some databases implement serializable behavior through locks. Others detect non-serializable execution and abort one of the transactions.
Standard isolation-level comparison
Isolation levelDirty readNon-repeatable readPhantom read
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible |
| SERIALIZABLE | Prevented | Prevented | Prevented |
This table is important for conceptual understanding and certification exams.
It should not be treated as a complete description of every database engine.
Products differ in:
- snapshot semantics
- lock duration
- range locking
- write-conflict behavior
- treatment of read-only transactions
- handling of serialization anomalies
The isolation-level name alone is not enough to predict every product-specific result.
MVCC maintains multiple versions of data
Lock-based concurrency can cause readers and writers to block one another.
Multiversion Concurrency Control, or MVCC, reduces some of this blocking by keeping multiple versions of a row.
Suppose T1 began when the account balance was 1,000,000.
T2 later changes the balance to 900,000 and commits.
Older version: 1,000,000
Newer version: 900,000
T1
Continues reading the version visible to its snapshot
T2
Creates and commits the newer version
Depending on its isolation level and snapshot rules, T1 may continue seeing the older committed version.
Common benefits of MVCC include:
- readers block writers less often
- writers block readers less often
- queries can observe a consistent snapshot
- read-heavy workloads gain more concurrency
MVCC does not remove every conflict.
Two transactions updating the same logical row still create a write-write conflict. The engine must block, reject, or serialize those writes.
Long-running transactions may also keep old versions alive, increasing:
- storage consumption
- cleanup cost
- vacuum or purge pressure
- transaction metadata growth
MVCC
Reduces reader-writer interference
But
Does not eliminate writer-writer conflicts
Pessimistic concurrency control assumes conflicts are likely
Pessimistic concurrency control acquires locks before the protected modification.
SELECT account_id, balance
FROM accounts
WHERE account_id = 'A-100'
FOR UPDATE;
FOR UPDATE indicates that the selected row is being read for modification.
Another transaction attempting to update the same row may have to wait.
T1
Locks row A-100
Checks the balance
Updates the balance
Commits
Releases the lock
T2
Waits until T1 releases the row
Pessimistic locking can be suitable when:
- contention is frequent
- overselling or over-withdrawal is unacceptable
- retrying after a conflict is expensive
- the transaction is short
- the protected data set is well defined
Its costs include:
- lock waits
- lower throughput
- possible deadlocks
- long blocking chains
- greater sensitivity to slow transactions
Optimistic concurrency control assumes conflicts are uncommon
Optimistic concurrency control does not hold a long-lived lock while the user or application works.
Instead, it checks for a conflict when saving.
A version column is commonly used.
Account IDBalanceVersion
| A-100 | 1,000,000 | 7 |
T1 reads version 7.
When it updates, it includes the original version in the condition.
UPDATE accounts
SET balance = 900000,
version = version + 1
WHERE account_id = 'A-100'
AND version = 7;
If another transaction has already updated the row, the stored version is no longer 7.
The statement affects zero rows.
Affected rows = 1
→ Update succeeded
Affected rows = 0
→ A concurrent modification was detected
The application may then:
- notify the user
- reload the data
- merge changes
- retry the operation
- reject the stale update
Optimistic control is often suitable when:
- reads are frequent
- conflicting writes are uncommon
- users may keep an edit form open for a long time
- holding database locks would be impractical
- conflicts can be handled explicitly
Its costs include:
- failed saves
- retry logic
- merge complexity
- possible user frustration
- complicated multi-row version coordination
Pessimistic and optimistic control compared
CriterionPessimistic controlOptimistic control
| Basic assumption | Conflicts are likely | Conflicts are rare |
| Main technique | Lock before modification | Validate before commit or update |
| Conflict result | Another transaction waits | One operation fails or retries |
| Main strength | Directly prevents conflicting changes | High read concurrency |
| Main cost | Blocking and deadlocks | Retry and conflict handling |
| Common use | Inventory, balances, limited resources | Administrative editing, documents, low-contention data |
Neither strategy is universally better.
The decision depends on:
- conflict frequency
- transaction duration
- cost of waiting
- cost of retrying
- user experience
- business consequences of stale data
Atomic UPDATE statements can simplify concurrency
A read-calculate-write sequence creates a gap in which another transaction may change the data.
SELECT the current balance
Calculate a new balance in application code
UPDATE the row with the calculated absolute value
Instead of storing an absolute value:
UPDATE accounts
SET balance = 900000
WHERE account_id = 'A-100';
the database can perform the calculation atomically:
UPDATE accounts
SET balance = balance - 100000
WHERE account_id = 'A-100'
AND balance >= 100000;
The number of affected rows can communicate the result.
Affected rows = 1
→ Withdrawal succeeded
Affected rows = 0
→ Account not found or insufficient balance
This reduces the race window between reading and writing.
Not every business process fits into one SQL statement, but expressing simple state transitions as atomic database operations is often safer than calculating stale absolute values in application memory.
Transaction boundaries should be short and meaningful
Long transactions hold resources for a long time.
Consider this sequence:
Begin transaction
Wait for user input
Call an external API
Process a large file
Run additional queries
Commit
Possible consequences include:
- longer lock waits
- increased deadlock probability
- database connections held for too long
- delayed cleanup of MVCC versions
- larger rollback cost
- external latency propagated into the database
- more time for conflicts to develop
As a general principle, a database transaction should contain the database work that must succeed or fail together.
Outside the transaction
Prepare external input
Parse files
Wait for user interaction
Perform non-transactional calculations
Inside the transaction
Validate relevant database state
Modify related records
Write required history
Commit
This does not mean transactions should be split merely to make them shorter.
Splitting one atomic business operation across several independent commits may create inconsistency.
The correct boundary is the smallest boundary that still preserves the required business atomicity.
A database transaction does not automatically roll back external systems
Suppose a service method performs:
1. Save an order in the database
2. Call an external payment API
3. Mark the payment as completed
4. Send an email
If the database transaction rolls back, an already approved external payment is not automatically reversed.
An email that has already been sent cannot be unsent by database rollback.
Database ROLLBACK
≠
Automatic rollback of external side effects
Operations spanning independent systems may require additional techniques:
- compensation
- idempotency keys
- transactional outbox
- event publishing
- duplicate detection
- retry policies
- explicit state machines
- reconciliation jobs
Traditional ACID guarantees normally apply within a database transaction boundary.
Distributed systems frequently accept temporary inconsistency and coordinate state changes over time rather than trying to hold one global database lock across every external service.
Strong isolation does not replace business constraints
SERIALIZABLE prevents many concurrency anomalies, but it does not automatically encode every business rule.
Suppose a system requires:
A user may have only one active subscription.
The application first checks whether one exists.
SELECT COUNT(*)
FROM subscriptions
WHERE user_id = 100
AND status = 'ACTIVE';
Then it inserts a new subscription.
If two requests perform this check concurrently, both may conclude that no active subscription exists.
A database constraint should protect the final rule whenever possible.
CREATE UNIQUE INDEX uq_active_subscription
ON subscriptions (user_id, status);
The exact design may require a conditional unique index or a different representation, depending on the database.
Critical invariants are best protected through several layers:
Application validation
Database constraints
Appropriate isolation
Locking or version checks
A check in application code is not enough when another transaction can change the relevant state between the check and the write.
Retrying a transaction requires idempotency analysis
A database may abort one transaction to resolve:
- deadlock
- serialization failure
- transient lock conflict
- temporary connection failure
The application may retry the transaction.
Not every operation is safe to retry automatically.
Database UPDATE
→ Rolled back with the transaction
Email delivery
→ May already have succeeded
External payment call
→ May have succeeded even if the response was lost
A retried request may therefore create:
- duplicate payments
- duplicate emails
- duplicate orders
- duplicated history records
- repeated external commands
Idempotency helps make repeated execution safe.
A request can carry a unique identifier:
Request ID: PAYMENT-20260731-0001
The database can enforce uniqueness.
CREATE UNIQUE INDEX uq_payment_request
ON payments (request_id);
Retries are not merely an exception-handling concern.
They are part of the business protocol and must account for external side effects and ambiguous outcomes.
Transaction handling in Spring applications
Spring applications commonly place @Transactional on a service method that represents one business operation.
@Service
public class TransferService {
private final AccountRepository accountRepository;
private final TransferRepository transferRepository;
public TransferService(
AccountRepository accountRepository,
TransferRepository transferRepository
) {
this.accountRepository = accountRepository;
this.transferRepository = transferRepository;
}
@Transactional
public void transfer(
String fromAccountId,
String toAccountId,
long amount
) {
Account from =
accountRepository.findForUpdate(
fromAccountId
);
Account to =
accountRepository.findForUpdate(
toAccountId
);
from.withdraw(amount);
to.deposit(amount);
transferRepository.save(
new Transfer(
fromAccountId,
toAccountId,
amount
)
);
}
}
The important point is not the presence of the annotation.
The important point is the business boundary:
Withdraw from the source account
Deposit into the destination account
Record the transfer
These changes belong to one unit and should commit or roll back together.
Spring applications must also consider:
- proxy-based transaction interception
- self-invocation that bypasses the proxy
- rollback rules for different exception types
- propagation behavior
- isolation settings
- read-only hints
- external API calls inside transactions
- long-running loops
- work started on another thread
- events that should run after commit
Adding @Transactional does not automatically solve every consistency problem.
Correctness still depends on:
- SQL design
- locking
- isolation
- constraints
- transaction boundaries
- external side-effect handling
Key concepts for certification exams
Definition of a transaction
A set of operations that performs one logical function
within a database
ACID
Atomicity
All operations succeed or all are undone
Consistency
Defined integrity rules remain valid
Isolation
Concurrent transactions do not interfere incorrectly
Durability
Committed results survive failure
Common concurrency anomalies
Lost update
One transaction's change is overwritten by another
Dirty read
A transaction reads uncommitted data
Non-repeatable read
The same row returns a different committed value
Phantom read
The set of rows matching a condition changes
Basic lock modes
Shared lock
Used for reading
Compatible with other shared locks
Exclusive lock
Used for writing
Conflicts with shared and exclusive locks
Two-phase locking
Growing phase
Locks may be acquired but not released
Shrinking phase
Locks may be released but not acquired
Necessary deadlock conditions
Mutual exclusion
Hold and wait
No preemption
Circular wait
Standard isolation levels
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
As isolation generally becomes stronger, consistency guarantees increase while available concurrency may decrease.
Correct concurrency control does not mean locking everything
A system could use the strongest locks and SERIALIZABLE isolation for every operation.
That approach might protect data, but it could also create:
- excessive waiting
- frequent deadlocks
- reduced throughput
- more transaction retries
- poor user response time
The opposite extreme is also dangerous. Weak isolation and no conflict detection may improve apparent speed while producing invalid data.
A sound concurrency-control strategy considers:
- which records are accessed concurrently
- how often writes conflict
- the cost of an incorrect result
- whether the operation can be retried
- how long the transaction lasts
- the ratio of reads to writes
- the database’s MVCC and locking implementation
- which invariants can be enforced by constraints
For example, optimistic concurrency may be appropriate for editing a product description because simultaneous edits are uncommon.
A limited inventory count may need an atomic update or pessimistic lock because overselling is expensive.
UPDATE products
SET stock = stock - 1
WHERE product_id = 100
AND stock >= 1;
Concurrency control is not about selecting the strongest available mechanism.
It is about preserving the correctness required by the business while retaining as much safe concurrency as possible.
Transactions create boundaries in time
A transaction is more than a container around SQL statements.
It creates a temporal boundary in the database.
Before the transaction
The business operation has not started
During the transaction
Changes are in progress but not yet final
After COMMIT
The result is confirmed and may be trusted
After ROLLBACK
The database returns to the prior valid state
Concurrency control determines what happens when several such boundaries overlap.
It decides:
- which version a transaction may read
- who may update a row first
- when a transaction must wait
- when a transaction must fail
- which transaction should be retried
- whether a range of possible rows must be protected
ACID describes the properties of the boundary.
Locks and MVCC are mechanisms used to implement isolation.
Isolation levels decide how much interaction and change a transaction is allowed to observe.
Deadlock handling decides which transaction must be sacrificed when progress becomes impossible.
All of these concepts serve the same objective:
Preserve trustworthy database results even while many operations are happening at the same time.
The purpose of transactions is trustworthy state change
A transaction groups several operations into one logical unit.
Atomicity prevents partial results.
Consistency preserves defined rules and invariants.
Isolation controls interference between concurrent transactions.
Durability protects committed results from failure.
Concurrency-control mechanisms such as locks, serialization, validation, timestamps, and MVCC determine how concurrent work can proceed safely.
Transactions do not solve every consistency problem automatically.
A system can still fail because:
- the transaction boundary is incorrect
- the isolation level is too weak
- locks are held for too long
- application validation contains a race condition
- external side effects cannot be rolled back
- retries duplicate payments or messages
- important invariants are not protected by constraints
- the application assumes behavior that the database does not provide
Designing a transaction therefore requires answering questions such as:
Which operations must succeed or fail together?
What data should another transaction be allowed to observe while this work is in progress?
What is the correct result when two requests modify the same record?
Should a conflict cause waiting, failure, or retry?
How will external side effects be compensated or deduplicated?
The answers determine:
- transaction scope
- isolation level
- lock strategy
- versioning approach
- constraint design
- retry policy
- external integration protocol
Understanding transactions and concurrency control is not merely knowing the syntax of COMMIT and ROLLBACK.
It means being able to define the correct result when several users act on the same data and to choose mechanisms that allow the database to preserve that result.
The ultimate purpose is simple:
A state change should not be left half-finished, and concurrent activity should not make the final result untrustworthy.
That is how a database can serve many users at once while continuing to behave like one coherent system.
References
- Database System Concepts, Seventh Edition — Abraham Silberschatz, Henry F. Korth, and S. Sudarshan / McGraw-Hill
- Transactions — Database System Concepts
- Concurrency Control — Database System Concepts
- ISO/IEC 9075-1:2023 — Database Languages SQL — Part 1: Framework — ISO / IEC
- Transactions — PostgreSQL Global Development Group
- Concurrency Control — PostgreSQL Global Development Group
- Transaction Isolation — PostgreSQL Global Development Group
- Explicit Locking — PostgreSQL Global Development Group
- Data Consistency Checks at the Application Level — PostgreSQL Global Development Group
- Write-Ahead Logging — PostgreSQL Global Development Group
- Constraints — PostgreSQL Global Development Group
- Using @Transactional — Spring Framework
- Rolling Back a Declarative Transaction — Spring Framework
- Transaction-Bound Events — Spring Framework
- Making Retries Safe with Idempotent APIs — Amazon Web Services
