Odoo15安装论坛模块时PostgreSQL查询锁异常排查求助

锁定查询信息 #

持有锁的SQL查询如下：

SELECT * FROM ir_translation
          WHERE lang='en_US' AND
                type='model_terms' AND
                name='ir.ui.view,arch_db' AND
                res_id IN (2618)

该查询的res_id=2618是在同一事务中创建的，由于暂无对应翻译数据，查询返回空结果。

ir_translation表结构 #

Table "public.ir_translation"
  Column  |       Type        | Collation | Nullable |                  Default                   
----------+-------------------+-----------+----------+--------------------------------------------
 id       | integer           |           | not null | nextval('ir_translation_id_seq'::regclass)
 name     | character varying |           | not null | 
 res_id   | integer           |           |          | 
 lang     | character varying |           |          | 
 type     | character varying |           |          | 
 src      | text              |           |          | 
 value    | text              |           |          | 
 module   | character varying |           |          | 
 state    | character varying |           |          | 
 comments | text              |           |          | 
Indexes:
    "ir_translation_pkey" PRIMARY KEY, btree (id)
    "ir_translation_code_unique" UNIQUE, btree (type, lang, md5(src)) WHERE type::text = 'code'::text
    "ir_translation_comments_index" btree (comments)
    "ir_translation_model_unique" UNIQUE, btree (type, lang, name, res_id) WHERE type::text = 'model'::text
    "ir_translation_module_index" btree (module)
    "ir_translation_res_id_index" btree (res_id)
    "ir_translation_src_md5" btree (md5(src))
    "ir_translation_type_index" btree (type)
    "ir_translation_unique" UNIQUE, btree (type, name, lang, res_id, md5(src))
Foreign-key constraints:
    "ir_translation_lang_fkey_res_lang" FOREIGN KEY (lang) REFERENCES res_lang(code)

阻塞现象 #

后续3个涉及res_groups_users_rel、ir_model_data等表的查询未被阻塞，但查询website表的语句被上述ir_translation查询阻塞，阻塞的查询语句如下：

SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."google_maps_api_key" as "google_maps_api_key", "website"."user_id" as "user_id", "website"."cdn_activated" as "cdn_activated", "website"."cdn_url" as "cdn_url", "website"."cdn_filters" as "cdn_filters", "website"."homepage_id" as "homepage_id", "website"."custom_code_head" as "custom_code_head", "website"."custom_code_footer" as "custom_code_footer", "website"."robots_txt" as "robots_txt", "website"."theme_id" as "theme_id", "website"."specific_user_account" as "specific_user_account", "website"."auth_signup_uninvited" as "auth_signup_uninvited", "website"."create_uid" as "create_uid", "website"."create_date" as "create_date", "website"."write_uid" as "write_uid", "website"."write_date" as "write_date", "website"."channel_id" as "channel_id", "website"."karma_profile_min" as "karma_profile_min" FROM "website" WHERE "website".id IN (1)

疑问点 #

• 无法理解无直接关联的website表查询为何会被上述ir_translation查询阻塞（已通过lock_monitor视图确认该阻塞关系）
• 理论上所有操作都在同一事务中，但PID显示存在两个进程，想问PostgreSQL是否会为单个游标创建多个进程？

内容的提问来源于stack exchange，提问作者Loïc Faure-Lacroix