search

Vauxoo / odoo

Fork of Odoo (formerly OpenERP). [This project is not publically mantained just born for internal usage with some little patches] go to official repository on github.com/odoo/odoo
https://www.odoo.com
Other
9 stars 9 forks source link

[IMP] core: mitigate possible deadlock computing fields #447

Closed moylop260 closed 2 years ago

moylop260 commented 3 years ago

If we have computed fields depending on model A But model A have compute fields depending on model B too The possibility to reproduce a deadlock is very high

This change set a timeout in order to avoid locking a record for 14h (real locking time from live production databases)

We could use lock_timeout PG parameter but it will stop the next queries to locked one but the sentence locking will not stop it so the raises will increase

Using statement_timeout will stop any sentence spending more than that time including the SELECT ones not only UPDATE For this reason the parameter is set only in _write method in order to reduce a possible stop for a large SELECT and only stop UPDATE sentences

Valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days)

e.g. Set 20min value for limit to 20 minutes

moylop260 commented 3 years ago

For record

Using a demo database with -i sale,purchase,sale_management,account,sale_subscription Executing ./odoo-bin shell -d DB --no-http --logfile=/tmp/odoo.log --log-level=debug_sql

And running line by line the following code

env['ir.config_parameter'].sudo().set_param('compute_statement_timeout', '1ms')
env.cr.execute("SHOW statement_timeout")
print(env.cr.fetchone()[0])
# 0
sale = env['sale.order'].browse(1)
sale.write({'state': 'draft'})
# QueryCanceled: canceling statement due to statement timeout
sale.write({'state': 'draft'})
# QueryCanceled: canceling statement due to statement timeout
sale.write({'state': 'draft'})
# QueryCanceled: canceling statement due to statement timeout
env.cr.execute("SHOW statement_timeout")
print(env.cr.fetchone()[0])
# 0
env['ir.config_parameter'].sudo().set_param('compute_statement_timeout', '1min')
sale.write({'state': 'draft'})
# True
env.cr.rollback()

The queries executed for sale.write({'state': 'draft'}) with 1ms

SELECT id FROM "sale_order_line" WHERE "order_id" in (1)
UPDATE "sale_order" SET "state"='draft',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1)
SELECT id FROM "sale_order_line" WHERE "order_id" in (1)
SELECT "sale_order"."id" as "id","sale_order"."partner_shipping_id" as "partner_shipping_id","sale_order"."amount_total" as "amount_total","sale_order"."payment_term_id" as "payment_term_id","sale_order"."origin" as "origin","sale_order"."access_token" as "access_token","sale_order"."pricelist_id" as "pricelist_id","sale_order"."require_signature" as "require_signature","sale_order"."currency_rate" as "currency_rate","sale_order"."fiscal_position_id" as "fiscal_position_id","sale_order"."signed_by" as "signed_by","sale_order"."message_main_attachment_id" as "message_main_attachment_id","sale_order"."note" as "note","sale_order"."write_date" as "write_date","sale_order"."client_order_ref" as "client_order_ref","sale_order"."require_payment" as "require_payment","sale_order"."user_id" as "user_id","sale_order"."company_id" as "company_id","sale_order"."amount_untaxed" as "amount_untaxed","sale_order"."reference" as "reference","sale_order"."commitment_date" as "commitment_date","sale_order"."analytic_account_id" as "analytic_account_id","sale_order"."partner_id" as "partner_id","sale_order"."team_id" as "team_id","sale_order"."create_uid" as "create_uid","sale_order"."date_order" as "date_order","sale_order"."state" as "state","sale_order"."create_date" as "create_date","sale_order"."partner_invoice_id" as "partner_invoice_id","sale_order"."sale_order_template_id" as "sale_order_template_id","sale_order"."validity_date" as "validity_date","sale_order"."amount_tax" as "amount_tax","sale_order"."write_uid" as "write_uid","sale_order"."confirmation_date" as "confirmation_date","sale_order"."name" as "name" FROM "sale_order" WHERE "sale_order".id IN (1)
SHOW statement_timeout
SAVEPOINT "4ad9b9ca164411ec8aed1e003a34d891"
SET SESSION statement_timeout='1ms'
RELEASE SAVEPOINT "4ad9b9ca164411ec8aed1e003a34d891"
SAVEPOINT "4ad9e8d2164411ec8aed1e003a34d891"
UPDATE "sale_order_line" SET "state"='draft',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3)
-- bad query: UPDATE "sale_order_line" SET "state"='draft',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3)
-- ERROR: canceling statement due to statement timeout
-- CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."product_product" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
ROLLBACK TO SAVEPOINT "4ad9e8d2164411ec8aed1e003a34d891"
SET SESSION statement_timeout='0'

