Issue Details (XML | Word | Printable)

Key: CORE-4933
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Vlad Khorsun
Reporter: Helen Borrie
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Add better transaction control to isql

Created: 14/Sep/15 11:15 PM   Updated: 19/Feb/20 04:52 PM
Component/s: ISQL
Affects Version/s: None
Fix Version/s: 4.0 Beta 2, 3.0.6

Environment: Any

QA Status: Done successfully


 Description  « Hide
This is a suggestion for a new feature in isql.
Currently, it is not possible to change the attributes of the default transaction in isql. Whilst you can use SET TRANSACTION to start one transaction that is not the global default - READ WRITE WAIT SNAPSHOT - the following transaction reverts to the global configuration. This is confusing for anyone using isql to test the effects of concurrent transactions in various configurations and get to grips with multi-version transaction control. I think it is important to provide a way to support this learning process, that does not require a user to write a custom application.

SET TRANSACTION is standards-compliant and part of Firebird's SQL lexicon, unlike some of the SET commands in isql. It works as a DSQL command but isql has no way to retain the attributes and use them for further transactions in the isql session. How about a command KEEP TRANSACTION <access-mode> <lock-resolver> <isolation-level> [<rv-flag>]? It would work (for the user) just like SET TRANSACTION, but would start the next transaction with the same attributes. And maybe LOSE TRANSACTION with no parameters, to have isql revert to the current behaviour.

Along with this, I would like to see SHOW TRANSACTION, to list out the attributes of current_transaction, including the handle ID, if that were feasible.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Helen Borrie added a comment - 14/Sep/15 11:18 PM
Sorry the versions selector didn't seem to be working. I think it would be possible to do this for v.2.5.5 and port it forward to v.3.0.

Vlad Khorsun added a comment - 16/Feb/20 02:52 PM
Description of proposed implementation (see also https://github.com/FirebirdSQL/firebird/pull/258)

Add new SET command at ISQL: SET KEEP_TRAN_PARAMS:

    when ON it keeps text of following successful SET TRANSACTION statement and
    new DML transactions is started using the same SQL (instead of defaul CONCURRENCY WAIT mode)
    when OFF, isql start new DML transaction as usual.
    Name KEEP_TRAN_PARAMS could be cut up to the KEEP_TRAN.

Example:

SQL> SET;
...
Keep transaction params: OFF
SQL> SET KEEP_TRAN;
SQL> SET;
...
Keep transaction params: ON
SET TRANSACTION
SQL>commit;
SQL>SET TRANSACTION READ COMMITTED WAIT;
SQL>SET;
...
Keep transaction params: ON
  SET TRANSACTION READ COMMITTED WAIT
SQL> SELECT RDB$GET_CONTEXT('SYSTEM', 'ISOLATION_LEVEL') FROM RDB$DATABASE;

RDB$GET_CONTEXT

=============================================================
READ COMMITTED

SQL> commit;
SQL> SELECT RDB$GET_CONTEXT('SYSTEM', 'ISOLATION_LEVEL') FROM RDB$DATABASE;

RDB$GET_CONTEXT

=============================================================
READ COMMITTED

SQL> SET KEEP_TRAN OFF;
SQL> SELECT RDB$GET_CONTEXT('SYSTEM', 'ISOLATION_LEVEL') FROM RDB$DATABASE;

RDB$GET_CONTEXT

=============================================================
READ COMMITTED

SQL> commit;
SQL> SELECT RDB$GET_CONTEXT('SYSTEM', 'ISOLATION_LEVEL') FROM RDB$DATABASE;

RDB$GET_CONTEXT

=============================================================
SNAPSHOT

SQL> SET;
...
Keep transaction params: OFF
SQL>

Vlad Khorsun added a comment - 18/Feb/20 09:52 PM
The feature is committed into fb3 and fb4.
In fb3 KEEP_TRAN_PARAMS is OFF by default, preserving backward compatibility with old (weird) behaviour.
In fb4 KEEP_TRAN_PARAMS is ON by default to make isql behaviour more logical by default.