Postgres Pro
Downloads
Home   >   mailing lists

Thread: Serialization failures on PQ9.5

Serialization failures on PQ9.5

From
Sean Gong
Date:
Hello,

I=A1=AFm Sean Gong and we are a Odoo 9.0 enterprise version user.=20

I saw your thread (BUG #13681) on  =
https://www.postgresql.org/message-id/flat/20151014164844.3019.25750%40wri=
gleys.postgresql.org#20151014164844.3019.25750@wrigleys.postgresql.org =
<https://www.postgresql.org/message-id/flat/20151014164844.3019.25750@wrig=
leys.postgresql.org#20151014164844.3019.25750@wrigleys.postgresql.org>.

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"=3D10.0,"write_uid"=3D1,"write_date"=3D(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?

Thank you very much and hope to hear from you soon!


Cheers,
Sean Gong
CIO of ebayta

Re: Serialization failures on PQ9.5

From
Alvaro Herrera
Date:
Sean Gong wrote:
> Hello,
>
> I’m Sean Gong and we are a Odoo 9.0 enterprise version user.
>
> I saw your thread (BUG #13681) on
https://www.postgresql.org/message-id/flat/20151014164844.3019.25750%40wrigleys.postgresql.org#20151014164844.3019.25750@wrigleys.postgresql.org
<https://www.postgresql.org/message-id/flat/20151014164844.3019.25750@wrigleys.postgresql.org#20151014164844.3019.25750@wrigleys.postgresql.org>.
>
> 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.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Serialization failures on PQ9.5

From
Olivier Dony
Date:
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