Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add better transaction control to isql [CORE4933] #5224

Closed
firebird-automations opened this issue Sep 15, 2015 · 8 comments
Closed

Add better transaction control to isql [CORE4933] #5224

firebird-automations opened this issue Sep 15, 2015 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @helebor

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.

Commits: 2d5dc3f bef4e5f 577cd64 0a0a056

@firebird-automations
Copy link
Collaborator Author

Commented by: @helebor

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Description of proposed implementation (see also #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>

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Deferred

Test Details: Some questions raised, sent letter to hvlad, 19.02.2020 11:03.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => Done successfully

Test Details: Some questions raised, sent letter to hvlad, 19.02.2020 11:03. =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants