Team Exasol
Team Exasol

Background

Idle sessions with open transactions may have some negative implications:
First, there might be more transaction conflicts in parallel sessions, most likely if the idle session has an open write transaction.
Second, the database garbage collection might not be able to reclaim older object versions though increasing storage space usage and backup sizes.

How to determine idle sessions with open transactions

You can use the following SQL statement to add locking information to your session system tables using the EXA_SQL_LAST_DAY statistics:

with
	EXA_SQL as (
		select
			SESSION_ID,
			STMT_ID,
			COMMAND_CLASS,
			COMMAND_NAME,
			SUCCESS
		from
			--EXA_DBA_AUDIT_SQL                   -- delivers more exact results (if available)
			EXA_SQL_LAST_DAY
		where
			SESSION_ID in (select SESSION_ID from EXA_DBA_SESSIONS)
	),
	SESSION_RISKS as (
		select
			SESSION_ID,
			HAS_LOCKS
		from
			(
				select
					SESSION_ID,
					decode(
						greatest(CURRENT_ACCESS, LAST_ACCESS),
						0,
						'NONE',
						1,
						'READ LOCKS',
						2,
						'WRITE LOCKS'
					) HAS_LOCKS
				from
					(
						select
							S.SESSION_ID,
							case
								when
									(S.STATUS not in ('IDLE', 'DISCONNECTED')) OR
									(
										S.COMMAND_NAME not in ('COMMIT', 'ROLLBACK', 'NOT SPECIFIED')
									)
								then
									case
										when
											S.COMMAND_NAME in (
												'SELECT', 'DESCRIBE', 'OPEN SCHEMA', 'CLOSE SCHEMA', 'FLUSH STATISTICS', 'EXECUTE SCRIPT'
											)
										then
											1
										else
											2
									end
								else
									0
							end CURRENT_ACCESS,
							zeroifnull(A.ACCESS) LAST_ACCESS
						from
								EXA_DBA_SESSIONS S
							left join
								(
									select
										SESSION_ID,
										max(ACCESS) ACCESS
									FROM
										(
											select
												SESSION_ID,
												case
													when
														(
															COMMAND_NAME not in ('COMMIT', 'ROLLBACK', 'NOT SPECIFIED')
														)
													then
														case
															when
																COMMAND_NAME in (
																	'SELECT',
																	'DESCRIBE',
																	'OPEN SCHEMA',
																	'CLOSE SCHEMA',
																	'FLUSH STATISTICS',
																	'EXECUTE SCRIPT'
																)
															then
																1
															else
																2
														end
													else
														0
												end ACCESS
											from
												EXA_SQL C
											where
												C.COMMAND_CLASS <> 'TRANSACTION' and
												SUCCESS and
												not exists(
													select
														*
													from
														EXA_SQL E
													where
														E.SESSION_ID = C.SESSION_ID and
														E.STMT_ID > C.STMT_ID and
														E.COMMAND_CLASS = 'TRANSACTION'
												)
										)
									group by
										SESSION_ID
								) A
							on
								S.SESSION_ID = A.SESSION_ID
					)
				where
					SESSION_ID <> 4
			)
	)
select
	HAS_LOCKS,
	case
		when
			DURATION > '1:00:00' and
			STATUS = 'IDLE'
		then
			decode(
				HAS_LOCKS,
				'READ LOCKS',
				'CRITICAL',
				'WRITE LOCKS',
				'VERY CRITICAL',
				NULL
			)
	end EVALUATION,
	S.*
from
		EXA_DBA_SESSIONS S
	left join
		SESSION_RISKS R
	on
		(S.SESSION_ID = R.SESSION_ID)
order by
	EVALUATION desc,
	LOGIN_TIME;

The query determines the approximate lock status of each session and does a risk evaluation on the basis of idle times and open transaction locks. Session with open transactions being idle for over an hour typically start to cause stated effects.

In the following example the session

HAS_LOCKS EVALUATION SESSION_ID USER_NAME STATUS COMMAND_NAME DURATION ...
    4 SYS IDLE NOT SPECIFIED 0:00:02 ...
NONE   1505059440358261249 GUEST IDLE NOT SPECIFIED 3:28:20 ...
READ LOCKS   1505059440023663104 ADMIN EXECUTE SQL SELECT 0:00:01 ...
WRITE LOCKS   1505061190567112340 LOADER EXECUTE SQL MERGE 0:11:02 ...
READ LOCKS CRITICAL 1505059543549212162 ANALYST IDLE NOT SPECIFIED 1:26:19 ...
WRITE LOCKS VERY CRITICAL 1505061190568112648 TESTER IDLE NOT SPECIFIED 2:10:02 ...

The sessions 1505061190568112648 and 1505059543549212162 in the example have been idle for some time but did not finish their open transactions.

Additional References

https://docs.exasol.com/database_concepts/session_management.htm?Highlight=idle%20sessions

https://docs.exasol.com/sql_references/metadata/metadata_system_tables.htm?Highlight=idle%20sessions

 https://www.exasol.com/support/browse/EXASOL-2415

https://www.exasol.com/support/browse/EXASOL-1330