Обсуждение: [araman@india-today.com: locking problem with JDBC (suspicion)]
hi guys I posted this a few days back.. Reposting because i didnt get any answer.. Can any one point out whats wrong or if any has been facing a similar problem.. Thanx Anand ----- Forwarded message from Anand Raman <araman@india-today.com> ----- From: Anand Raman <araman@india-today.com> Date: Sat, 10 Feb 2001 22:46:11 +0530 To: postgresql <pgsql-general@postgresql.org> Subject: [GENERAL] locking problem with JDBC (suspicion) hi guys I am facing problems with locks occasionally when using postgresql with jdbc drivers.. I typically update a table exhibits which has 2 foreign keys from artits table and a foreign key from atoday_users.. Sometimes this query simply hangs waiting for some lock to be released. This is a sample log entry which is generated when the process runs smoothly.. StartTransactionCommand query: insert into exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) values (611,1001,'trial sake',null,157,null) ProcessQuery query: SELECT oid FROM "artists" WHERE "artist_id" = $1 FOR UPDATE OF "artists" query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF "atoday_users" CommitTransactionCommand this is the log entry which is generated when the process hangs.. StartTransactionCommand query: insert into exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) values (613,1001,'Painiting 1',null,56,null) ProcessQuery <<HANGS AFTER THIS>> What could be the cause of this problem.. There arent many concurrent transaction on the db engine.. Infact i am the only one who is using this application now.. ** MORE IMPORTANTG QUESTION ** Also why should insert into exhibits table lock a few rows from artists and atoday_user for UPDATE.. Could this be the cause of the problem.. I am using 702.. Thanks Anand ----- End forwarded message -----
From: "Anand Raman" <araman@india-today.com> > I am facing problems with locks occasionally when using postgresql with > jdbc drivers.. > > I typically update a table exhibits which has 2 foreign keys from > artits table and a foreign key from atoday_users.. Sometimes this query simply hangs waiting for some > lock to be released. Might be the foreign keys. > This is a sample log entry which is generated when the process runs > smoothly.. > > StartTransactionCommand > query: insert into exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) values (611,1001,'trial sake',null,157,null) > ProcessQuery > query: SELECT oid FROM "artists" WHERE "artist_id" = $1 FOR UPDATE OF "artists" > query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF "atoday_users" > CommitTransactionCommand SELECT ... FOR UPDATE will obviously lock the relevant records here until the end of transaction, but you don't appear to be updating the records. I'm guessing this is just an example. > this is the log entry which is generated when the process hangs.. > StartTransactionCommand > query: insert into exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) values (613,1001,'Painiting 1',null,56,null) > ProcessQuery > <<HANGS AFTER THIS>> Assuming you have described everything that's going on, it must be something to do with either the foreign keys or the jdbc driver. > ** MORE IMPORTANTG QUESTION ** > Also why should insert into exhibits table lock a few rows from artists > and atoday_user for UPDATE.. Could this be the cause of the problem.. Well, foreign keys enforce constraints so it's not unreasonable that the lock might extend to the referenced tables. Could you try it from the command-line? Open up one psql session, begin a transaction with the artists update and then try the exhibits update in a second session - it should hang until the first transaction completes/rolls back or a timeout occurs. If this reproduces your problem, we'll know where we are. If it doesn't I'd guess it's in the JDBC drivers. HTH - Richard Huxton
HI richard Thanks for your response.. No this is not a example.. These are the ACTUAL SERVER LOG which got generated in my postgres log file on a debug level 2 (-d2) I will also try the 2 session thing which u suggested and get back.. Thanks Anand On Tue, Feb 13, 2001 at 11:54:04AM -0000, Richard Huxton wrote: >From: "Anand Raman" <araman@india-today.com> > > >> I am facing problems with locks occasionally when using postgresql with >> jdbc drivers.. >> >> I typically update a table exhibits which has 2 foreign keys from >> artits table and a foreign key from atoday_users.. Sometimes this query >simply hangs waiting for some >> lock to be released. > >Might be the foreign keys. > >> This is a sample log entry which is generated when the process runs >> smoothly.. >> >> StartTransactionCommand >> query: insert into >exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) >values (611,1001,'trial sake',null,157,null) >> ProcessQuery >> query: SELECT oid FROM "artists" WHERE "artist_id" = $1 FOR UPDATE OF >"artists" >> query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF >"atoday_users" >> CommitTransactionCommand > >SELECT ... FOR UPDATE will obviously lock the relevant records here until >the end of transaction, but you don't appear to be updating the records. I'm >guessing this is just an example. > >> this is the log entry which is generated when the process hangs.. >> StartTransactionCommand >> query: insert into >exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2) >values (613,1001,'Painiting 1',null,56,null) >> ProcessQuery >> <<HANGS AFTER THIS>> > >Assuming you have described everything that's going on, it must be something >to do with either the foreign keys or the jdbc driver. >> ** MORE IMPORTANTG QUESTION ** >> Also why should insert into exhibits table lock a few rows from artists >> and atoday_user for UPDATE.. Could this be the cause of the problem.. > >Well, foreign keys enforce constraints so it's not unreasonable that the >lock might extend to the referenced tables. > >Could you try it from the command-line? Open up one psql session, begin a >transaction with the artists update and then try the exhibits update in a >second session - it should hang until the first transaction completes/rolls >back or a timeout occurs. > >If this reproduces your problem, we'll know where we are. If it doesn't I'd >guess it's in the JDBC drivers. > >HTH > >- Richard Huxton >
"Richard Huxton" <dev@archonet.com> writes:
> Well, foreign keys enforce constraints so it's not unreasonable that the
> lock might extend to the referenced tables.
Precisely. The SELECT FOR UPDATE is done to ensure that the referenced
rows don't go away before the update of the referencing table commits.
I'm guessing that Anand's not shown us all the operations involved,
and that when things hang up it's because there are two different
transactions trying to lock overlapping sets of referenced rows.
Hard to tell from the info we have, however.
regards, tom lane
hi guys On Tue, Feb 13, 2001 at 10:07:57AM -0500, Tom Lane wrote: >"Richard Huxton" <dev@archonet.com> writes: >> Well, foreign keys enforce constraints so it's not unreasonable that the >> lock might extend to the referenced tables. > thanx tom I got this point after pondering over it for some time.. >Precisely. The SELECT FOR UPDATE is done to ensure that the referenced >rows don't go away before the update of the referencing table commits. > >I'm guessing that Anand's not shown us all the operations involved, >and that when things hang up it's because there are two different >transactions trying to lock overlapping sets of referenced rows. >Hard to tell from the info we have, however. as far as the entire picture is concerned here it comes.. EXHIBITS ------> EXHIBIT_DISTRIBUTIONS --- EXHIBIT_PRICES where EXHIBITS is the primary table and has a one to many relationaship with EXHIBIT_DISTRIBUTIONS. EXHIBIT_DISTRIBUTIONS has a one to one relationship with EXHIBIT_PRICES. In my web applicaiton i am first inserting the rows into EXHIBITS table and in the next page ask the user to insert details regarding the EXHIBIT_DISTRIBUTIONS. I pass the primary key from the first form to the second and subsequently use it for inserting rows in EXHIBIT_DISTRIBUTIONS. The entire process works smoothly for the first few time.. However it hangs when i repeat the process for more than 3 times.. I fail to understand if there is a problem then it would manifest itself in the very first time.. HOwever why should it show upon the third insert.. I can attach the postgres log files and the creation scripts if it helps.. Thanks for any suggestion.. Anand > > regards, tom lane
hi guys Just as a after thought, will a explict commit help me out of this situation.. U know I fear that maybe some DML statement escaped commit however it is a very remote possibility Thanx Anand Raman On Tue, Feb 13, 2001 at 09:14:49PM +0530, Anand Raman wrote: >hi guys > >On Tue, Feb 13, 2001 at 10:07:57AM -0500, Tom Lane wrote: >>"Richard Huxton" <dev@archonet.com> writes: >>> Well, foreign keys enforce constraints so it's not unreasonable that the >>> lock might extend to the referenced tables. >> > >thanx tom >I got this point after pondering over it for some time.. > > >>Precisely. The SELECT FOR UPDATE is done to ensure that the referenced >>rows don't go away before the update of the referencing table commits. >> > >>I'm guessing that Anand's not shown us all the operations involved, >>and that when things hang up it's because there are two different >>transactions trying to lock overlapping sets of referenced rows. >>Hard to tell from the info we have, however. > >as far as the entire picture is concerned here it comes.. > >EXHIBITS ------> EXHIBIT_DISTRIBUTIONS --- EXHIBIT_PRICES > >where EXHIBITS is the primary table and has a one to many relationaship >with EXHIBIT_DISTRIBUTIONS. EXHIBIT_DISTRIBUTIONS has a one to one >relationship with EXHIBIT_PRICES. > >In my web applicaiton i am first inserting the rows into EXHIBITS table >and in the next page ask the user to insert details regarding the >EXHIBIT_DISTRIBUTIONS. I pass the primary key from the first form to the >second and subsequently use it for inserting rows in >EXHIBIT_DISTRIBUTIONS. > >The entire process works smoothly for the first few time.. However it >hangs when i repeat the process for more than 3 times.. > >I fail to understand if there is a problem then it would manifest itself >in the very first time.. HOwever why should it show upon the third >insert.. > >I can attach the postgres log files and the creation scripts if it helps.. > >Thanks for any suggestion.. > >Anand >> >> regards, tom lane
Hi Tom and others I have been trying to find a pattern to this problem and I have failed to find any.. In a recently conducted test i could insert 6 records without and problems so far ... However some other time i am able to insert just as many as 3 records.. I need one more clarifications.. query: insert into exhibit_distributions (exhibit_distribution_id, exhibit_id, created_by, exhibit_type_id, medium, image_path_small,image_path_big, length, breadth, w idth, dimensional_aspect, unframed_volume_weight, framed_volume_weight, override_vw_computation, exhibit_code, exhibit_options,unframed_exhibit_restriction, framed_exhibit_restriction, up_for_sale, gallery_id, site_section) values (717,619,1001,3,'watercolor','test','test',97.0,100.0,null,'cms',2.0,24.416,'f',null,'NULL','WORLD','WORLD','T',52,'GALLERY') ProcessQuery query: SELECT oid FROM "gallery" WHERE "gallery_id" = $1 FOR UPDATE OF "gallery" query: SELECT oid FROM "exhibit_types" WHERE "exhibit_type_id" = $1 FOR UPDATE OF "exhibit_types" query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF "atoday_users" query: SELECT oid FROM "exhibits" WHERE "exhibit_id" = $1 FOR UPDATE OF "exhibits" CommitTransactionCommand StartTransactionCommand query: insert into exhibit_prices(exhibit_price_id, exhibit_distribution_id, unframed_price_inr, framed_price_inr, unframed_crate_price_inr,framed_crate_price_inr, tax_percent, calculate_shipping_for) values(317,717,12000.0,25000.0,150.0,2000.0,10.0,'WORLD') ProcessQuery query: SELECT oid FROM "exhibit_distributions" WHERE "exhibit_distribution_id" = $1 FOR UPDATE OF "exhibit_distributions" CommitTransactionCommand StartTransactionCommand query: insert into inventory(exhibit_distribution_id,quantity,quantity_available) values(717,1,1) ProcessQuery CommitTransactionCommand StartTransactionCommand query: commit I actually use the 2 insert statements in a transaction.. However the log file is outputting a CommitTransactionCommand after every insert.. Is it true that the data is committed after every insert or only when i commit the entire transaction.., Thanks Anand On Tue, Feb 13, 2001 at 09:14:49PM +0530, Anand Raman wrote: >hi guys > >On Tue, Feb 13, 2001 at 10:07:57AM -0500, Tom Lane wrote: >>"Richard Huxton" <dev@archonet.com> writes: >>> Well, foreign keys enforce constraints so it's not unreasonable that the >>> lock might extend to the referenced tables. >> > >thanx tom >I got this point after pondering over it for some time.. > > >>Precisely. The SELECT FOR UPDATE is done to ensure that the referenced >>rows don't go away before the update of the referencing table commits. >> > >>I'm guessing that Anand's not shown us all the operations involved, >>and that when things hang up it's because there are two different >>transactions trying to lock overlapping sets of referenced rows. >>Hard to tell from the info we have, however. > >as far as the entire picture is concerned here it comes.. > >EXHIBITS ------> EXHIBIT_DISTRIBUTIONS --- EXHIBIT_PRICES > >where EXHIBITS is the primary table and has a one to many relationaship >with EXHIBIT_DISTRIBUTIONS. EXHIBIT_DISTRIBUTIONS has a one to one >relationship with EXHIBIT_PRICES. > >In my web applicaiton i am first inserting the rows into EXHIBITS table >and in the next page ask the user to insert details regarding the >EXHIBIT_DISTRIBUTIONS. I pass the primary key from the first form to the >second and subsequently use it for inserting rows in >EXHIBIT_DISTRIBUTIONS. > >The entire process works smoothly for the first few time.. However it >hangs when i repeat the process for more than 3 times.. > >I fail to understand if there is a problem then it would manifest itself >in the very first time.. HOwever why should it show upon the third >insert.. > >I can attach the postgres log files and the creation scripts if it helps.. > >Thanks for any suggestion.. > >Anand >> >> regards, tom lane
From: "Anand Raman" <araman@india-today.com> > Hi Tom and others > > I have been trying to find a pattern to this problem and I have failed > to find any.. In a recently conducted test i could insert 6 records > without and problems so far ... However some other time i am able to > insert just as many as 3 records.. > > I need one more clarifications.. > [snipped initial query] > query: SELECT oid FROM "gallery" WHERE "gallery_id" = $1 FOR UPDATE OF "gallery" > query: SELECT oid FROM "exhibit_types" WHERE "exhibit_type_id" = $1 FOR UPDATE OF "exhibit_types" > query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF "atoday_users" > query: SELECT oid FROM "exhibits" WHERE "exhibit_id" = $1 FOR UPDATE OF "exhibits" You don't seem to be updating gallery,exhibit_types,atoday_users,exhibits in the following queries. Are you sure you want SELECT...FOR UPDATE here? > CommitTransactionCommand > StartTransactionCommand > query: insert into exhibit_prices(exhibit_price_id, exhibit_distribution_id, unframed_price_inr, framed_price_inr, unframed_crate_price_inr, framed_crate_price_inr, tax_percent, calculate_shipping_for) values(317,717,12000.0,25000.0,150.0,2000.0,10.0,'WORLD') > ProcessQuery > query: SELECT oid FROM "exhibit_distributions" WHERE "exhibit_distribution_id" = $1 FOR UPDATE OF "exhibit_distributions" > CommitTransactionCommand > StartTransactionCommand > query: insert into inventory(exhibit_distribution_id,quantity,quantity_available) values(717,1,1) > ProcessQuery > CommitTransactionCommand > StartTransactionCommand > query: commit > > > I actually use the 2 insert statements in a transaction.. However the > log file is outputting a CommitTransactionCommand after every insert.. > Is it true that the data is committed after every insert or only when i > commit the entire transaction.., > > Thanks > Anand You probably have an autocommit flag set to true. I don't use JDBC myself but there is something similar in ODBC. If that's the case though the SELECT...FOR UPDATE stuff should expire at the end of the transaction, so it can't cause locking problems. As you noted earlier it sounds like a transaction *isn't* being completed If you can dump the database schema (pg_dump -s) and put together some sample queries I'll be happy to see if I can reproduce the problem. Bear in mind that I'll be running from the psql command-line and it might take me a day or so to get back to you. If the files are big, feel free to send them to me direct rather than via the list. - Richard Huxton