Обсуждение: exclusive locking on SELECT ?
Greetings, I have 3 clients trying to do a select on the same table (different rows). pgAdmin III Server Status shows the queries and has as mode "Exclusive Lock" for all three. After a long while (10 minutes maybe?) things eventually get unstuck and everything is alright. These clients are selecting 2000 rows from the database. My suspicion is the database servers IO is a bottleneck (running SATA drives, not SCSI). Would fetching less number of rows at a time be better? How can I find out what the best number of rows to fetch at a time are? Is this a deadlock situation? How would I go about diagnosing this situation, and how do I fix it? I'm just using the postgresql-8.2-504.jdbc2.jar on all three clients. I'm also researching and trying to implement the pooling suggested before (trying DBCP first). If that will fix this problem then just disregard this. Thanks in advance, James
On 11-Jan-07, at 4:47 PM, James Neff wrote: > Greetings, > > I have 3 clients trying to do a select on the same table (different > rows). pgAdmin III Server Status shows the queries and has as mode > "Exclusive Lock" for all three. Are you doing select for update ? If so why so many rows ? > > After a long while (10 minutes maybe?) things eventually get > unstuck and everything is alright. > > These clients are selecting 2000 rows from the database. My > suspicion is the database servers IO is a bottleneck (running SATA > drives, not SCSI). Would fetching less number of rows at a time be > better? How can I find out what the best number of rows to fetch > at a time are? > > Is this a deadlock situation? no > > How would I go about diagnosing this situation, and how do I fix it? Have you tuned the server ? > > I'm just using the postgresql-8.2-504.jdbc2.jar on all three clients. > > I'm also researching and trying to implement the pooling suggested > before (trying DBCP first). If that will fix this problem then > just disregard this. > > Thanks in advance, > James > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Dave Cramer wrote: > > On 11-Jan-07, at 4:47 PM, James Neff wrote: > >> Greetings, >> >> I have 3 clients trying to do a select on the same table (different >> rows). pgAdmin III Server Status shows the queries and has as mode >> "Exclusive Lock" for all three. > Are you doing select for update ? If so why so many rows ? > > No updates, just a select. Is there a way (in Java) to only select one row at a time from a resultset, and is this a good idea? The clients are processing rows from this one table in 2000 record chunks. There are about 9 million rows total to process. >> >> How would I go about diagnosing this situation, and how do I fix it? > > Have you tuned the server ? I'm doing a search now on "tuning postgres". Do you have any suggested links that might pertain to my particular situation? Thanks again, James
On 12-Jan-07, at 9:14 AM, James Neff wrote: > Dave Cramer wrote: >> >> On 11-Jan-07, at 4:47 PM, James Neff wrote: >> >>> Greetings, >>> >>> I have 3 clients trying to do a select on the same table >>> (different rows). pgAdmin III Server Status shows the queries >>> and has as mode "Exclusive Lock" for all three. >> Are you doing select for update ? If so why so many rows ? >> >> > > No updates, just a select. Is there a way (in Java) to only select > one row at a time from a resultset, and is this a good idea? The > clients are processing rows from this one table in 2000 record > chunks. There are about 9 million rows total to process. you can setFetchSize to allow the driver to fetch fewer at a time. > > >>> >>> How would I go about diagnosing this situation, and how do I fix it? >> >> Have you tuned the server ? > > I'm doing a search now on "tuning postgres". Do you have any > suggested links that might pertain to my particular situation? Well, I'd have to see exactly what the machine was doing, how big it is, etc. Dave > > Thanks again, > James >