Обсуждение: Lockup fetching from database

Поиск
Список
Период
Сортировка

Lockup fetching from database

От
Brendan Duddridge
Дата:
Hi,

I'm running PostgreSQL 8.1 on OS X Server 10.4.3 using postgresql-8.1-404.jdbc3.jar and I'm experiencing some lockups when querying from the database through our java WebObjects program.

Here is a dump of the thread where the problem occurs:

"CSThreadPoolThread-0" prio=5 tid=0x00563ab0 nid=0x1894600 runnable [f1111000..f1112ac0]
       at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
        at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
        - locked <0x541cb7f8> (a java.io.BufferedInputStream)
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:254)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        - locked <0x541c8860> (a org.postgresql.core.v3.QueryExecutorImpl)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:324)
        at com.webobjects.jdbcadaptor.JDBCChannel._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:237)
        at com.webobjects.jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:303)
        at com.webobjects.jdbcadaptor.JDBCChannel.evaluateExpression(JDBCChannel.java:261)
        at com.webobjects.eoaccess.EOUtilities._rawRowsForSQL(EOUtilities.java:584)
        at com.webobjects.eoaccess.EOUtilities.rawRowsForSQL(EOUtilities.java:550)
        at ca.shopshop.ssmodel.Merchant.deactivateProductsForMerchantHavingNoOtherMerchantOffers(Merchant.java:614)
        at ca.shopshop.ssmodel.Merchant.deactivateMerchant(Merchant.java:685)
        at MerchantAccountStatusCheckerThread.performAction(MerchantAccountStatusCheckerThread.java:131)
        at com.clickspace.csutilities.threads.DatabaseUpdateThread.run(DatabaseUpdateThread.java:41)
        at com.clickspace.csutilities.threads.CSThreadPool$ThreadPoolThread.run(CSThreadPool.java:200)

So it seems that it's locking up retrieving data from the database. Is there a timeout parameter or something that can be set so if it takes too long to retrieve from the database I can handle an exception and continue on with my program? Right now all the other threads in my application start to block waiting for this one to complete and eventually my entire application instance locks up.

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 

Вложения

Re: Lockup fetching from database

От
Dave Cramer
Дата:
Hi Brendan,

I'd be looking at the underlying cause before timing out. This is a pretty robust system usually.


There is a query timeout in the backend if that is causing the problem.
Is this a very long query ?

Dave
On 13-Jan-06, at 10:56 AM, Brendan Duddridge wrote:

Hi,

I'm running PostgreSQL 8.1 on OS X Server 10.4.3 using postgresql-8.1-404.jdbc3.jar and I'm experiencing some lockups when querying from the database through our java WebObjects program.

Here is a dump of the thread where the problem occurs:

"CSThreadPoolThread-0" prio=5 tid=0x00563ab0 nid=0x1894600 runnable [f1111000..f1112ac0]
       at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
        at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
        - locked <0x541cb7f8> (a java.io.BufferedInputStream)
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:254)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        - locked <0x541c8860> (a org.postgresql.core.v3.QueryExecutorImpl)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:324)
        at com.webobjects.jdbcadaptor.JDBCChannel._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:237)
        at com.webobjects.jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:303)
        at com.webobjects.jdbcadaptor.JDBCChannel.evaluateExpression(JDBCChannel.java:261)
        at com.webobjects.eoaccess.EOUtilities._rawRowsForSQL(EOUtilities.java:584)
        at com.webobjects.eoaccess.EOUtilities.rawRowsForSQL(EOUtilities.java:550)
        at ca.shopshop.ssmodel.Merchant.deactivateProductsForMerchantHavingNoOtherMerchantOffers(Merchant.java:614)
        at ca.shopshop.ssmodel.Merchant.deactivateMerchant(Merchant.java:685)
        at MerchantAccountStatusCheckerThread.performAction(MerchantAccountStatusCheckerThread.java:131)
        at com.clickspace.csutilities.threads.DatabaseUpdateThread.run(DatabaseUpdateThread.java:41)
        at com.clickspace.csutilities.threads.CSThreadPool$ThreadPoolThread.run(CSThreadPool.java:200)

So it seems that it's locking up retrieving data from the database. Is there a timeout parameter or something that can be set so if it takes too long to retrieve from the database I can handle an exception and continue on with my program? Right now all the other threads in my application start to block waiting for this one to complete and eventually my entire application instance locks up.

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 


Re: Lockup fetching from database

От
Brendan Duddridge
Дата:
It seems it could have been caused by some database dead-locks. Not sure how those happened. I've set the statement_timeout to 3 minutes, so hopefully that'll prevent our threads from deadlocking too.

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 

On Jan 13, 2006, at 9:24 AM, Dave Cramer wrote:

Hi Brendan,

I'd be looking at the underlying cause before timing out. This is a pretty robust system usually.


There is a query timeout in the backend if that is causing the problem.
Is this a very long query ?

Dave
On 13-Jan-06, at 10:56 AM, Brendan Duddridge wrote:

Hi,

I'm running PostgreSQL 8.1 on OS X Server 10.4.3 using postgresql-8.1-404.jdbc3.jar and I'm experiencing some lockups when querying from the database through our java WebObjects program.

Here is a dump of the thread where the problem occurs:

"CSThreadPoolThread-0" prio=5 tid=0x00563ab0 nid=0x1894600 runnable [f1111000..f1112ac0]
       at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
        at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
        - locked <0x541cb7f8> (a java.io.BufferedInputStream)
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:254)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        - locked <0x541c8860> (a org.postgresql.core.v3.QueryExecutorImpl)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:324)
        at com.webobjects.jdbcadaptor.JDBCChannel._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:237)
        at com.webobjects.jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:303)
        at com.webobjects.jdbcadaptor.JDBCChannel.evaluateExpression(JDBCChannel.java:261)
        at com.webobjects.eoaccess.EOUtilities._rawRowsForSQL(EOUtilities.java:584)
        at com.webobjects.eoaccess.EOUtilities.rawRowsForSQL(EOUtilities.java:550)
        at ca.shopshop.ssmodel.Merchant.deactivateProductsForMerchantHavingNoOtherMerchantOffers(Merchant.java:614)
        at ca.shopshop.ssmodel.Merchant.deactivateMerchant(Merchant.java:685)
        at MerchantAccountStatusCheckerThread.performAction(MerchantAccountStatusCheckerThread.java:131)
        at com.clickspace.csutilities.threads.DatabaseUpdateThread.run(DatabaseUpdateThread.java:41)
        at com.clickspace.csutilities.threads.CSThreadPool$ThreadPoolThread.run(CSThreadPool.java:200)

So it seems that it's locking up retrieving data from the database. Is there a timeout parameter or something that can be set so if it takes too long to retrieve from the database I can handle an exception and continue on with my program? Right now all the other threads in my application start to block waiting for this one to complete and eventually my entire application instance locks up.

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 



Вложения