Обсуждение: 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 -- edoardo@aspix.it AIM: edoardopn Jabber: edoardopa@talk.google.com tel:075 9142766
Edoardo Panfili <edoardo@aspix.it> 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
Tom Lane wrote: > Edoardo Panfili <edoardo@aspix.it> 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 -- edoardo@aspix.it AIM: edoardopn Jabber: edoardopa@talk.google.com tel:075 9142766
Edoardo Panfili <edoardo@aspix.it> 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
Tom Lane wrote: > Edoardo Panfili <edoardo@aspix.it> 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 -- edoardo@aspix.it AIM: edoardopn Jabber: edoardopa@talk.google.com tel:075 9142766
Edoardo Panfili <edoardo@aspix.it> 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
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.
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 -- edoardo@aspix.it AIM: edoardopn Jabber: edoardopa@talk.google.com tel:075 9142766
Tom Lane wrote: > Edoardo Panfili <edoardo@aspix.it> 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 -- edoardo@aspix.it AIM: edoardopn Jabber: edoardopa@talk.google.com tel:075 9142766