PostgreSQL“交易中的空闲"授予所有锁 [英] postgresql "idle in transaction" with all locks granted

问题描述

每次在一个小表(700行)上进行一次非常简单的删除(通过键)，然后将空闲状态"保持几分钟(通常需要数毫秒)，即使所有锁都标记为已授予". >

我该怎么做才能查明是什么原因? 我正在使用此选择:

  SELECT a.datname,
     c.relname,
     l.transactionid,
     l.mode,
     l.GRANTED,
     a.usename,
     a.waiting,
     a.query, 
     a.query_start,
     age(now(), a.query_start) AS "age", 
     a.pid 
FROM  pg_stat_activity a
 JOIN pg_locks         l ON l.pid = a.pid
 JOIN pg_class         c ON c.oid = l.relation
ORDER BY a.query_start;

其中显示了很多"RowExclusiveLock"，但都被授予了...所以我看不到是什么导致了这种延迟高峰.

解决方案

这是应用程序服务器的问题.

当应用程序未使用COMMIT或ROLLBACK结束事务时，

会话处于事务空闲"状态.这被认为是应用程序中的错误.

在事务结束之前，这些锁保持不变(并且当然是被授予的，否则会话将不能处于空闲状态).

从PostgreSQL 9.6开始，您可以设置参数

What can I do to pinpoint what causes it? I'm using this select:

  SELECT a.datname,
     c.relname,
     l.transactionid,
     l.mode,
     l.GRANTED,
     a.usename,
     a.waiting,
     a.query, 
     a.query_start,
     age(now(), a.query_start) AS "age", 
     a.pid 
FROM  pg_stat_activity a
 JOIN pg_locks         l ON l.pid = a.pid
 JOIN pg_class         c ON c.oid = l.relation
ORDER BY a.query_start;

which shows a lot of "RowExclusiveLock"s but all are granted... so I don't see what is causing this spikes of delays.

解决方案

This is a problem of the application server.

Sessions are in state "idle in transaction" when the application does not end the transaction with COMMIT or ROLLBACK. This is to be considered a bug in the application.

The locks remain (and are of course granted, otherwise the session could not be idle) until the transaction ends.

From PostgreSQL 9.6 on, you can set the parameter idle_in_transaction_session_timeout to terminate such transactions automatically with a ROLLBACK, but that is a band-aid to avoid problems on the database rather than a solution.

这篇关于PostgreSQL“交易中的空闲"授予所有锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！