Community mailing list archives

community@mail.odoo.com

Re: Odoo Performance and concurrency locks

by
nils
- 09/18/2015 00:13:46
Olivier,

To reproduce:
- launch a big import of products using the import csv native wizard
- login through XML-RPC in parallel

2015-09-18 04:06:55,161 533 ERROR v8 openerp.sql_db: bad query: INSERT INTO "product_template" ("id", "track_all", "list_price", "mes_type", "uom_id", "expense_pdt", "uos_coeff", "sale_ok", "categ_id", "track_outgoing", "company_id", "income_pdt", "uom_po_id", "type", "track_incoming", "active", "rental", "sale_delay", "name", "available_in_pos", "to_weight", "create_uid", "write_uid", "create_date", "write_date") VALUES(nextval('product_template_id_seq'), false, '1.00', 'fixed', 1, false, '1.000', true, 1, false, 1, false, 1, 'consu', false, true, false, 7.0, 'product_465', true, false, 1, 1, (now() at time zone 'UTC'), (now() at time zone 'UTC')) RETURNING id
Traceback (most recent call last):
  File "/opt/openerp/code/8.0/odoo/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"

On Fri, Sep 18, 2015 at 1:22 AM, Olivier Dony <odo@openerp.com> 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




--


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
nils@trobz.com / trobz.com / Skype: nils.hamerlinck