Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili <[EMAIL PROTECTED]> writes: I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Try turning on statement logging in the server. Looking at the sequence of SQL commands actually issued to the server by the different clients would probably be informative. I did some debug. I did non consider the execution of a second transaction inside the first, this is my error. The only way to avoid problem with my code is to lower the lock level, I can't avoid the nesting of the transactions. The systems works anyway two hours ago, I can't figure how to reproduce that occasion. thanks a lot to all Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] strange (maybe) behaviour of table lock
Scott Marlowe wrote: On Fri, 2006-12-08 at 09:16, Edoardo Panfili wrote: I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do "LOCK table,table2,table3,table4" then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. Before we go any further, what are you trying to accomplish by this lock? Perhaps there's a better "postgresqlish" approach than a table lock. You are right, also Tom said that. At a lower level of lock all goes well but I'd like to know what is going wrong. This is a bug of my code (obviously) and I am investigating. thak you Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] strange (maybe) behaviour of table lock
On Fri, 2006-12-08 at 09:16, Edoardo Panfili wrote: > I have a question regarding a strange behaviour (for me, maybe that this > is desidered feature) of LOCK on tables. I am using postgres 8.2 > > I have a servlet that uses connection pools. > The servlet do "LOCK table,table2,table3,table4" > then do some select (I am testing the code, I will put the update in the > future) an then I close instruction and connection. Before we go any further, what are you trying to accomplish by this lock? Perhaps there's a better "postgresqlish" approach than a table lock. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili <[EMAIL PROTECTED]> writes: > I spend many time to explain the bahaviour of the system: > I some occasions the system use another connection to retrieve some > information during the main connection. This explain the hangs but... > why sometimes the system works. > I do more tests. Try turning on statement logging in the server. Looking at the sequence of SQL commands actually issued to the server by the different clients would probably be informative. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili <[EMAIL PROTECTED]> writes: It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? Um, possibly not, if you're using connection-pooling software ... and even if you're not, I think closing the connection is asynchronous; it'd be possible to establish a new connection before the old one has terminated and released its locks. Tnaks again. Rather than closing the connection, I think you need to do something explicit to commit your transaction. I use connection.commit(); I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Thanks a lot again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili <[EMAIL PROTECTED]> writes: > It seems that if I put some delay between calls to the servlet all goes > well. I can change lock level but ther is something wrong. > Obviously I am doiung something wrong. To unlock the tables is not > sufficient close the Statement and the Connection? Um, possibly not, if you're using connection-pooling software ... and even if you're not, I think closing the connection is asynchronous; it'd be possible to establish a new connection before the old one has terminated and released its locks. Rather than closing the connection, I think you need to do something explicit to commit your transaction. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili <[EMAIL PROTECTED]> writes: I have a servlet that uses connection pools. The servlet do "LOCK table,table2,table3,table4" then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use "psql" to do the last query (the one that hangs the system) of the servlet, also psql hangs. Look into the pg_locks view to find out what it's blocked on. Note that LOCK by default gets an ACCESS EXCLUSIVE lock, which means it will be blocked by *any* pre-existing access, even an open transaction that merely read the table awhile back. Are you sure you really need such a strong lock? I don't need a "ACCESS EXCLUSIVE" thanks a lot for your help. I luk at pg_locks, when my system hangs there are (it's right) loocked tables. It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? tanks again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili <[EMAIL PROTECTED]> writes: > I have a servlet that uses connection pools. > The servlet do "LOCK table,table2,table3,table4" > then do some select (I am testing the code, I will put the update in the > future) an then I close instruction and connection. > The first 4 executions of the servlet goes well, the 5th hangs. > Afther that I use "psql" to do the last query (the one that hangs the > system) of the servlet, also psql hangs. Look into the pg_locks view to find out what it's blocked on. Note that LOCK by default gets an ACCESS EXCLUSIVE lock, which means it will be blocked by *any* pre-existing access, even an open transaction that merely read the table awhile back. Are you sure you really need such a strong lock? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] strange (maybe) behaviour of table lock
I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do "LOCK table,table2,table3,table4" then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use "psql" to do the last query (the one that hangs the system) of the servlet, also psql hangs. I did a modify of the servlet "LOCK table,table2,table3,table4 IN EXCLUSIVE MODE" and no other modifications. I stop and restart my system, all works well. Can someone tell me if this is a desidered behaviour? (and if is possible why). If may help I can post all the queryes and table structure, they are a little long. Thank you Edoardo Panfili -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
