Skip to content
ERP Concurrency

Handling Concurrency in ERP: Preventing Data Conflicts in Multi-User Environments

Learn how to prevent data conflicts in multi-user ERP environments through optimistic locking, transaction management, and robust error handling. A practical guide for developers and architects.

10 min readSysGenPro Editorial
Handling Concurrency in ERP: Preventing Data Conflicts in Multi-User Environments

Preventing Data Conflicts Through Robust Concurrency Control

Handling concurrency in ERP systems requires a strategic combination of database-level locking mechanisms, application-level validation, and user-friendly error handling to prevent data conflicts in multi-user environments. The primary risk is the 'lost update' problem, where two users modify the same record simultaneously, and one change overwrites the other without notification. This leads to financial inaccuracies, inventory discrepancies, and broken business processes. The recommended approach is to default to optimistic locking for high-read, low-write scenarios, as it minimizes database contention and improves system throughput. For critical financial transactions or inventory adjustments where data integrity is paramount, pessimistic locking may be necessary, though it must be carefully scoped to avoid deadlocks and performance bottlenecks. By implementing version numbers or timestamps on records, validating state before commit, and providing clear feedback to users, organizations can maintain data integrity while preserving system availability and user experience.

Understanding Concurrency Risks in ERP Environments

ERP systems are inherently multi-user, with finance, operations, sales, and procurement teams accessing the same core data simultaneously. Without proper concurrency control, several anomalies can occur. The most common is the lost update, where User A reads a record, User B reads the same record, User A updates it, and User B updates it, resulting in User A's changes being silently discarded. Another risk is the dirty read, where a transaction reads data that has been modified by another transaction that has not yet committed. If the second transaction rolls back, the first transaction has acted on invalid data. Non-repeatable reads occur when a transaction reads the same row twice and gets different results because another transaction updated and committed the row in between. These issues are particularly dangerous in ERP contexts where financial ledgers, inventory counts, and order statuses must remain consistent.

Business Impact of Data Conflicts

Data conflicts in an ERP system do not just cause technical errors; they have direct business consequences. In finance, a lost update on a journal entry can lead to unbalanced ledgers, requiring time-consuming manual reconciliation. In inventory management, concurrent updates to stock levels can result in overselling or phantom stock, disrupting supply chain operations. In sales, conflicting updates to customer credit limits can lead to unauthorized credit extensions. These issues erode trust in the system, increase operational overhead, and can lead to compliance violations if audit trails are compromised. Therefore, concurrency control is not merely a technical detail but a critical component of business process integrity.

Optimistic Locking: The Default Strategy

Optimistic locking is the preferred approach for most ERP customizations and standard modules because it assumes that conflicts are rare. Instead of locking the record in the database when it is read, the system allows multiple users to read and modify the record concurrently. To detect conflicts, a version number or timestamp is added to the record. When a user attempts to save changes, the system checks if the version number in the database matches the version number the user had when they started editing. If it matches, the update proceeds, and the version number is incremented. If it does not match, it means another user has already modified the record, and the system rejects the update, prompting the user to review the changes and re-enter their data.

Implementing Version Numbers

To implement optimistic locking, add a non-nullable integer column, such as 'version', to the relevant database tables. Initialize this column to 0 for new records. In the application logic, when a record is loaded for editing, store the current version number in the user session or the form state. When the user submits the form, include the version number in the update query. The SQL update statement should include a WHERE clause that checks the version number. For example: UPDATE table SET field = value, version = version + 1 WHERE id = id AND version = expected_version. If the update affects zero rows, a conflict has occurred. This method is efficient because it does not hold database locks during the user's editing time, allowing high concurrency. It is particularly suitable for master data, such as customer or vendor records, where conflicts are infrequent.

Pessimistic Locking: When to Use It

