Identifying idle transactions on a Netezza system

There are a couple of reasons why you might want to know about old(ish) transactions that are in an idle state.

  1. Any reference to an object between the begin/commit requires some type of lock on that object.  The lock is only cleared once the transaction completes.
  2. Netezza uses visibility lists to govern what records should be visible to each session.  A transaction idle for many days could prevent a user or process from doing something with a row that it might otherwise have access to.
  3. There is some impact on the DBAs ability to administer the system.  For example: if I run the groom command, which physically removes the logical deletes from a table, this command will not be able to permanently delete a record that was deleted in a transaction that occurred AFTER the idle transaction began.

More often than not, this situation occurs when a user explicitly begins a transaction and neglects to commit it.

So how do we identify these transactions?  One way is to run nzsession and manually review the state and initial connection time of each session.  nzsession will report the sessions we’re interested in as being tx-idle.  This simply means that a begin was issued but that there is currently no active SQL interaction running.  This helps but I like to automate whenever possible so have the underlying SQL is more helpful.  The following could be run daily, hourly — whatever.  The SQL will report the session ID, connection time, user name, database, IP address and the last SQL command run for any session that is currently in a tx-idle state and began more than 6 hours ago.

select  *, 
        conn_actual < current_timestamp
from (  select
        '1970-01-01'::timestamp + extract(epoch from conntime) - 7200 conn_actual
    from     _v_session
    where    status = 'tx-idle'
) x
where     conn_actual < current_timestamp - '5 minutes'::interval;
and       status = 'tx-idle';

To change the age of the transactoins reported by this query, change the value 7200 to the number of seconds appropriate for your requirement (e.g. 86400 for 1 day old transactions).

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s