Обсуждение: Idle Sessions inTransaction with RELEASE_EXEC_SVP queries and locks

On 10 Dec 2014 08:52, <chris.jurado@primesoft.ph> wrote:
>
> ​I am having a problem with having idle sessions in transactions. In pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX data are varied) as its query and it's locks also contain a lot of these "RELEASE_EXEC_SVP_XXXXXX" entries. What do these mean? These commonly cause lock blocking problems because these sessions sometimes lock other rows but the session status is always "Idle in transaction". I do not know why there are sessions like these. I have reviewed our system and do not see any possibility of transactions getting started that are not rollbacked or committed and just stay idle so this must be something internal to postgresql.
>
> Version of PostgreSQL is 9.2.8 running on Windows using psqlodbc driver 9.0.3.210.

What is the application platform? I know there are some APIs/packages in languages viz python which would start an implicit transaction even when you fire a select statement. What is the kind of lock and what is their granted status?

What is the isolation level that you use in application?

Regards
Sameer Kumar
Ashnik Pte Ltd, Singapore

On 12/08/2014 02:05 AM, chris.jurado@primesoft.ph wrote:
> ​I am having a problem with having idle sessions in transactions. In
> pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX
> data are varied) as its query and it's locks also contain a lot of these
> "RELEASE_EXEC_SVP_XXXXXX" entries. What do these mean? These commonly
> cause lock blocking problems because these sessions sometimes lock other
> rows but the session status is always "Idle in transaction". I do not
> know why there are sessions like these. I have reviewed our system and
> do not see any possibility of transactions getting started that are not
> rollbacked or committed and just stay idle so this must be something
> internal to postgresql.
>
> Version of PostgreSQL is 9.2.8 running on Windows using psqlodbc driver
> 9.0.3.210.

 From the psqlodbc source this would seem to indicate SAVEPOINTs are
involved, so you might want to take a look at this thread:

http://www.postgresql.org/message-id/52dd07051f6b42b09328c339c7b9c5d4@EXCH2013.mustinformatique.fr

in particular this message:

http://www.postgresql.org/message-id/54819FAC.7030103@vmware.com

>


--
Adrian Klaver
adrian.klaver@aklaver.com