There are a couple of reasons why you might want to know about old(ish) transactions that are in an idle state.
- 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.
- 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.
- 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 id, conntime, username, dbname, ipaddr, command, '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).