使用Hibernate，Postgres和Guice Provider时出现“交易空闲" [英] 'idle in transaction' when using Hibernate, Postgres and Guice Provider

问题描述

执行时:

select * from pg_stat_activity where state ~ 'idle in transact'

我得到了状态为交易中空闲"的不适当的行数.其中一些闲置了几天.其中大多数是相同的简单select查询，它们是从一个服务类(Hibernate 5.1.0.Final，Guice 4.1.0)执行的:

I get inappropriate number of rows with state 'idle in transaction'. Some of them idle for a few days. Most of them, are the same simple select query which are executed from one service class (Hibernate 5.1.0.Final, Guice 4.1.0):

public class FirebaseServiceImpl implements FirebaseService {

    @Inject
    private Provider<FirebaseKeyDAO> firebaseKeyDAO;

    @Override
    public void sendNotification(User recipient) {

        List<FirebaseKey> firebaseKeys = firebaseKeyDAO.get().findByUserId(recipient.getId());

        final ExecutorService notificationsPool = Executors.newFixedThreadPool(3);

        for (FirebaseKey firebaseKey : firebaseKeys)
            notificationsPool.execute(new Runnable() {

                 @Override
                 public void run() {
                    sendNotification(new FirebaseNotification(firebaseKey.getFirebaseKey(), "example");
                 }
        });

        notificationsPool.shutdown();
    }
}

DAO方法:

@Override
@SuppressWarnings("unchecked")
public List<FirebaseKey> findByUserId(Long userId) {
    Criteria criteria = getSession().createCriteria(type);
    criteria.add(Restrictions.eq("userId", userId));
    return criteria.list();
}

为什么会发生?如何避免这种情况?

Why does it happen? How to avoid this?

更新

当我在单独的线程中使用Guice Provider exampleDAO.get()时，不会提交事务:

Transactions are not commited when I use Guice Provider exampleDAO.get() in a separate thread:

@Inject
Provider<ExampleDAO> exampleDAO;

推荐答案

通常在使用pgbouncer或其他使用pool_mode = transaction的池/会话管理器时发生.例如，当客户打开一个事务并保留它时，不提交也不回滚.检查是否在查询列中看到DISCARD ALL-如果是这种情况，请

It usually happens when you use pgbouncer or other pooler/session manager that uses pool_mode = transaction. Eg when client opens a transaction and holds it, not committing nor rolling back. Check if you see DISCARD ALL in query column - if you do this is the case, because pooler has to discard shared session plans, sequences, deallocate statements etc to avoid mixing those for different sessions in pool.

另一方面，任何正常"交易都给出相同的idle in transaction，例如:

On the other hand any "normal" transaction gives same idle in transaction, eg:

2>select now(),pg_backend_pid();
               now                | pg_backend_pid
----------------------------------+----------------
 2017-05-05 16:53:01.867444+05:30 |          26500
(1 row)

如果检查其状态，则会看到正统的idle:

if we check its state we see orthodox idle:

t=# select query,state from pg_stat_activity where pid = 26500;
             query              | state
--------------------------------+-------
 select now(),pg_backend_pid(); | idle
(1 row)

现在我们在session 2 >上开始交易: 2>开始； 开始

now we start transaction on session 2 >: 2>begin; BEGIN

2>select now(),pg_backend_pid();
               now                | pg_backend_pid
----------------------------------+----------------
 2017-05-05 16:54:15.856306+05:30 |          26500
(1 row)

并检查pg_stat_statements增益:

t=# select query,state from pg_stat_activity where pid = 26500;
             query              |        state
--------------------------------+---------------------
 select now(),pg_backend_pid(); | idle in transaction
(1 row)

它会一直保持这种状态，直到语句超时或事务结束:

It will remain this way until statement timeout or end of transaction:

2>end;
COMMIT
t=# select query,state from pg_stat_activity where pid = 26500;
 query | state
-------+-------
 end;  | idle
(1 row)

所以这很普遍而且可以.如果要避免连接会话，则必须断开客户端连接.但是postgres中的连接非常昂贵，因此通常人们尝试使用pool重用现有的连接，因此这些状态出现在pg_stat_activity

So it is quite common and ok to have it. If you want to avoid connected sessions, you have to disconnect client. But connection in postgres is expensive, so usually people try reuse existing connections with pool, and so such states appear in pg_stat_activity

这篇关于使用Hibernate，Postgres和Guice Provider时出现“交易空闲"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！