Pessimistic locking assumes that conflicts are likely and prevents them by locking the record as soon as it is read for modification. This is achieved using SQL commands like SELECT FOR UPDATE, which places an exclusive lock on the row until the transaction commits or rolls back. While this guarantees that no other user can modify the record during the editing session, it has significant drawbacks. It reduces system throughput because other users must wait for the lock to be released. It also increases the risk of deadlocks if multiple transactions attempt to acquire locks in different orders. Pessimistic locking should be reserved for critical, short-duration transactions where data integrity is non-negotiable, such as inventory adjustments, financial postings, or order allocation. It is not suitable for long-running user sessions, such as editing a complex sales order, as it would block other users from accessing the same inventory or customer data for extended periods.

Mitigating Deadlock Risks

When using pessimistic locking, it is crucial to minimize the duration of the lock and ensure consistent lock ordering. Transactions should acquire locks in a predefined order to prevent circular wait conditions, which are a primary cause of deadlocks. For example, if a transaction needs to lock both a customer record and an inventory record, it should always lock the customer record first, followed by the inventory record. Additionally, set appropriate lock timeouts to prevent transactions from waiting indefinitely. If a timeout occurs, the transaction should be rolled back, and the user should be notified. Monitoring tools should be configured to detect and alert on deadlock occurrences, allowing developers to analyze and optimize the locking strategy.

Transaction Isolation Levels and Their Impact

Database transaction isolation levels define how transactions interact with each other and what level of consistency is guaranteed. The standard levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Read Uncommitted allows dirty reads and is generally not suitable for ERP systems due to the risk of acting on uncommitted data. Read Committed is the default for many databases and prevents dirty reads but allows non-repeatable reads. Repeatable Read ensures that if a transaction reads a row, it will read the same value if it reads the row again, even if another transaction updates it. Serializable is the strictest level, ensuring that transactions execute as if they were run sequentially. It prevents all anomalies but has the highest performance cost due to extensive locking. For most ERP applications, Read Committed or Repeatable Read is appropriate, depending on the specific business requirements and the database engine's capabilities.

Isolation LevelDirty ReadsNon-Repeatable ReadsPhantom ReadsPerformance ImpactERP Suitability
Read UncommittedYesYesYesLowNot Recommended
Read CommittedNoYesYesMediumCommon Default
Repeatable ReadNoNoYesHighGood for Consistency
SerializableNoNoNoVery HighCritical Financial Data

Application-Level Conflict Resolution

Database locking mechanisms are necessary but not sufficient. The application layer must handle conflicts gracefully to provide a good user experience. When an optimistic locking conflict is detected, the system should not simply display a generic error message. Instead, it should inform the user that the record has been modified by another user and provide options to resolve the conflict. Common strategies include: 1) Reload the record and ask the user to re-enter their changes. 2) Display a side-by-side comparison of the user's changes and the current database values, allowing the user to merge the changes manually. 3) Automatically merge non-conflicting fields and prompt the user to resolve conflicting fields. The choice of strategy depends on the complexity of the data and the business process. For simple fields, reloading is often sufficient. For complex records, a merge interface is more user-friendly.

User Interface Considerations

The user interface should clearly indicate when a record is being edited by another user, if possible. This can be achieved by displaying a 'locked by' indicator or a timestamp of the last modification. While this does not prevent conflicts, it alerts users to potential issues before they attempt to save. Additionally, the system should minimize the time between reading a record and saving it. Long editing sessions increase the likelihood of conflicts. For complex forms, consider breaking them into smaller, more focused screens to reduce the scope of potential conflicts. Clear error messages are also crucial. Instead of 'Update failed,' use 'The record was modified by [User Name] at [Time]. Please review the changes and try again.' This transparency helps users understand the cause of the error and take appropriate action.

Testing Concurrency in ERP Systems

Testing concurrency is challenging because conflicts are non-deterministic and depend on timing. Unit tests alone are insufficient. Integration tests should simulate multiple users modifying the same record simultaneously. This can be achieved using multi-threaded test scripts that perform read-modify-write operations on the same record. The tests should verify that the system correctly detects conflicts, prevents lost updates, and handles errors gracefully. Load testing is also essential to evaluate the performance impact of locking mechanisms under high concurrency. Monitor database lock waits, deadlock occurrences, and transaction response times. If performance degrades significantly, consider adjusting the isolation level, optimizing queries, or redesigning the locking strategy. Automated regression tests should include concurrency scenarios to ensure that future changes do not introduce new conflicts.

