Hot news

Why Oracle DB?

Oracle is the most suitable DB for storing data in high- security mode, arranging BigData , implementing any idea in many applications . ...

2018-09-13

Commit or not commit

Multy-sessioning or single connect

On working in developer UI the helpful ability to keep several connections may cause a critical problem.
For example, there are global hot-keys in SQLDetective: "F10" executes the COMMIT command in active session," F11" executes the ROLLBACK command in active session. But this globalization is very limited. Icon Dictionary describes: "Performs COMMIT/ROLLBACK  for the active session. All changes in the database that have been made by the program will be committed.". The help-topic "Connection to Oracle databases – Committing and rolling back changes" suggests hot-keys as the first step and it does not explain the "main active session" term mixing up the local session in SQL Editor.
Application allows several  DB connections per instance if the "View / Preferences / General / Session / Only one connection per SQLDetective instance" option is disabled (default value). Data changing is available in four windows: Object Navigator ("Data" tab), Table Wizard ("Data" page), Smart Dataset, SQL Editor (executed SELECT statement with ROWID column into "Data Output" tab).
Table Wizard operates with data only from one active session per window, but you may open the same table in its wizard in each connection. "Data" page of Table Wizard is a part of Smart Dataset tool. By the way, all connections to the same user and DB are numbered. As the "Preferences / Dataset Editors / Smart Dataset / Open separate window for each object" option is disabled by default then you may modify data from different connections in one window (several tabs open). Unfortunately, there is no actions in toolbar or popup menu for commiting or rolling back in the Smart Dataset window. For saving data you should select the main session in main menu (Session / selection) or main toolbar and commit by pressing "F10" or executing "Session /  Commit" from main menu or main toolbar.
On working with data in Object Navigator the main active session is selected automatically on selecting in ObjectSelector. That's why the global actions works automatically in "Data" tab of ContentSelector.
"Data Output" tab in SQL Editor is a part of Smart Dataset tool. But as SQL Editor selects the working session then COMMIT/ROLLBACK command can be executed in the same window by manually typed and running script.

Example of using all four windows:
- run SQLDetective;
- connect to your DB with the same schema name four times;
- locate a table of the fourth connection in Object Navigator;
- execute "Generate SQL / SELECT *, ROWID" for this table;
- execute selection into Data Output;
Result: SQL Editor opens with "select *, rowid from mytable" grid for "[4] MySession".
- locate the same table of the third connection in Object Navigator;
- execute "Browse" for this table;
Result: Smart Dataset "[3] MySession - MyTable" opens.
- locate the same table of the second connection in Object Navigator;
- execute "Open" for this table;
Result: Table Wizard "[2] MySession - MyTable" opens.
- locate the same table of the first connection in Object Navigator;
- open "Data" tab in ContentSelector.
Add a row in Object Navigator. Click in Table Wizard. Press F10. Add a row in Data page. Click in Smart Dataset. Press F10. Add a row in Smart Dataset. Click in SQL Editor. Add a row in Data Output tab. Click in Object Navigator. Refresh data.
Unfortunately, the global actions work only in globally selected session. It's visible by default in the combobox in main toolbar. Each window shows the working session in caption or status bar and it may be not equal to main active session. So, a row was saved in DB only from Object Navigator. And when you try to delete or modify the same row in all windows with pressing "F10" the error "Record is locked by another user." or "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" occur. To exclude the described behaviour you should work in one session or check the main session before commiting.

As a workaround the Conquest Software Solutions developers should add an ability to select the session for which the commit/rollback action is executed by hot-key. Or run the global action for session in the current active window automatically.

Tip1: please, don't forget about DDL commands that execute commiting automatically.
Tip2: SQLDdetective extract DDL or create some objects with COMMIT command execution.
Tip3: Stored Program Execution, Fast Copier, Version Control System, Export/Import Data  tools have an option to commit/rollback.
Tip4: there are several options for auto commiting data (Preferences / General / Session / Auto commit after each statement, Preferences / General / Session / On Disconnect = Commit/Rollback/Prompt if changes detected, Preferences / General / Session / Confirm commit and rollback, Preferences / Dataset Editors / Commit on post). By default they are disabled to not be executed in auto mode.

No comments:

Post a Comment