Prevent lock sequence strict row when updating invoice
As explained in https://engineering.nordeus.com/postgres-locking-revealed/
As we can see we are executing UPDATE query which doesn’t touch anything related to parent table. After first execution we can see that just child table contains table level locks here. Same thing is for row-level locks. Only child table’s row is locked FOR UPDATE. This is optimization that exists in Postgres. If locking manager can figure out from the first query that foreign key is not changed (it is not mentioned in update query or is set to same value) it will not lock parent table. But in second query it will behave as it is described in documentation (it will lock parent table in ROW SHARE locking mode and referenced row in FOR SHARE mode).
So this means that if we write twice in the same transaction on the invoice, it will take a row lock for key share on the foreign keys even if they are not modified. This is the lowest row lock possible so it has not much impact as usually foreign keys are referential data which are not often updated. Except for the ir.sequence.strict
which is operation and frequently used. So since #5205 (closed) updating the invoice twice adds a new contention lock point (in addition to numbering the invoice).
A use case which write twice on the invoice is when validating a statement with lines linked to invoices. This is because in StatementLine.reconcile
we call Invoice.add_payment_lines
(which writes on the Invoice) and MoveLine.reconcile
(which may call Invoice.process
). So this can trigger two writes on the invoice. This can block posting invoice for a long time if the statement is huge.
I tried to avoid the first write by Invoice.add_payment_lines
but it is quite complex because we need to invalidate the cache of the invoice instance in order to properly compute Invoice.get_reconcile_lines_for_amount
. Also this will fix only this workflow but we could have other cases similar.
Finally I think the best option is to remove the foreign key on ir.sequence.strict
. We do not really need data integrity there because we have it from account.fiscalyear.invoice_sequence
which prevents to delete the sequence. And storing the sequence on the invoice has only the goal of ordering sequence usage so if it is deleted, it can no more be used incorrectly.