Monitoring and Observability

Effective monitoring is critical for maintaining concurrency control in production. Implement logging to capture all conflict events, including the user, record ID, timestamp, and the nature of the conflict. This data can be used to identify patterns, such as specific records or processes that frequently cause conflicts. Set up alerts for high levels of lock waits or deadlocks, as these may indicate performance issues or design flaws. Use database monitoring tools to track lock statistics, such as the number of active locks, average lock wait time, and deadlock frequency. Regularly review these metrics to proactively address potential issues. Additionally, monitor application logs for error messages related to concurrency, such as 'version mismatch' or 'lock timeout.' This observability enables continuous improvement of the concurrency control strategy.

Best Practices for ERP Developers

  • Default to optimistic locking for most records to maximize concurrency.
  • Use pessimistic locking only for critical, short-duration transactions.
  • Implement version numbers or timestamps on all mutable records.
  • Provide clear, user-friendly error messages for conflict resolution.
  • Minimize transaction duration to reduce lock contention.
  • Ensure consistent lock ordering to prevent deadlocks.
  • Test concurrency scenarios with multi-threaded integration tests.
  • Monitor lock waits, deadlocks, and conflict events in production.
  • Document the locking strategy for each module to guide future development.
  • Review and optimize locking strategies regularly based on performance data.

Conclusion

Handling concurrency in ERP systems is a critical aspect of ensuring data integrity and system reliability. By understanding the risks of data conflicts and implementing appropriate locking mechanisms, organizations can prevent lost updates, dirty reads, and other anomalies. Optimistic locking is the preferred default strategy for most scenarios, while pessimistic locking should be used judiciously for critical transactions. Application-level conflict resolution, thorough testing, and continuous monitoring are essential to maintain a robust and user-friendly system. By following these best practices, ERP developers and administrators can build systems that are both highly concurrent and data-consistent, supporting the complex business processes that rely on accurate and timely information.

Written by
SysGenPro Editorial

SysGenPro Editorial

Platform & solutions team

Practical guidance on ERP, AI automation, cloud deployment and enterprise transformation from the SysGenPro team.

Share this article

Frequently asked questions

Common questions on this topic. Talk to our team for anything specific to your business.

01What is the difference between optimistic and pessimistic locking?

Optimistic locking assumes conflicts are rare and checks for them at save time using version numbers. Pessimistic locking assumes conflicts are likely and locks the record immediately upon read, preventing other users from modifying it until the transaction completes.

02How do I detect concurrent updates in an ERP system?

Use optimistic locking by adding a version number or timestamp column to your records. When saving, compare the version number in the database with the one the user had when they started editing. If they differ, a concurrent update has occurred.

03What is a lost update in a database?

A lost update occurs when two transactions read the same data, both modify it, and both write it back. The second write overwrites the first, causing the first transaction's changes to be lost.

04How can I prevent deadlocks in ERP applications?

Prevent deadlocks by ensuring consistent lock ordering, minimizing transaction duration, and setting appropriate lock timeouts. Monitor for deadlocks and analyze the involved transactions to identify and fix the root cause.

05Should I use row-level or table-level locks in ERP?

Row-level locks are generally preferred in ERP systems as they allow higher concurrency by locking only the specific rows being modified. Table-level locks should be avoided as they block all access to the table, significantly reducing performance.

06How does transaction isolation affect ERP performance?

Higher isolation levels, such as Serializable, provide stronger consistency guarantees but increase locking and reduce performance. Lower levels, like Read Committed, offer better performance but may allow non-repeatable reads. Choose the level that balances consistency requirements with performance needs.

Continue learning
View all articles
Explore the platform

Where to go next

Browse the knowledge hub

See how SysGenPro fits your business

Discuss your workflows, modules and deployment options with our team.

Back to resources