Обсуждение: Mysterious locking problem
Hi, I'm having a misterious locking problem ( caused by a transaction, i think ). But for my understanding of transactions and lock's, this wasnt supposed to appen. I'm using Postgresq 7.1.2 on a Suse 7.x box. I notice the locking problem when there are two 2 different queries running, of different connections( initiated by PHP ). Lets say I have 3 Tables: - Table A, B, C The query that is executed by one of the connections is a select on table B( to get some foreign keys values ) and an insert in table A( just after the select ). The other query that the second connection execute is an insert into table C( to start this insert it does some selects in table B ). Every connection is executed inside a transaction. I have tried getting the internal state of each transaction....but i havent find docs about it( i have seen docs for 7.2.1 - Monitoring ). Can i inspect the state of a running transaction in 7.1.2? Does any one have any hint? Thanks Miguel Carvalho
> I'm having a misterious locking problem ( caused by a transaction, i think ). > But for my understanding of transactions and lock's, this wasnt supposed > to appen. > I'm using Postgresq 7.1.2 on a Suse 7.x box. > > I notice the locking problem when there are two 2 different queries > running, of different connections( initiated by PHP ). > Lets say I have 3 Tables: > > - Table A, B, C > > The query that is executed by one of the connections is a select on table > B( to get some foreign keys values ) and an insert in table A( just after > the select ). > The other query that the second connection execute is an insert into > table C( to start this insert it does some selects in table B ). Given your query things above I'm guessing you've turned on query printing? Are the selects perhaps SELECT ... FOR UPDATE queries going for the same rows in which case I think the second would wait on the first?
Hi, thank's for reppling. > Given your query things above I'm guessing you've turned on query > printing? Query print is off, because that will startve may hardisk verry quickly. > Are the selects perhaps SELECT ... FOR UPDATE queries going I forgot to mention that the selects are plain selects( not select for update ). > for the same rows in which case I think the second would wait on the > first? Can this be a PHP issue? I have noticed the "locking", because the script line that executes the query, doesnt ever return( until i stock the first query ). Regards, Miguel Carvalho
On Tue, 21 May 2002, Miguel Carvalho wrote: > Hi, > > thank's for reppling. > > > Given your query things above I'm guessing you've turned on query > > printing? > > Query print is off, because that will startve may hardisk verry quickly. > > > Are the selects perhaps SELECT ... FOR UPDATE queries going > > I forgot to mention that the selects are plain selects( not select for > update ). You might want to turn query printing on for just short time to see if there are other queries being done. Do you have any foreign keys or triggers on these tables that might be doing so? > > for the same rows in which case I think the second would wait on the > > first? > > Can this be a PHP issue? Seems wierd for it to be. Can you distill what you're doing into a small reproducable example and send enough information to reproduce it?
> > On Tue, 21 May 2002, Miguel Carvalho wrote: > >> Hi, >> >> thank's for reppling. >> >> > Given your query things above I'm guessing you've turned on query >> > printing? >> >> Query print is off, because that will startve may hardisk verry >> quickly. >> >> > Are the selects perhaps SELECT ... FOR UPDATE queries going >> >> I forgot to mention that the selects are plain selects( not select >> for update ). > > You might want to turn query printing on for just short time to see if > there are other queries being done. Ok, i will try it. > Do you have any foreign keys or > triggers on these tables that might be doing so? On one of the tables i have a trigger before insert, but the trigger isnt doing any row or table locking. The tirgger is just executing some validations( plain selects ). Can this be the problem? > >> > for the same rows in which case I think the second would wait on the >> > first? I agree with you. >> >> Can this be a PHP issue? > > Seems wierd for it to be. Ok, i am going to dig into the problem. > Can you distill what you're doing into a small reproducable example and > send enough information to reproduce it? I'am gone try. Regards, Miguel Carvalho