Thread: begin transaction locks out other connections
Hello everybody, I have searched for a long time about this issue but I was not able to find what is the cause. Maybe because I have not been able to identify the right keywords. We are experiencing the following problem using postgres8.3 and postgres8.1 On client1 we execute begin; insert into table1 and a message of correct execution for both statements is returned On client2 we execute begin; insert into table2 and the client receives a correct execution message for the begin stmt but waits for the insert statement until client1 execute either a commit or a rollback. The same happens with an insert on client1 and an update on client2 . While there is no problem with an insert on client1 and a select on client2. We have experienced the same problem either using pgAdmin as a client or the jdbc driver. So it seems the problem is on the db server: we experienced the same problem on different installations of the same server on different operative system. Of course I'm colling this a problem beacuse there would be no need of transaction support if request would be strictly serialized. Thank to anyone has payed attention to this message. _ _ <http://www.archicoop.it/>__ <http://www.archicoop.it/>
Ivano Luberti wrote: > On client1 we execute > > begin; > insert into table1 > > and a message of correct execution for both statements is returned > > On client2 we execute > > begin; > insert into table2 > > and the client receives a correct execution message for the begin stmt > but waits for the insert statement until client1 execute either a commit > or a rollback. These aren't valid statements, so you can't be getting a "success" message. You're going to have to give details of what you're doing if you want someone to tell you what is being locked and why. -- Richard Huxton Archonet Ltd
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.
On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote: > 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. > I think that's because Postgres does not have deferred constraint checks. They are checked at the execution time, instead of commit time. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote: > On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote: > > I think that's because Postgres does not have deferred constraint checks. I believe it does. See http://www.postgresql.org/docs/8.3/interactive/sql-set-constraints.html and the DEFERRABLE keyword in CREATE TABLE. Or am I missing something here?
Dennis Brakhane wrote: > On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee > <pavan.deolasee@gmail.com> wrote: > >> On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote: >> >> I think that's because Postgres does not have deferred constraint checks. >> > > I believe it does. See > http://www.postgresql.org/docs/8.3/interactive/sql-set-constraints.html > and the DEFERRABLE keyword in CREATE TABLE. > > Or am I missing something here? > > As far as I know UNIQUE and CHECK constraints cannot be deferrable; only FOREIGN KEY constraints can be deferrable. You can use a CONSTRAINT TRIGGER to emulate others though. I'm told some other databases can defer UNIQUE constraint checks, but I haven't the foggiest how that can work in a remotely sane way. Wouldn't a deferred UNIQUE constraint be useless to the query planner (which can't trust that the data is really unique right now) and cause confusing behaviour with scalar subqueries (that might suddenly not return a single result) and stored procedures that rely on the unique constraint? I guess the same thing applies to a deferred foreign key constraint, really - you can't actually trust it in any context where you're modifying the data involved. It just seems a lot simpler to think about the effects of deferred foreign key constraints. -- Craig Ringer
On Thu, Apr 10, 2008 at 11:29 PM, Dennis Brakhane <brakhane@googlemail.com> wrote: > > I believe it does. See > http://www.postgresql.org/docs/8.3/interactive/sql-set-constraints.html > and the DEFERRABLE keyword in CREATE TABLE. > > Or am I missing something here? > Only foreign key contrains checks (and triggers) can be deferred, not the primary or unique key checks. See the following statement in the same doc page: "Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable. Triggers that are declared as "constraint triggers" are also affected." Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com