Concurrent Order Processing with Selective Locking

Imagine you're building a robust e-commerce platform. Orders need to be processed, and several steps are involved: checking inventory, applying discounts, calculating shipping costs, and finally, updating the order status. Simultaneous order processing is crucial for performance, but incorrect handling can lead to data inconsistencies (e.g., overselling, incorrect discounts). This challenge focuses on implementing advanced SQL locking strategies to ensure data integrity during concurrent order processing.

Problem Description

You are tasked with designing and implementing a SQL solution to handle concurrent order processing while preventing race conditions and ensuring data consistency. The core of the problem revolves around a simplified Orders table and an Inventory table.

What needs to be achieved:

Develop a SQL script (or a series of scripts) that demonstrates the use of advanced locking techniques (e.g., SELECT ... FOR UPDATE, WHERE CURRENT OF, optimistic locking) to safely process orders. The script should simulate a scenario where multiple processes attempt to process the same order concurrently. The goal is to prevent overselling (updating inventory with a negative quantity) and ensure that discounts are applied correctly, even when multiple processes are trying to process the same order simultaneously.

Key Requirements:

1. Inventory Check: Before processing an order, verify that sufficient inventory exists for all items in the order.
2. Discount Application: Apply a discount if the order total exceeds a certain threshold.
3. Inventory Update: Reduce the inventory quantity for each item in the order.
4. Order Status Update: Update the order status to "Processed."
5. Concurrency Handling: The solution must handle concurrent access to the Orders and Inventory tables safely, preventing race conditions. Demonstrate at least one advanced locking technique beyond simple row-level locking.
6. Rollback: If any step fails (e.g., insufficient inventory), the entire transaction must be rolled back to maintain data consistency.

Expected Behavior:

• Multiple processes should be able to attempt to process orders concurrently.
• If an order can be processed successfully (sufficient inventory, discount applied correctly), the inventory should be updated, and the order status should change.
• If an order cannot be processed (insufficient inventory), the transaction should be rolled back, and the order status should remain unchanged.
• No overselling should occur.
• Discounts should be applied correctly, even with concurrent processing.

Edge Cases to Consider:

• Insufficient Inventory: Handle cases where the inventory for one or more items in the order is insufficient.
• Concurrent Discount Application: If multiple processes are processing orders with similar totals, ensure that only one discount is applied correctly.
• Deadlocks: Design the locking strategy to minimize the risk of deadlocks. If deadlocks are possible, include logic to detect and handle them (e.g., retry with a delay).
• Order Already Processed: Handle the case where an order is already in the "Processed" state.

Examples

Example 1:

Input:
Orders Table:
OrderID | TotalAmount | Status
------- | ----------- | --------
1       | 100         | Pending
2       | 250         | Pending

Inventory Table:
ItemID | Quantity
------- | --------
1       | 50
2       | 100

Discount Threshold: 200

Output:
Orders Table:
OrderID | TotalAmount | Status
------- | ----------- | --------
1       | 100         | Pending
2       | 250         | Processed

Inventory Table:
ItemID | Quantity
------- | --------
1       | 50
2       | 90

Explanation: Order 1 is below the discount threshold and remains pending. Order 2 exceeds the threshold, receives a discount (implicitly applied in the logic), and its status is updated to "Processed." Inventory is reduced accordingly.

Example 2:

Input:
Orders Table:
OrderID | TotalAmount | Status
------- | ----------- | --------
1       | 100         | Pending
2       | 250         | Pending

Inventory Table:
ItemID | Quantity
------- | --------
1       | 10
2       | 100
Discount Threshold: 200

Output:
Orders Table:
OrderID | TotalAmount | Status
------- | ----------- | --------
1       | 100         | Pending
2       | 250         | Pending

Inventory Table:
ItemID | Quantity
------- | --------
1       | 10
2       | 100

Explanation: Order 2 cannot be processed because there is insufficient inventory for ItemID 1. The transaction is rolled back, and the order status remains "Pending."

Constraints

• Database System: The solution should be compatible with a standard SQL database system (e.g., PostgreSQL, MySQL, SQL Server). Specify the database system used in your solution.
• Transaction Size: Assume that each order involves a maximum of 10 items.
• Concurrency: Simulate at least 5 concurrent processes attempting to process orders. This can be achieved through multiple database connections or simulated concurrency.
• Locking Technique: Must utilize at least one advanced locking technique beyond simple row-level locking (e.g., SELECT ... FOR UPDATE, WHERE CURRENT OF, optimistic locking with versioning).
• Performance: The solution should be reasonably efficient. Avoid unnecessary locking or complex queries that could significantly degrade performance.

Notes

• Consider using transactions to ensure atomicity.
• Think carefully about the order in which you acquire locks to minimize the risk of deadlocks.
• Document your locking strategy clearly. Explain why you chose the specific locking technique and how it prevents race conditions.
• The specific discount application logic is not critical; focus on the concurrency handling aspects. You can assume a simple discount calculation (e.g., 10% off for orders over 200).
• You are not required to implement a full e-commerce system; focus solely on the order processing logic.
• Pseudocode is acceptable, but should be detailed enough to be easily translated into SQL. Clearly indicate which parts represent SQL statements.
• Consider using a staging table to simulate concurrent updates and then applying the changes to the main tables within a transaction. This can help isolate the concurrency logic.