Choosing Between Auto‑Commit and Manual Transaction Modes in DataGrip Query Console

Decision and Constraints

When you need to run exploratory or potentially destructive SQL in DataGrip, decide to use the query console in manual transaction mode. This lets you group statements, examine results, and roll back if something goes wrong, preventing unintended data changes.

Constraints for this approach:

• The target database must support transactions (e.g., PostgreSQL, MySQL InnoDB, Oracle).
• Your database user must have privileges to start, commit, and roll back transactions (typically any user with write access).
• DataGrip must be configured to disable auto‑commit for the console.

Options Comparison

Mode	Behavior	Risk	Typical Use
Auto‑commit (default)	Each statement is committed immediately after execution.	Accidental changes become permanent; no rollback possible.	Simple ad‑hoc queries where immediate persistence is acceptable.
Manual transaction	You explicitly start a transaction, run statements, then choose Commit or Rollback.	Forgot to end the transaction can hold locks and block other users.	Exploratory work, testing scripts, or any situation where you need a safety net.

Trade‑offs

Auto‑commit offers simplicity and instant visibility of changes, but it carries the risk of permanent data loss if a statement is erroneous. Manual transaction adds an extra step—starting the transaction and later committing or rolling back—but provides the ability to test multiple statements together and undo them safely.

Implementation Steps

1. Open a query console for the desired data source (right‑click the data source → Open Console).
2. In the console toolbar, click the transaction icon (a circular arrow) to disable auto‑commit. The icon will change to indicate that manual mode is active.
3. To begin a transaction, either click the Start Transaction button on the toolbar or execute BEGIN; (or the equivalent for your DBMS).
4. Run your SQL statements. You can use the usual Ctrl+Enter to execute the current statement or selection.
5. After reviewing the results, decide:
   • Click Commit to make changes permanent.
   • Click Rollback to discard all changes made since the transaction started.
6. When finished, ensure the transaction is ended; the toolbar icon will revert to the auto‑commit state and the status bar will no longer show “Transaction: active”.

Placeholders: replace with your actual data source name, and with the SQL you wish to test.

Required permissions: any user that can write to the target schema can start a transaction. No special privileges are needed beyond standard DML rights.

Risks: leaving a transaction open can lock rows or tables, preventing other sessions from modifying the same data until you commit or roll back. Always verify that the transaction is closed before moving on.

Verification

1. Open a second database client session (e.g., psql, MySQL CLI, or another DataGrip console) connected to the same database.
2. Run a SELECT on the table you are modifying. While the transaction in DataGrip is active, you should see the data unchanged.
3. Commit the transaction in DataGrip, then repeat the SELECT in the second session; the changes should now be visible.
4. To test rollback, deliberately cause an error inside the transaction (e.g., SELECT 1/0;), then click Rollback. Verify with the second session that the table remains as it was before the transaction began.
5. Check DataGrip’s console status bar: when a transaction is active it displays “Transaction: active”. When idle, the message disappears.