In this thread I will post the deadlocks I encountered on 5.3 on a fairly busy site which I hope to get fixed using the method Gustav and Rocael suggested earlier (
https://openacs.org/forums/message-view?message_id=461759). If you have an idea what could be done about the deadlocks apart from locking acs_objects in share mode, I would be happy 😊.
Do we need to put the share lock everywhere where we have an insert into acs_objects ?
=======
Error: Ns_PgExec: result status: 7 message: ERROR: deadlock detected
DETAIL: Process 21202 waits for ShareLock on transaction 297574; blocked by process 21163.
Process 21163 waits for ShareRowExclusiveLock on relation 17591 of database 17233; blocked by process 21202.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."acs_objects" x WHERE "object_id" = $1 FOR UPDATE OF x"
SQL statement "insert into acs_object_context_index (object_id, ancestor_id, n_generations) select $1 as object_id, ancestor_id, n_generations + 1 as n_generations from acs_object_context_index where object_id = $2 "
PL/pgSQL function "acs_objects_context_id_in_tr" line 10 at SQL statement
SQL statement "insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )"
PL/pgSQL function "acs_object__new" line 40 at SQL statement
PL/pgSQL function "acs_rel__new" line 13 at assignment
PL/pgSQL function "membership_rel__new" line 11 at assignment
========
Error: Ns_PgExec: result status: 7 message: ERROR: deadlock detected
DETAIL: Process 21204 waits for ShareLock on transaction 145694; blocked by process 21163.
Process 21163 waits for ShareLock on transaction 145856; blocked by process 21204.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."acs_objects" x WHERE "object_id" = $1 FOR UPDATE OF x"
SQL statement "insert into acs_object_context_index (object_id, ancestor_id, n_generations) select $1 as object_id, ancestor_id, n_generations + 1 as n_generations from acs_object_context_index where object_id = $2 "
PL/pgSQL function "acs_objects_context_id_in_tr" line 10 at SQL statement
SQL statement "insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )"
PL/pgSQL function "acs_object__new" line 40 at SQL statement
PL/pgSQL function "content_revision__new" line 30 at assignment
PL/pgSQL function "content_revision__new" line 14 at return
PL/pgSQL function "contact_party_revision__new" line 18 at assignment
============
Error: Ns_PgExec: result status: 7 message: ERROR: deadlock detected
DETAIL: Process 21203 waits for ShareLock on transaction 279773; blocked by process 21163.
Process 21163 waits for ShareRowExclusiveLock on relation 17591 of database 17233; blocked by process 21203.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."acs_objects" x WHERE "object_id" = $1 FOR UPDATE OF x"
SQL statement "insert into acs_object_context_index (object_id, ancestor_id, n_generations) select $1 as object_id, ancestor_id, n_generations + 1 as n_generations from acs_object_context_index where object_id = $2 "
PL/pgSQL function "acs_objects_context_id_in_tr" line 10 at SQL statement
SQL statement "insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )"
PL/pgSQL function "acs_object__new" line 40 at SQL statement
PL/pgSQL function "content_item__new" line 85 at assignment
PL/pgSQL function "content_folder__new" line 34 at assignment