Re: begin transaction locks out other connections

From: Ivano Luberti <luberti(at)archicoop(dot)it>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: begin transaction locks out other connections
Date: 2008-04-10 13:48:46
Message-ID: 47FE1ABE.9090207@archicoop.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Richard, I was convinced that it was not necessary to provide detailed
SQL to not introduce any noise in the information I was giving to the list.
Anyway you proved right at last, since trying to sample some sql to make
others able to reproduce the problem we were also able to identify the
cause of the problem.

If two insert statements ST1 and ST2, to which are attached triggers
that create a postgreSQL schema with the same name, are executed in two
concurrent transaction T1 and T2 started from two different connections
C1 and C2, then ST2 must wait until T1 has ended (rolled back or
commited) before being executed. By consequence the client executing ST2
hangs until T1 has ended.

The fact that both statement try to create a schema with the same name
make the second one to hang.

Now after that we have found in a PostgreSQL book we have that when in
the scenario above the 2 statements try to insert a row in a table with
the same primary key the second statement hangs.

So in some way this behavior is documented so it is not a bug.
Anyway I am a little surprised by this thing cause I thought that in a
case like this the habgs should happen only at commit/rollback time.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message William Temperley 2008-04-10 14:06:18 percentile rank query
Previous Message Volkan YAZICI 2008-04-10 13:29:38 Re: pl/java on Solaris