Community mailing list archives

community@mail.odoo.com

Re: Odoo Performance and concurrency locks

by
Camptocamp France SAS, Alexandre Fayolle - Camptocamp
- 09/18/2015 03:25:34
Hello Olivier,

Thanks for looking into this.

I created https://github.com/odoo/odoo/issues/8585 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 
> ir.model.data table. This has been optimized fairly recently at commit 05f176f:
>     https://github.com/odoo/odoo/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/sql_db.py", 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 
> http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE 
> 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: https://www.odoo.com/groups/community-59
> Post to: mailto:community@mail.odoo.com
> Unsubscribe: https://www.odoo.com/groups?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
http://www.camptocamp.com