Answer the question
In order to leave comments, you need to log in
How to avoid blocking parallel queries in Postgresql?
Starts 10 threads from the ExecutorService thread pool. In each thread, a connection is created and 1 sql query is executed and committed. By itself, a large selection query (select from several tables) takes 1-2 minutes.
After start requests hang and are not executed. Tracking was done from pg_stat_activity, where 10 threads (sometimes these statistics changed, sometimes blocked, sometimes not) have the following statuses:
They can be completed in an hour. Previously, this problem was not due to less data in the tables with the same queries.
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQL82Dialect</property>
<property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
<property name="show_sql">true</property>
<property name="log_statement">all</property>
<property name="hibernate.hikari.dataSourceClassName">org.postgresql.ds.PGSimpleDataSource</property>
<property name="hibernate.hikari.connectionTimeout">300000</property>
<property name="hibernate.hikari.maximumPoolSize">35</property>
<property name="hibernate.hikari.poolName">hicari</property>
<property name="hibernate.hikari.dataSource.url"></property>
<property name="hibernate.hikari.dataSource.user"></property>
<property name="hibernate.hikari.dataSource.password"></property>
<property name="hibernate.hikari.autoCommit">false</property>
<property name="hibernate.current_session_context_class">thread</property>
@Override
public List<SendObj> call() {
List<SendObj> sendObjList = null;
Session session = sessionFactory.getCurrentSession();
session.getTransaction().setTimeout(200);
session.getTransaction().begin();
...
//здесь происходит выполнение query.list()
...
if (session.isOpen())
session.getTransaction().commit();
return sendObjList;
}
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question