A database operation can work perfectly during testing and still fail when multiple requests execute simultaneously.
Two customers may purchase the last available product. Two workers may process the same order. Two API requests may update an account balance using the same outdated value.
These problems are known as concurrency issues. They can cause duplicate transactions, lost updates, incorrect inventory, and inconsistent application state.
For backend developers, senior engineers, and technical leads, preventing these failures requires more than writing correct SQL. It requires understanding transactions, isolation, locking, constraints, and how concurrent operations interact.
1. Understand the Race Condition
Consider an application that manages inventory.
A product has five units in stock. Two requests arrive simultaneously, and each attempts to purchase four units.
Both requests read the same initial value:
SELECT stock FROM products WHERE id = 101;Each sees five units available. Both conclude that the purchase is valid, potentially allowing eight units to be sold.
The problem is that checking availability and updating stock are separate operations without sufficient concurrency protection.
A race condition occurs when the outcome depends on the timing or interleaving of concurrent operations.
The solution is to make the critical operation safe at the database level rather than relying on application timing.
2. Use Transactions for Related Changes
A database transaction groups related operations into a unit of work.
The traditional ACID properties are:
Atomicity: all operations succeed together or roll back together.
Consistency: transactions preserve defined data integrity rules.
Isolation: concurrent transactions are controlled according to the selected isolation level.
Durability: committed changes persist according to the database's durability guarantees.
Suppose an order requires inserting an order record, inserting order items, and reducing inventory. These changes should not be left partially applied if one operation fails.
In Laravel:
use Illuminate\Support\Facades\DB;
DB::transaction(function () use ($productId, $quantity) {
$product = DB::table('products')
->where('id', $productId)
->lockForUpdate()
->first();
if (!$product || $product->stock < $quantity) {
throw new RuntimeException(
'Insufficient inventory.'
);
}
DB::table('products')
->where('id', $productId)
->decrement('stock', $quantity);
// Create the order and its items here.
});The transaction and row lock ensure that competing transactions cannot both perform this protected read-and-update sequence using the same unlocked inventory state, assuming the database engine supports the locking behavior and the operations use the same transaction.
In production, validate that the quantity is positive, handle expected business failures appropriately, and keep transactions short.
3. Prefer Atomic Updates When Possible
Sometimes you do not need to read a value into application code before changing it.
For inventory, a conditional update can combine the availability check and stock reduction:
UPDATE products
SET stock = stock - 4
WHERE id = 101
AND stock >= 4;Check the affected-row count. One affected row means the update succeeded; zero means the product was missing or insufficient stock was available.
The quantity should be a validated parameter, not concatenated into SQL.
This pattern avoids the classic read-check-update race for a single inventory adjustment. Related order creation must still be handled transactionally.
Practical principle: if a business rule can be enforced safely by one atomic database statement, consider that before implementing a separate read and write.
4. Understand Isolation Levels
Isolation levels determine how concurrent transactions interact and what data a transaction can observe.
Common SQL isolation levels include:
Read Uncommitted: may allow reading uncommitted changes.
Read Committed: reads committed data, with behavior depending on the database and statement.
Repeatable Read: provides stronger consistency for repeated reads, with specific behavior determined by the database implementation.
Serializable: provides the strongest standard isolation level, restricting concurrent execution to preserve serializable outcomes.
MySQL InnoDB uses REPEATABLE READ by default in common configurations, but locking reads, ordinary reads, and range operations can behave differently.
Higher isolation can reduce certain anomalies, but it may also increase blocking, deadlocks, or transaction retries.
Do not change the isolation level simply because an application has a race condition. First identify the anomaly, understand the database's behavior, and choose the narrowest effective protection.
5. Use Row Locks Carefully
A locking read such as SELECT ... FOR UPDATE can protect rows that a transaction needs to modify.
It is useful for inventory allocation, balance updates, and state transitions that depend on the current row value.
However, locking has trade-offs:
Other transactions may wait for the same rows.
Long transactions reduce concurrency.
Inconsistent lock ordering can cause deadlocks.
Locking behavior depends on indexes, query conditions, isolation level, and database engine.
Always access shared rows in a consistent order where practical. Keep network calls and lengthy computation outside database transactions.
A database may choose a deadlock victim even when each transaction is logically valid. Applications should handle retryable deadlocks using bounded retries and safe transaction logic.
6. Prevent Lost Updates With Optimistic Concurrency
Not every workflow needs an exclusive lock.
For records that are updated relatively infrequently, optimistic concurrency can detect whether another transaction changed the record after it was read.
For example, add a version column:
UPDATE documents
SET content = ?,
version = version + 1
WHERE id = ?
AND version = ?;The application supplies the version it originally read. If the affected-row count is zero, another update may have occurred, or the record may no longer exist.
The application can then reload the record, report a conflict, or ask the user to reconcile changes.
This approach is useful for document editing, administrative forms, and workflows where silently overwriting someone else's work would be unacceptable.
7. Enforce Integrity With Database Constraints
Application validation improves usability, but it cannot always prevent concurrent requests from violating a rule.
Suppose two requests check whether an email address exists before creating a user. Both checks may return false, allowing both requests to attempt insertion.
A database unique constraint provides the final guarantee:
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);The application should handle duplicate-key errors and return an appropriate response.
Use database constraints for rules such as unique identifiers, required relationships, and valid references wherever supported.
Do not rely exclusively on a preliminary SELECT to enforce uniqueness.
8. Apply the Same Principles in Java
Spring applications commonly use @Transactional to define transaction boundaries:
@Service
public class InventoryService {
@Transactional
public void reserveInventory(
Long productId, int quantity) {
// Validate quantity.
// Load and protect inventory.
// Verify availability.
// Update stock and create the order.
}
}The annotation alone does not prevent every race condition. The method must use appropriate repository queries, database constraints, locking or atomic updates, and suitable transaction configuration.
Be aware that Spring's transaction behavior depends on how the method is invoked and configured. In typical proxy-based setups, self-invocation can bypass transactional interception.
Test concurrency-sensitive code against the actual database engine rather than assuming an in-memory test database behaves identically.
9. Make Retries and External Side Effects Safe
Transactions protect database operations, but they cannot automatically roll back an email, payment-provider request, or external API call.
For example, a payment provider may accept a charge just before the application times out. Retrying the request without an idempotency mechanism could create a second charge.
Use provider-supported idempotency keys, durable operation identifiers, and explicit state transitions.
For workflows that update the database and publish events, consider the transactional outbox pattern. It stores the business change and an event record in one transaction, allowing a separate publisher to deliver events reliably.
Consumers must still tolerate duplicate delivery.
10. A Practical Concurrency Testing Checklist
Before deploying a critical workflow:
Simulate simultaneous requests against the same record.
Test competing purchases for limited inventory.
Verify that unique constraints reject duplicates.
Force transaction failures and confirm rollback behavior.
Test deadlock handling and bounded retries.
Verify optimistic concurrency conflicts.
Test duplicate job and webhook delivery.
Monitor lock waits, deadlocks, transaction duration, and query latency.
Use realistic concurrency levels and the same database engine as production whenever possible.
Common Mistakes to Avoid
Assuming a transaction automatically solves every race condition.
Performing a read and write without concurrency protection.
Holding database locks while calling external services.
Using application checks instead of database constraints for uniqueness.
Increasing isolation without understanding its performance impact.
Retrying non-idempotent operations blindly.
Ignoring deadlocks and lock-wait timeouts.
Testing concurrency only with sequential requests.
Final Thoughts
Concurrency problems often appear only under production load, but they can be prevented through deliberate database design.
Use transactions for related changes, atomic statements for simple invariants, locks when coordinated access is necessary, optimistic concurrency when conflicts should be detected, and database constraints for enforceable integrity rules.
The right strategy depends on the business operation and the database's actual behavior. Measure, test competing requests, and make correctness a database-level design concern.
Mirakinetix helps businesses build, automate, and scale reliable software systems. Visit dparab.me for practical engineering insights and Mirakinetix for software architecture, automation, performance optimization, and application security.
Build. Automate. Scale.
