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:
DISCLAIMER
An Auto-Commit is not always set for executions in Snapshot-Mode. Thus, in the case of snapshot execution, this solution can lead to false-positive read locks.
=> We recommend to use caution when developing watchdog scripts which automatically kill all critical read locks
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
