Thomas,
From the thread dump I found that "pool-2-thread-43" &
"pool-2-thread-110" are on same database. So it make sense to
"pool-2-thread-43" is blocked by "pool-2-thread-110".
DriverManager.java synchronized on
private static synchronized Connection getConnection(
String url, java.util.Properties info, ClassLoader callerCL) throws
SQLException {
So the thread-43 try to get connection, but is blocked by therad-110. So all
the other threads blocked by thread-43 because they are trying to get the
connection (which is synchronized)
Looks
like class="org.springframework.jdbc.datasource.DriverManagerDataSource" is
trying to get the connection every time.
Is there anyway to use same connection without trying to get the connection
every time in the spring? I know it is outside but if you know please let me
know.
I have used the common-dbcp connection pool, but even the threads are
blocked.
Thanks,
Suresh
On Tue, Apr 19, 2011 at 11:10 PM, suresh kumar <[email protected]>wrote:
> Thomas,
>
> Here is the thread dump. All the threads are blocked on
> "pool-2-thread-43" which is blocked by "pool-2-thread-110"
>
>
> "pool-2-thread-50" - Thread t@88
> java.lang.Thread.State: BLOCKED on java.lang.Class@1308abb owned by:
> pool-2-thread-43
> at java.sql.DriverManager.getConnection(DriverManager.java:138)
> at
> org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
> at
> org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
> at
> org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
> at
> org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
> at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:661)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:701)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:165)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:171)
> at
> org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:178)
> at
> com.transerainc.rtmc.dao.CurrentValuesDAO.getQueues(CurrentValuesDAO.java:472)
> at
> com.transerainc.rtmc.dao.CurrentValuesDAO$$FastClassByCGLIB$$d6524a80.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)
> at
> com.transerainc.rtmc.dao.CurrentValuesDAO$$EnhancerByCGLIB$$37941adb.getQueues(<generated>)
> at
> com.transerainc.rtmc.bo.CurrentValuesBO.getQueueSnapshotReportData(CurrentValuesBO.java:427)
> at
> com.transerainc.rtmc.handler.CallSnapshotHandler.handleQueuePointReport(CallSnapshotHandler.java:117)
> at
> com.transerainc.rtmc.handler.CallSnapshotHandler.handleRequest(CallSnapshotHandler.java:75)
> at
> com.transerainc.rtmc.ui.UISimulatorTask.run(UISimulatorTask.java:31)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
> at
> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:280)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:135)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:65)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:146)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:170)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
> at java.lang.Thread.run(Thread.java:595)
> ------
> ------
> ------
>
> "pool-2-thread-43" - Thread t@81
> java.lang.Thread.State: BLOCKED on org.h2.engine.Database@189c09e owned
> by: pool-2-thread-110
> at org.h2.engine.Engine.openSession(Engine.java:70)
> at org.h2.engine.Engine.openSession(Engine.java:158)
> at org.h2.engine.Engine.createSessionAndValidate(Engine.java:137)
> at org.h2.engine.Engine.createSession(Engine.java:120)
> at org.h2.engine.Engine.createSession(Engine.java:28)
> at
> org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:289)
> at
> org.h2.engine.SessionRemote.createSession(SessionRemote.java:265)
> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
> at org.h2.Driver.connect(Driver.java:72)
> at java.sql.DriverManager.getConnection(DriverManager.java:525)
> at java.sql.DriverManager.getConnection(DriverManager.java:140)
> at
> org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
> at
> org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
> at
> org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
> at
> org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
> at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:661)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:701)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:177)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:190)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForInt(NamedParameterJdbcTemplate.java:217)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForInt(NamedParameterJdbcTemplate.java:222)
> at
> org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForInt(SimpleJdbcTemplate.java:104)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO.getTotalAgentsByTeams(CurrentAgentValuesDAO.java:520)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO$$FastClassByCGLIB$$a8144c1b.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO$$EnhancerByCGLIB$$6224ff44.getTotalAgentsByTeams(<generated>)
> at
> com.transerainc.rtmc.bo.CurrentAgentValuesBO.getMultiTeamAgentSnapshotData(CurrentAgentValuesBO.java:321)
> at
> com.transerainc.rtmc.handler.AgentSnapshotHandler.handleMultiTeamSnaphostRequest(AgentSnapshotHandler.java:144)
> at
> com.transerainc.rtmc.handler.AgentSnapshotHandler.handleRequest(AgentSnapshotHandler.java:93)
> at
> com.transerainc.rtmc.ui.UISimulatorTask.run(UISimulatorTask.java:31)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
> at
> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:280)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:135)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:65)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:146)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:170)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
> at java.lang.Thread.run(Thread.java:595)
>
>
> "pool-2-thread-110" - Thread t@148
> java.lang.Thread.State: RUNNABLE
> at org.h2.index.MultiVersionCursor.next(MultiVersionCursor.java:95)
> at org.h2.index.IndexCursor.next(IndexCursor.java:222)
> at org.h2.table.TableFilter.next(TableFilter.java:342)
> at org.h2.command.dml.Select.queryGroup(Select.java:311)
> at org.h2.command.dml.Select.queryWithoutCache(Select.java:592)
> at org.h2.command.dml.Query.query(Query.java:257)
> at org.h2.command.dml.Query.query(Query.java:227)
> at org.h2.command.CommandContainer.query(CommandContainer.java:78)
> at org.h2.command.Command.executeQuery(Command.java:132)
> at
> org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
> at
> org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)
> at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:661)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:701)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:165)
> at
> org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:171)
> at
> org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:178)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO.getAuxiliaryData(CurrentAgentValuesDAO.java:974)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO$$FastClassByCGLIB$$a8144c1b.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)
> at
> com.transerainc.rtmc.dao.CurrentAgentValuesDAO$$EnhancerByCGLIB$$6224ff44.getAuxiliaryData(<generated>)
> at
> com.transerainc.rtmc.bo.CurrentAgentValuesBO.setAuxiliaryData(CurrentAgentValuesBO.java:837)
> at
> com.transerainc.rtmc.bo.CurrentAgentSkillValuesBO.getSkillAgentSnapshotReportData(CurrentAgentSkillValuesBO.java:423)
> at
> com.transerainc.rtmc.handler.AgentSnapshotHandler.handleSkillAgentReport(AgentSnapshotHandler.java:234)
> at
> com.transerainc.rtmc.handler.AgentSnapshotHandler.handleRequest(AgentSnapshotHandler.java:75)
> at
> com.transerainc.rtmc.ui.UISimulatorTask.run(UISimulatorTask.java:31)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
> at
> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:280)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:135)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:65)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:146)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:170)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
> at java.lang.Thread.run(Thread.java:595)
>
>
> Thanks,
> Suresh
>
>
> On Tue, Apr 19, 2011 at 11:05 PM, suresh kumar <[email protected]>wrote:
>
>> Thomas,
>>
>> I am in the assumption that if I use separate database for each table
>> the syn on the executeQuery should do only database.
>> But when I look at the thread-dump all the queries are blocked by single
>> thread (even they are on different database).
>>
>> Here are the connection urls of each db.
>>
>> rtmch2db.driver.class.name = org.h2.Driver
>> rtmch2db1.url = jdbc:h2:mem:rtmch2db1;MVCC=TRUE;DB_CLOSE_DELAY=-1
>> rtmch2db2.url = jdbc:h2:mem:rtmch2db2;MVCC=TRUE;DB_CLOSE_DELAY=-1
>> rtmch2db3.url = jdbc:h2:mem:rtmch2db3;MVCC=TRUE;DB_CLOSE_DELAY=-1
>> rtmch2db4.url = jdbc:h2:mem:rtmch2db4;MVCC=TRUE;DB_CLOSE_DELAY=-1
>> rtmch2db5.url = jdbc:h2:mem:rtmch2db5;MVCC=TRUE;DB_CLOSE_DELAY=-1
>> rtmch2db6.url = jdbc:h2:mem:rtmch2db6;MVCC=TRUE;DB_CLOSE_DELAY=-1
>>
>>
>> I am using spring with h2. Here is the datasource configuration.
>>
>> <bean id="h2DataSource1"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db1.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>> <bean id="h2DataSource2"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db2.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>> <bean id="h2DataSource3"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db3.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>> <bean id="h2DataSource4"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db4.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>> <bean id="h2DataSource5"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db5.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>> <bean id="h2DataSource6"
>> class="org.springframework.jdbc.datasource.DriverManagerDataSource">
>> <property name="driverClassName" value="${
>> rtmch2db.driver.class.name}" />
>> <property name="url" value="${rtmch2db6.url}" />
>> <property name="username" value="${rtmch2db.username}"/>
>> <property name="password" value=""/>
>> </bean>
>>
>>
>> <jdbc:initialize-database data-source="h2DataSource1">
>> <jdbc:script location="classpath:current-values-schema.sql"/>
>> </jdbc:initialize-database>
>>
>> <jdbc:initialize-database data-source="h2DataSource2">
>> <jdbc:script location="classpath:current-queue-depth-schema.sql"/>
>> </jdbc:initialize-database>
>>
>> <jdbc:initialize-database data-source="h2DataSource3">
>> <jdbc:script location="classpath:current-agent-site-team-schema.sql"/>
>> </jdbc:initialize-database>
>>
>> <jdbc:initialize-database data-source="h2DataSource4">
>> <jdbc:script location="classpath:current-agent-values-schema.sql"/>
>> </jdbc:initialize-database>
>>
>> <jdbc:initialize-database data-source="h2DataSource5">
>> <jdbc:script location="classpath:current-call-skills-schema.sql"/>
>> </jdbc:initialize-database>
>>
>> <jdbc:initialize-database data-source="h2DataSource6">
>> <jdbc:script location="classpath:current-agent-skills-schema.sql"/>
>> </jdbc:initialize-database>
>>
>>
>> when i dig into thread dump current running thread (query execution) sync
>> on
>>
>> public ResultInterface executeQuery(int maxrows, boolean scrollable) {
>>
>> startTime = 0;
>>
>> Database database = session.getDatabase();
>>
>> Object sync = database.isMultiThreaded() ? (Object) session :
>> (Object) database;
>>
>> session.waitIfExclusiveModeEnabled();
>>
>> * synchronized (sync) {*
>>
>> * try {*
>>
>> * database.checkPowerOff();*
>>
>> * session.setCurrentCommand(this);*
>>
>> * return query(maxrows);*
>>
>> * } catch (DbException e) {*
>>
>> * e.addSQL(sql);*
>>
>> * database.exceptionThrown(e.getSQLException(), sql);*
>>
>> * throw e;*
>>
>> * } finally {*
>>
>> * stop();*
>>
>> * }*
>>
>> * }*
>> }
>>
>> H2 should do syncronization on database right? Can you please let me know
>> where I am doing wrong?
>>
>> Thanks,
>> Suresh
>>
>> On Sun, Apr 10, 2011 at 2:29 AM, Thomas Mueller <
>> [email protected]> wrote:
>>
>>> Hi,
>>>
>>> > Can I create separate data base for each table? The tables
>>> are not
>>> > related to each other.
>>> > Can this help?
>>>
>>> Yes, I believe this would help.
>>>
>>> Regards,
>>> Thomas
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "H2 Database" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/h2-database?hl=en.
>>>
>>>
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
