Work order status updates cause database locks and timeouts in manufacturing module

ERP Odoo
, , , , , , , ,
1

We’re experiencing intermittent database lock timeouts when shop floor workers update work order statuses on our manufacturing tablets. The issue happens most frequently during shift changes when multiple workers (10-15) are simultaneously closing completed work orders and starting new ones.

The error message users see is just a generic timeout, but in the PostgreSQL logs I can see lock wait timeouts on the mrp.workorder and mrp.production tables. Some updates take 30-45 seconds to complete, which is unacceptable for our fast-paced production environment.

We’re running Odoo 16 Enterprise with about 200 active work orders at any given time across three production lines. Each work order update seems to lock related records in mrp.production, stock.move, and quality.check tables. Is there a way to reduce the locking scope or optimize these updates to handle concurrent operations better?

2

Absolutely. Automated actions that trigger on work order status changes execute within the same transaction, which extends lock duration. Move any non-critical updates (notifications, dashboard updates, reporting calculations) to asynchronous scheduled actions that run every 1-2 minutes instead of real-time.

3

I’ve dealt with this exact issue in high-volume manufacturing environments. The problem is that Odoo’s manufacturing module uses pessimistic locking to maintain data integrity across related records. When a work order status changes, it needs to update inventory moves, quality checks, and potentially the parent manufacturing order state.

The key is to minimize the transaction scope and ensure your database is properly tuned for concurrent updates. You should also review your work order workflow - if you have many automated actions or computed fields that trigger on status changes, those are adding to the lock duration. Consider making some of those calculations asynchronous using scheduled actions instead of real-time computation.

4

We do have some custom automated actions that send notifications and update dashboard widgets when work orders complete. Could those be extending the transaction time and holding locks longer than necessary?

5

This sounds like cascading locks from foreign key relationships. When you update a work order, Odoo locks the parent production order and related stock moves. Check if you have any custom code that’s extending the write() method and adding additional locking.

6

Here’s a comprehensive approach to resolve your work order locking issues:

1. Optimize Database Lock Settings Update your PostgreSQL configuration to handle concurrent manufacturing operations better. In postgresql.conf, adjust these settings:

  • lock_timeout = 10s (fail fast instead of queuing)
  • deadlock_timeout = 1s (detect deadlocks quickly)
  • max_locks_per_transaction = 128 (increase from default 64)

Restart PostgreSQL after making these changes.

2. Refactor Automated Actions Identify all automated actions triggered by work order status changes. Move non-critical operations to scheduled actions:

  • Dashboard metric updates: Run every 2 minutes via scheduled action
  • Notification emails: Queue them and send in batches
  • Reporting calculations: Defer to nightly batch processing

Keep only essential operations (inventory moves, quality check creation) in the real-time transaction.

3. Implement Optimistic Locking Pattern For work order status updates, reduce lock scope by updating only the specific work order record first, then handle related records in a separate transaction:

  • Update work order status (quick, minimal locking)
  • Commit transaction immediately
  • Process related updates (stock moves, parent MO) in subsequent transaction
  • Use try-except blocks with retry logic for conflicts

4. Add Database Indexes for Concurrent Access Ensure you have indexes on frequently locked columns to speed up lock acquisition. Work orders are typically queried by production_id and state during updates, so verify these indexes exist.

5. Implement Client-Side Retry Logic On your shop floor tablets, implement exponential backoff retry logic for status updates. If a lock timeout occurs, wait 1-2 seconds and retry. This prevents the lock convoy effect where multiple tablets keep hammering the database with failed requests.

6. Review Work Order Update Workflow Consider breaking up complex status transitions. Instead of a single ‘Complete’ button that updates work order, stock moves, quality checks, and parent MO simultaneously, use a two-phase approach:

Phase 1: Mark work order as ‘Completed’ (fast, minimal locks)

Phase 2: Background job processes inventory and quality updates (runs within seconds, but doesn’t block UI)

7. Monitor Lock Contention Create a monitoring view to track lock waits:

Query pg_stat_activity and pg_locks to identify which specific queries are causing the most lock contention. This helps you prioritize which operations to optimize first.

Expected Results: After implementing these changes, work order status updates should complete in under 2 seconds even during shift changes. Lock timeouts should become rare (less than 1% of operations). The system will gracefully handle 15-20 concurrent updates without performance degradation.

The key is reducing transaction scope, deferring non-critical operations, and implementing proper retry logic. Your shop floor workers will experience immediate, responsive updates without the frustrating timeouts.