Our sales team is randomly getting constraint violation errors when creating quotations. The error appears intermittently - about 5-10 times per day across our 25 sales users. When it happens, the quotation form freezes and users have to refresh and start over, losing any data they entered.
The error message isn’t very helpful to our users, but from the logs I can see it’s related to duplicate sequence numbers in the sale.order table. We’re on Odoo 15 Community Edition with about 15,000 quotations created so far this year. The issue started appearing about three weeks ago when we scaled up our sales team from 12 to 25 users.
I’ve checked the sequence configuration in Settings > Technical > Sequences and the quotation sequence appears normal. The pattern is SO%(range_year)s%(range_month)s%(y)s-%(seq)s with a step of 1. Is this a concurrency issue with multiple users hitting the sequence generator simultaneously? How do we prevent this from happening?
One additional consideration: if you absolutely need consecutive numbers for compliance or audit reasons, you can keep No gap but implement a retry mechanism in your custom code. However, for standard quotations, gaps are completely acceptable and Standard implementation is the way to go.
I’ve encountered this exact scenario. The root cause is usually the sequence implementation type combined with transaction isolation levels in PostgreSQL. Odoo’s ‘No gap’ sequences use SELECT FOR UPDATE which can cause deadlocks under high concurrency. With 25 concurrent users, you’re hitting that threshold.
Switch your quotation sequence to ‘Standard’ implementation instead of ‘No gap’. Standard sequences use PostgreSQL’s native sequence objects which handle concurrency much better. You’ll have gaps in your sequence numbers when quotations are deleted or cancelled, but that’s normal and acceptable in most business scenarios.
Also check your PostgreSQL logs for deadlock errors. Run this query to see if you have sequence-related deadlocks: SELECT * FROM pg_stat_database WHERE datname = ‘your_db_name’. If deadlocks are increasing, that confirms the concurrency issue.
Here’s the complete solution to fix your sequence constraint errors:
Step 1: Verify Current Sequence Configuration
Go to Settings > Technical > Sequences, find your quotation sequence (likely named ‘Sales Order’ or similar), and confirm it’s using ‘No gap’ implementation. This is your problem - No gap sequences use database locks that cause conflicts with concurrent users.
Step 2: Switch to Standard Implementation
Edit the sequence and change Implementation from ‘No gap’ to ‘Standard’. Standard implementation uses PostgreSQL’s native sequence objects which are specifically designed for high-concurrency scenarios.
Step 3: Reset Sequence to Safe Number
The tricky part is ensuring no duplicates occur during the transition. Find your highest existing quotation number and set the sequence’s Next Number to be higher than that. For example, if your last quotation was SO202504-1247, set Next Number to 1250 to give yourself a safety buffer.
Step 4: Test Concurrency
Have multiple users (at least 5-6) create quotations simultaneously to verify the constraint errors are gone. Standard sequences handle this gracefully - each user gets a unique number even under heavy concurrent load.
Step 5: Communicate with Management
Explain to management that gaps in quotation numbers are normal and acceptable. Gaps occur when:
- Quotations are deleted or cancelled
- Users start creating a quotation but abandon it
- System errors occur before saving
These gaps don’t indicate missing data - they’re a necessary trade-off for system reliability at scale. Most accounting and compliance frameworks accept non-consecutive numbering as long as each number is unique and traceable.
Alternative for Strict Compliance Requirements
If your industry has strict consecutive numbering requirements (rare, but exists in some jurisdictions), you have two options:
-
Implement a post-processing numbering system where quotations get temporary numbers during creation, then receive final consecutive numbers during a nightly batch process after approval.
-
Use Standard sequences for internal system operations but display a separate consecutive reference number to users, generated through a different mechanism with appropriate locking.
For 99% of businesses, switching to Standard implementation resolves the issue completely. Your constraint errors will disappear, your sales team can work without interruptions, and your system will scale smoothly even if you double your user count again.
Before changing the sequence type, make sure there are no pending transactions that might have reserved sequence numbers. Also, after switching to Standard implementation, you should reset the sequence’s next number to avoid potential duplicates with already-used numbers from the No gap sequence.
Checked the sequence settings and yes, it’s set to ‘No gap’ implementation. I can see why that was chosen initially - management wanted consecutive quotation numbers without gaps. But the errors are causing bigger problems than number gaps would. Should I just switch it to Standard or are there other considerations?
This is definitely a sequence concurrency issue. When multiple users create quotations simultaneously, they can grab the same sequence number before the transaction commits. Check if your sequence is using the ‘Standard’ or ‘No gap’ implementation - ‘No gap’ is more prone to this problem.