Thread: Odd Locking Problem

I'm having an odd case where my system is locking such that if I insert
into a table during a transaction, if I start a new connection and
transaction, it blocks while trying to do a similar insert until the
first transaction is committed or rolled back.

The schema is rather complex (currently 157 tables, 200 views), and I
still haven't been able to create a small test case. Everything I've
tried so far just works.

The data is private, but the schema is open source, so I probably could
work with someone on it. When I look at the pg_locks table, I seem to be
blocked on:
SELECT * FROM pg_locks WHERE granted = false;
 relation | database | transaction |  pid  |       mode       | granted
----------+----------+-------------+-------+------------------+---------
          |          |     1525932 | 30175 | ShareLock        | f
...

Which if I understand correctly, means that the current transaction is
intentionally blocking waiting for the other transaction to finish.

I'm currently running 8.0.3, but the database was first created under
7.4.? I confirmed this behavior on both systems.

Under what circumstances would this occur?

To try and outline the situation there is a main object table, which is
the root object. It contains a group column which is used for access
rights. There is a groupref table, which keeps track of the group rights
for each user. (Each user has specific insert,update,select rights per
group).

The select rights are enforced by views (the tables are not publicly
accessible, the views join against the groupref table to check for
select permission).
Insert and update rights are validated by BEFORE INSERT triggers.

Most tables references the object table. Basically it is OO, but doesn't
use the postgres inheritance (in our testing postgres inheritance didn't
scale well for deep inheritance, and wasn't able to enforce uniqueness
anyway.) The views present an OO appearance, and behind the scenes
direct table foreign keys maintain referential integrity.

I have checked using RAISE NOTICE and the BEFORE INSERT trigger gets all
the way to the RETURN statement before things hang, so I haven't figured
out what is actually hanging.

I have a bzip'd version of the schema and just enough data to be useful
available here:
http://www.arbash-meinel.com/extras/schema_and_data.sql.bz2

This is the commands to replicate the locking:

-- Connect as postgres

-- Required before any inserts, so that the TEMP env table is
-- created and filled out.
select mf_setup_env();

-- Begin a transaction and insert some data
BEGIN;
INSERT INTO object(vgroup,otype,oname) VALUES ('test',1,'test');

-- Start a new shell, and connect again and do exactly the same thing
-- as the above.
-- It should hang until you either do END/ROLLBACK in the first
-- connection.

Thanks for any help,
John
=:->

On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
> I'm having an odd case where my system is locking such that if I insert
> into a table during a transaction, if I start a new connection and
> transaction, it blocks while trying to do a similar insert until the
> first transaction is committed or rolled back.

Are there foreign keys here?  I can duplicate the problem easily with
them:

-- session 1
create table a (a serial primary key);
create table b (a int references a);
insert into a values (1);

begin;
insert into b values (1);


-- session 2
insert into b values (1);
-- hangs


If I commit on session 1, session 2 is unlocked.

This is a known problem, solved in 8.1.  A workaround for previous
releases is to defer FK checks until commit:

create table b (a int references a initially deferred);

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.

Alvaro Herrera wrote:
> On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
>
>>I'm having an odd case where my system is locking such that if I insert
>>into a table during a transaction, if I start a new connection and
>>transaction, it blocks while trying to do a similar insert until the
>>first transaction is committed or rolled back.
>
>
> Are there foreign keys here?  I can duplicate the problem easily with
> them:
>
> -- session 1
> create table a (a serial primary key);
> create table b (a int references a);
> insert into a values (1);
>
> begin;
> insert into b values (1);
>
>
> -- session 2
> insert into b values (1);
> -- hangs
>

Actually, there are but the insert is occurring into table 'a' not table
'b'.
'a' refers to other tables, but these should not be modified.

>
> If I commit on session 1, session 2 is unlocked.
>
> This is a known problem, solved in 8.1.  A workaround for previous
> releases is to defer FK checks until commit:
>
> create table b (a int references a initially deferred);

I'll try one of the CVS entries and see if it happens there. Good to
hear there has been work done.

John
=:->

>

Alvaro Herrera wrote:
> On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
>

...

>
> This is a known problem, solved in 8.1.  A workaround for previous
> releases is to defer FK checks until commit:

So I don't know exactly what the fix was, but I just tested, and my
problem is indeed fixed with the latest CVS head. It no longer blocks.

>
> create table b (a int references a initially deferred);
>

John
=:->

On Thu, 11 Aug 2005 16:11:58 -0500, John A Meinel
<john@arbash-meinel.com> wrote:
>the insert is occurring into table 'a' not table 'b'.
>'a' refers to other tables, but these should not be modified.

So your "a" is Alvaro's "b", and one of your referenced tables is
Alvaro's "a".  This is further supported by the fact that the problem
doesn't occur with 8.1.

Servus
 Manfred