The queries executed for sale.write({'state': 'draft'}) with 1min

SELECT id FROM "sale_order_line" WHERE "order_id" in (1) 
UPDATE "sale_order" SET "state"='draft',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1) 
SELECT id FROM "sale_order_line" WHERE "order_id" in (1) 
SELECT "sale_order"."id" as "id","sale_order"."partner_shipping_id" as "partner_shipping_id","sale_order"."amount_total" as "amount_total","sale_order"."payment_term_id" as "payment_term_id","sale_order"."origin" as "origin","sale_order"."access_token" as "access_token","sale_order"."pricelist_id" as "pricelist_id","sale_order"."require_signature" as "require_signature","sale_order"."currency_rate" as "currency_rate","sale_order"."fiscal_position_id" as "fiscal_position_id","sale_order"."signed_by" as "signed_by","sale_order"."message_main_attachment_id" as "message_main_attachment_id","sale_order"."note" as "note","sale_order"."write_date" as "write_date","sale_order"."client_order_ref" as "client_order_ref","sale_order"."require_payment" as "require_payment","sale_order"."user_id" as "user_id","sale_order"."company_id" as "company_id","sale_order"."amount_untaxed" as "amount_untaxed","sale_order"."reference" as "reference","sale_order"."commitment_date" as "commitment_date","sale_order"."analytic_account_id" as "analytic_account_id","sale_order"."partner_id" as "partner_id","sale_order"."team_id" as "team_id","sale_order"."create_uid" as "create_uid","sale_order"."date_order" as "date_order","sale_order"."state" as "state","sale_order"."create_date" as "create_date","sale_order"."partner_invoice_id" as "partner_invoice_id","sale_order"."sale_order_template_id" as "sale_order_template_id","sale_order"."validity_date" as "validity_date","sale_order"."amount_tax" as "amount_tax","sale_order"."write_uid" as "write_uid","sale_order"."confirmation_date" as "confirmation_date","sale_order"."name" as "name" FROM "sale_order" WHERE "sale_order".id IN (1) 
SHOW statement_timeout 
SAVEPOINT "06fd6b42164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "06fd6b42164511ec81851e003a34d891" 
SAVEPOINT "06fd8960164511ec81851e003a34d891" 
UPDATE "sale_order_line" SET "state"='draft',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
RELEASE SAVEPOINT "06fd8960164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
SHOW statement_timeout 
SAVEPOINT "06fe0e08164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "06fe0e08164511ec81851e003a34d891" 
SAVEPOINT "06fe325c164511ec81851e003a34d891" 
UPDATE "sale_order_line" SET "qty_delivered_method"='manual',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
RELEASE SAVEPOINT "06fe325c164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
SELECT "sale_order_line"."id" as "id","sale_order_line"."order_id" as "order_id","sale_order_line"."price_unit" as "price_unit","sale_order_line"."currency_id" as "currency_id","sale_order_line"."state" as "state","sale_order_line"."create_date" as "create_date","sale_order_line"."price_reduce_taxexcl" as "price_reduce_taxexcl","sale_order_line"."is_expense" as "is_expense","sale_order_line"."product_id" as "product_id","sale_order_line"."qty_delivered_manual" as "qty_delivered_manual","sale_order_line"."price_subtotal" as "price_subtotal","sale_order_line"."company_id" as "company_id","sale_order_line"."display_type" as "display_type","sale_order_line"."create_uid" as "create_uid","sale_order_line"."write_date" as "write_date","sale_order_line"."discount" as "discount","sale_order_line"."is_downpayment" as "is_downpayment","sale_order_line"."price_tax" as "price_tax","sale_order_line"."product_uom" as "product_uom","sale_order_line"."write_uid" as "write_uid","sale_order_line"."order_partner_id" as "order_partner_id","sale_order_line"."customer_lead" as "customer_lead","sale_order_line"."price_total" as "price_total","sale_order_line"."qty_invoiced" as "qty_invoiced","sale_order_line"."name" as "name","sale_order_line"."product_uom_qty" as "product_uom_qty","sale_order_line"."qty_delivered_method" as "qty_delivered_method","sale_order_line"."price_reduce" as "price_reduce","sale_order_line"."untaxed_amount_invoiced" as "untaxed_amount_invoiced","sale_order_line"."salesman_id" as "salesman_id","sale_order_line"."sequence" as "sequence","sale_order_line"."price_reduce_taxinc" as "price_reduce_taxinc" FROM "sale_order_line" WHERE "sale_order_line".id IN (1, 2, 3) 
SHOW statement_timeout 
SAVEPOINT "06ff8878164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "06ff8878164511ec81851e003a34d891" 
SAVEPOINT "06ffa9b6164511ec81851e003a34d891" 
UPDATE "sale_order_line" SET "qty_delivered"='0.000',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
RELEASE SAVEPOINT "06ffa9b6164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
SHOW statement_timeout 
SAVEPOINT "070049de164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "070049de164511ec81851e003a34d891" 
SAVEPOINT "07007350164511ec81851e003a34d891" 
UPDATE "sale_order_line" SET "qty_to_invoice"='0.000',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
RELEASE SAVEPOINT "07007350164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
SHOW statement_timeout 
SAVEPOINT "070138bc164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "070138bc164511ec81851e003a34d891" 
SAVEPOINT "0701842a164511ec81851e003a34d891" 
SELECT DISTINCT "order_id" FROM "sale_order_line" WHERE "id" IN (1, 2, 3) 
SELECT "sale_order".id FROM "sale_order" WHERE ("sale_order"."id" in (1)) ORDER BY "sale_order"."id"   
UPDATE "sale_order_line" SET "invoice_status"='no',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
SELECT DISTINCT "order_id" FROM "sale_order_line" WHERE "id" IN (1, 2, 3) 
SELECT "sale_order".id FROM "sale_order" WHERE ("sale_order"."id" in (1)) ORDER BY "sale_order"."id"   
RELEASE SAVEPOINT "0701842a164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
            SELECT min("sale_order_line".id) AS id, count("sale_order_line".id) AS "__count" , "sale_order_line"."order_id" as "order_id" ,"sale_order_line"."invoice_status" as "invoice_status" 
            FROM "sale_order_line" LEFT JOIN "sale_order" as "sale_order_line__order_id" ON ("sale_order_line"."order_id" = "sale_order_line__order_id"."id")
            WHERE (("sale_order_line"."order_id" in (1))  AND  "sale_order_line"."product_id" IS NOT NULL)
            GROUP BY "sale_order_line"."order_id","sale_order_line"."invoice_status","sale_order_line__order_id"."date_order","sale_order_line__order_id"."id"
            ORDER BY  "sale_order_line__order_id"."date_order" DESC,"sale_order_line__order_id"."id" DESC ,"invoice_status"
