Project Name
Eliminating Race Conditions in a High-Volume ERP System With Database-Level Row Locking and Atomic Transactions
The client is a growing enterprise operating a high-volume ERP system processing thousands of concurrent operations with a combination of user-initiated requests and background automated tasks running simultaneously across the same data paths. As activity scaled, the system began encountering rare but structurally damaging data anomalies: duplicate serial numbers and transaction logs appearing in the database despite application-level validation logic that correctly prevented duplicates when tested in isolation.
The issue was invisible in local development environments. It could only manifest under the microsecond overlap conditions that real production concurrency produced, making it impossible to diagnose through standard testing and requiring a deep investigation into the database concurrency model underlying the ERP’s validation layer.
The key technical hurdles involved in identifying and resolving this production-level race condition.
- Duplicate Records Bypassing Application-Level Validation: Identical serial numbers and transaction logs intermittently appeared despite validation logic that worked correctly in isolation. Under concurrent load, two requests could pass the existence check before either record was committed, allowing both to proceed.
- Race Condition Invisible in Local Testing: The bug depended on microsecond overlaps between concurrent database operations, making it nearly impossible to reproduce in sequential local testing. Standard debugging consistently showed the validation working as expected.
- Application Layer Unable to Enforce Concurrency Safety: Application-level validation could not prevent another process from reading the same data between the check and create operations. Two concurrent requests could therefore pass validation before either committed.
- Manual Data Cleanup Consuming Operations Team Hours: Every duplicate required manual identification and cleanup, consuming operations time and introducing downstream errors in workflows that relied on unique serial numbers.
Ksolves, an AI-first Odoo development company, moved data validation from the application layer to the database layer, using a two-part strategy to eliminate the race condition at its source rather than relying on application checks that concurrent requests could bypass.
- Database-Level Row Locking With SELECT FOR UPDATE: Instead of simply checking whether a record existed, the query was modified to lock the relevant data path during the check. Using SELECT FOR UPDATE, the database ensures that other processes attempting to access the same path must wait until the current operation completes. The lock lasts only for the check-and-create sequence, keeping the impact minimal while enforcing sequential access.
- Atomic Transactions Wrapping the Check-and-Create Sequence: The check-and-create sequence was wrapped in a single atomic transaction, ensuring both operations succeed or fail together. If a conflict occurs, the transaction rolls back safely, preventing partial or duplicate records and ensuring waiting processes see the fully committed database state.
Technology Stack
| Category | Technology |
|---|---|
| Database Transactions | Atomic Transactions |
| Platform | ERP Database Layer – PostgreSQL |
| Validation | Stress Testing |
The results achieved after addressing the root cause of the concurrency issue:
- Zero Duplication Errors, Unique Serial Number Overlaps Resolved: Duplicate serial numbers and transaction logs were eliminated, with row locking preventing concurrent requests from passing the same existence check and atomic transactions safely handling conflicts.
- Guaranteed Data Integrity Under Concurrent Load: Stress testing with hundreds of simultaneous requests confirmed that transactions were properly serialized, with database-level locking enforcing consistent access to contested data.
- Zero User Performance Impact: Targeted row locks applied only to the required data path and released within milliseconds, ensuring no noticeable lag in everyday user workflows.
- Operations Team Hours Recovered From Manual Cleanup: Eliminating duplicate creation removed the recurring manual effort required to identify and resolve duplicate serial numbers that bypassed application-level validation.
This case showed that the duplicate-record issue was not a validation failure, but a concurrency problem that application-level checks could not reliably prevent. Ksolves addressed the root cause by moving enforcement to the database layer with SELECT FOR UPDATE and atomic transactions. The result was zero duplicate records under concurrent load, consistent data integrity, and no noticeable impact on user workflows. By resolving the race condition at the layer where concurrency is controlled, Ksolves turned an intermittent production issue into a reliable, predictable process.
Are Race Conditions Producing Duplicate Records in Your ERP Under Production Load?
