Community mailing list archives


Re: Odoo Performance and concurrency locks

- 01 أكتوبر, 2015 07:24:00
Case closed:

With comment from @odony in

"The KEY SHARE/NO KEY UPDATE locks in PG 9.3+ do behave as expected in the sense that they do not block each other. However it turns out that PostgreSQL's heuristics for detection non-serializable patterns can still trigger when a long-running transaction repeatedly acquires the same KEY SHARE lock on a record that was concurrently updated. This makes the whole thing less useful than we hoped. We're still checking whether PG workarounds are possible in 7.0/8.0, but for 9.0 the easiest course is to move the field outside res.users."

Thanks all!

On Fri, Sep 18, 2015 at 11:22 PM, Moises Lopez <> wrote:
Alexandre Fayolle
Good catching

2015-09-18 2:27 GMT-05:00 Alexandre Fayolle <>:
Hello Olivier, Thanks for looking into this. I created with a step by step procedure to reproduce the issue on the Odoo runbot (I assume it uses the recommended PostgreSQL version). Alexandre On 17/09/2015 20:22, Olivier Dony wrote: > On 09/16/2015 09:58 AM, Alexandre Fayolle wrote: >> A typical use case I have for base_concurrency is the following: I need >> to perform a large import of data in an Odoo database. For instance 150k >> partners, for which I have a csv file. Importing this in one go is >> impractical as each line in the csv file takes ~500ms to be processed. > > For what it's worth, a significant part of the time it takes to import a CSV > line used to be wasted by function field recomputations linked to the > table. This has been optimized fairly recently at commit 05f176f: >
> > >> The obvious approach to speed up the import to split the csv file and to >> run the import using N different processes. Since the import process is >> fairly CPU intensive on the Odoo side, and I have lots of cores, this >> should help. Besides having smaller chunks provide a smaller penalty >> from the checkpoints, by forcing more commits. >> >> In this case, not having base_concurrency, even with Pg 9.3, will cause >> rollbacks: >> >> Traceback (most recent call last): >> File "server/openerp/", line 234, in execute >> res = self._obj.execute(query, params) >> TransactionRollbackError: could not serialize access due to concurrent >> update >> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."res_users" x WHERE >> "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" > > That's quite interesting, because the FOR KEY SHARE lock should only be blocked > by a direct DELETE query or UPDATE of the primary key (id) [1]. > > The CONTEXT of your error message also clearly indicates that this FOR KEY > SHARE was automatically taken for a FK constraint (typically create_uid). > It is much harder to identify the transaction that conflicted with it, although > you might have some luck by enabling Odoo's debug_sql logging or simply logging > all queries in PG's log (log_min_duration_statement = 0) > > I'd like to reproduce your case and investigate more, because this is at the > core of Odoo's transaction management (the last login date on res.users is just > a minor special case, even if annoying) > > Could you elaborate a bit on your steps to reproduce this on a PG9.3 > deployment? Does the split in multiple CSV files really matter? Don't you get > the same effect with a single long file import + a manual login on the side? > How do you actually trigger the exception? Just by logging in once more in > another tab while the import is in progress? (couldn't repro so far) > > Thanks! > > >> So yes this is exactly the situation you are describing as "unusual" in >> your other email, but this is a very real case, and not unusual at all, >> at least for me. I'm pretty certain that a similar situation can be >> achieved with the procurement scheduler on large instances. > > If the multiple parallel imports is irrelevant to the issue, then I guess you > could reproduce this with the scheduler. However the scheduler can easily skip > and reprocess items in a later transaction, while the import can't - it has to > behave as a single ACID transaction. > > > > [1] The manual at >
> states the following: > > "FOR KEY SHARE behaves similarly to FOR SHARE, except that the lock is weaker: > SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared > lock blocks other transactions from performing DELETE or any UPDATE that > changes the key values, but not other UPDATE, and neither does it prevent > SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE." > > _______________________________________________ > Mailing-List: > Post to: > Unsubscribe: > -- Alexandre Fayolle Chef de Projet Tel : +33 4 58 48 20 30 Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac Cedex

Post to:

Moisés López Calderón
Vauxoo - OpenERP's Gold Partner
Mobile: (+521) 477-752-22-30
Office: (+52) 477-773-33-46
twitter: @vauxoo

Post to:


Nils Hamerlinck
Project Manager

Trobz - Open Source Solutions for the Enterprise
4th floor, 47/2/57 Bui Dinh Tuy street, Binh Thanh district, HCMC, Vietnam
Mobile: +84 (0) 125 323 2332 / Office: +84 (0) 862 737 605 / / Skype: nils.hamerlinck