SELECT "account_invoice".id FROM "account_invoice" WHERE ((("account_invoice"."origin"::text like '%SO001%')  AND  ("account_invoice"."company_id" = 1))  AND  ("account_invoice"."type" in ('out_invoice','out_refund'))) ORDER BY "account_invoice"."date_invoice" DESC,"account_invoice"."number" DESC,"account_invoice"."id" DESC  
SHOW statement_timeout 
SAVEPOINT "0705b266164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "0705b266164511ec81851e003a34d891" 
SAVEPOINT "0705df0c164511ec81851e003a34d891" 
UPDATE "sale_order" SET "invoice_status"='no',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1) 
RELEASE SAVEPOINT "0705df0c164511ec81851e003a34d891" 
SET SESSION statement_timeout='0' 
SHOW statement_timeout 
SAVEPOINT "0709e32c164511ec81851e003a34d891" 
SET SESSION statement_timeout='1min' 
RELEASE SAVEPOINT "0709e32c164511ec81851e003a34d891" 
SAVEPOINT "070a0398164511ec81851e003a34d891" 
UPDATE "sale_order_line" SET "untaxed_amount_to_invoice"='0.00',"write_uid"=1,"write_date"=(now() at time zone 'UTC') WHERE id IN (1, 2, 3) 
RELEASE SAVEPOINT "070a0398164511ec81851e003a34d891" 
SET SESSION statement_timeout='0'