Community mailing list archives
Re: Odoo Performance and concurrency locksby
- 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 <firstname.lastname@example.org> 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) . 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.  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."