Обсуждение: PL/PgSQL, Inheritance, Locks, and Deadlocks
I apologize for the broadbrush subject, but I've been dealing with some anomalies that seem somewhat interrelated. In the latest manifestation, I have a stored procedure that is designed to update some counts on a table that is inherited from another table. The basic structure is a superclass user table that is broken down into several subclass user tables. There is an account table and a group table, each of which store some statistics. Each subclass user table is linked to a group through a linking table that is itself a subclass. Each subclass user table also has its own stored procedure for updating statistics. What I find in running this is that the stored procedure attempts to acquire locks on linking tables from unrelated subclasses. I don't see anything that would cause this. The idea is to preserve an isolation among the different user subclasses. Here is the stored procedure (with proprietary identifiers altered; I hope I haven't introduced inconsistencies in this process...): DECLARE v_group record; v_group_id groups.group_id%TYPE; v_user_count1 users.count1%TYPE; v_group_count1 groups.count1%TYPE; v_group_count2 groups.count2%TYPE; v_group_count3 groups.count2%TYPE; BEGIN SELECT INTO v_user_count1 COUNT( * ) FROM ONLY users1 WHERE user_status_id = '1' AND user_is_deleted IS FALSE; UPDATE accounts SET count1 = v_user_count1 WHERE account_id = '1'; FOR v_group IN SELECT DISTINCT group_id FROM users1_groups LOOP SELECT INTO v_group_count1 COUNT( * ) FROM users1_groups AS ug, users1 AS u WHERE ug.user_id = u.user_id AND ug.group_id = v_group.group_id AND u.user_status_id = '1'; SELECT INTO v_group_count2 COUNT( * ) FROM users1_groups AS ug, users1 AS u WHERE ug.user_id = u.user_id AND ug.group_id = v_group.group_id AND u.user_status_id = '2'; SELECT INTO v_group_count3 COUNT( * ) FROM users1_groups AS ug, users1 AS u WHERE ug.user_id = u.user_id AND ug.group_id = v_group.group_id AND u.user_status_id = '3'; UPDATE groups SET count1 = v_group_count1, count2 = v_group_count2, count3 = v_group_count3 WHERE group_id = v_group.group_id; END LOOP; RETURN; END; For instance, when run, this stored procedure could try to acquire a lock on users2_groups despite not directly referencing it. In a somewhat related issue, I frequently encounter deadlocks while creating various pieces of the inheritance structure -- including tables and triggers -- when adding new user types. During these deadlock situations, pieces of the subclasses seem to be waiting for locks in other pieces that should be unrelated. Unfortunately, I've had a difficult time isolating a reproducible deadlock scenario. In fact, tips for doing so are welcome. I realize that inheritance is an incomplete implementation in postgres, but I'm seeing behavior that I definitely wouldn't expect given (the limited amount of) what I know about the pieces that are implemented. Does anyone have insight into why the above procedure would try to acquire locks not specifically referenced or why a data model with heavy usage of inheritance would be prone to deadlock situations in CREATE statements? Version information: PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC 2.95.4 -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
"Thomas F.O'Connell" <tfo@sitening.com> writes: > UPDATE groups > SET count1 = v_group_count1, count2 = v_group_count2, count3 = > > For instance, when run, this stored procedure could try to acquire a lock on > users2_groups despite not directly referencing it. Does the users2_groups contain a foreign key reference to the groups table? If so then if you need to update the groups table regularly you'll want an index on the referring column. Otherwise in order to check the constraint Postgres needs to do a sequential scan of the referring table to make sure your update doesn't break a reference. I don't know how this plays with locks though. -- greg
The linking table is a pure linking table. It has a user_id and a group_id, each a foreign key. The user_id ties to the appropriate subclass user table. The group_id ties to the groups table, which is not part of an inheritance hierarchy. A multicolumn primary key covers both foreign keys in the linking table, and the secondary column of the key also has its own index. I'm more concerned with the locking, which is thoroughly unexpected behavior to me. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 2, 2005, at 12:03 AM, Greg Stark wrote: > > "Thomas F.O'Connell" <tfo@sitening.com> writes: > >> UPDATE groups >> SET count1 = v_group_count1, count2 = >> v_group_count2, count3 = > >> >> For instance, when run, this stored procedure could try to acquire a >> lock on >> users2_groups despite not directly referencing it. > > Does the users2_groups contain a foreign key reference to the groups > table? If > so then if you need to update the groups table regularly you'll want > an index > on the referring column. Otherwise in order to check the constraint > Postgres > needs to do a sequential scan of the referring table to make sure your > update > doesn't break a reference. > > I don't know how this plays with locks though. > > -- > greg > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
"Thomas F.O'Connell" <tfo@sitening.com> writes: > The linking table is a pure linking table. It has a user_id and a > group_id, each a foreign key. The user_id ties to the appropriate > subclass user table. The group_id ties to the groups table, which is > not part of an inheritance hierarchy. A multicolumn primary key covers > both foreign keys in the linking table, and the secondary column of the > key also has its own index. Inserts/updates in a table that has a foreign key result in locks on the referenced rows in the master table. Could this explain your problem? regards, tom lane
Doubtful, because users never share groups, so even though the groups table is not part of an inheritance hierarchy, there shouldn't be any overlap between foreign keys in the users1_groups table and the users2_groups table in the groups table. users1_groups links all users in the users1 subclass to groups that will be completely distinct from the groups in which users2 users could be categorized. We were seeing, for instance, the stored procedure I posted, which was unique to users1, acquire an AccessShareLock on the users2_groups table. And as it ran (which took a while, since it does several counts), it seemed to acquire locks on a few different linking tables from itself (e.g., users3_groups and users4_groups, as well). The extra locks it was acquiring seemed to be related to some of the deadlocks I've been seeing during CREATE statements (during standard operation of the database) on a variety of the subclass tables (both user tables and linking tables). -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 2, 2005, at 9:41 AM, Tom Lane wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: >> The linking table is a pure linking table. It has a user_id and a >> group_id, each a foreign key. The user_id ties to the appropriate >> subclass user table. The group_id ties to the groups table, which is >> not part of an inheritance hierarchy. A multicolumn primary key covers >> both foreign keys in the linking table, and the secondary column of >> the >> key also has its own index. > > Inserts/updates in a table that has a foreign key result in locks on > the > referenced rows in the master table. Could this explain your problem? > > regards, tom lane
One thing that is curious, though, is that when the AccessShareLock is acquired by the stored procedure on an unrelated linking table, there is also an AccessShareLock acquired on the primary key of the groups table. The latter lock is understandable, but why would the procedure need any locks whatsoever on linking tables on which it has no direct effect (either reading or writing)? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 2, 2005, at 9:41 AM, Tom Lane wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: >> The linking table is a pure linking table. It has a user_id and a >> group_id, each a foreign key. The user_id ties to the appropriate >> subclass user table. The group_id ties to the groups table, which is >> not part of an inheritance hierarchy. A multicolumn primary key covers >> both foreign keys in the linking table, and the secondary column of >> the >> key also has its own index. > > Inserts/updates in a table that has a foreign key result in locks on > the > referenced rows in the master table. Could this explain your problem? > > regards, tom lane