Re: Serialization failures on PQ9.5

On 09/07/2016 05:58 PM, Alvaro Herrera wrote:
>> Now, we are facing the same issue using 9.0e-20160504 with psql 9.5.4.
>> We are getting ERROR: could not serialize access due to concurrent update
>> When executing 2 same sql at same time:
>> UPDATE "stock_quant" SET "qty"=10.0,"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (271941)
>>
>> Just want to know, is there any final solution for this bug? Has it been fixed on 9.5.4 or not?
>
> 9.5.4 was tagged on August and the fix was pushed on 15th July, so 9.5.4
> should definitely not exhibit the same problem.
>
> Note that you can receive the "could not serialize access ..." error
> message for other cases, so it's not necessarily a bug that you are
> getting it.

Exactly. If, as you say, you're executing twice the exact same UPDATE on the
same `stock_quant` record, in two parallel transactions, you _must_ be getting
a TransactionRollbackError. Odoo uses REPEATABLE READ isolation mode explicitly
for that purpose, otherwise you might be silently corrupting the data integrity.

Note that the Odoo framework will detect such transaction conflicts in most
cases and silently retry the failed transaction. You will notice this in the
logs with messages such as:
(...) INFO (...) SERIALIZATION_FAILURE, retry 1/5 in 0.324 sec
In most cases the next attempt will pass without any conflict.

This is the expected behavior when two transaction attempt to update the same
record at the same time, and you can safely ignore them.
If you have a lot of them to the point of impacting your system performance,
then something might be wrong in your installation / customizations.

You can find out whether you're facing this bug or a normal conflict by looking
at the PostgreSQL ERROR CONTEXT in the logs. A regular conflict will typically
show an UPDATE query (it should in your example), whereas you would see an
internal SELECT FOR [KEY] SHARE when the bug is triggered, e.g:
ERROR: could not serialize access due to concurrent update
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

Hope this helps,

Olivier