Incorrect user priorities in session system tables
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5068 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.3 |
| Fix Versions | EXASOL 6.0.4 |
| Resolution Date | 2017-09-25 |
Bug
During the time between a user priority change and the commit/rollback of that change the user priorities shown in EXA_DBA_SESSIONS, EXA_ALL_SESSIONS, and EXA_USER_SESSIONS do not match the effective user priorities but depend on the transaction state of the affected sessions. This is only a display problem, the effective user priorities change during commit.
How To Reproduce
- SET AUTOCOMMIT OFF
- Read the user priorities from EXA_DBA_SESSIONS, EXA_ALL_SESSIONS, or EXA_USER_SESSIONS.
- Change the user priority of the owner of one of those sessions, e.g. using GRANT PRIORITY.
- Read the user priority of the changed session from EXA_DBA_SESSIONS, EXA_ALL_SESSIONS, or EXA_USER_SESSIONS.
- Actual result: The user priority depends on whether the affected session is in an open transaction. Results may differ when changing the priority of the own user.
- Expected result: The user priority does not change since it has not been committed yet and does not depend on the transaction state of the affected session.
Workaround
Using SET AUTOCOMMIT ON when changing user priorities may mitigate this issue.
Changed behavior
The user priorities shown in EXA_DBA_SESSIONS, EXA_ALL_SESSIONS, and EXA_USER_SESSIONS now always match the priorities which are currently effective in the resource scheduler: User priority changes are no longer immediately visible in these system tables to the changing transaction but only after commit. Other transactions which have read an old priority value from one of these system tables now also see the new priority value immediately after the change gets committed.
