Transaction Control

DBCode provides comprehensive transaction control for databases that support transactions, allowing you to manage auto-commit behavior, track uncommitted changes, and safely commit or rollback your database operations.

Transaction control enables you to group multiple SQL statements into a single atomic operation, ensuring data consistency and integrity. With auto-commit turned off, you can execute multiple queries and review their effects before permanently committing the changes to the database.

Understanding Auto-Commit

Auto-commit is a mode that determines how database changes are handled:

  • Auto-Commit ON (Default): Each SQL statement is automatically committed to the database immediately after execution. This is the standard behavior for most database operations.

  • Auto-Commit OFF: SQL statements are executed but not committed automatically. Changes remain pending until you explicitly commit or rollback the transaction. This allows you to:

    • Execute multiple related queries as a single unit of work
    • Review the effects of your changes before making them permanent
    • Rollback changes if something goes wrong

Transaction Status Bar

When auto-commit is turned off, DBCode displays a transaction status indicator in the VS Code status bar showing:

  • Current auto-commit state (ON/OFF)
  • Number of uncommitted statements
  • Total rows affected by uncommitted changes

Example: đź”’ Auto-Commit: OFF (3 statements, 15 rows)

Click the status bar indicator to access quick actions for managing your transaction.

Managing Transactions

Turning Auto-Commit Off

To start managing transactions manually:

  1. Open a SQL file connected to a database that supports transactions.

  2. Click the transaction status indicator in the status bar.

  3. Select “Turn Auto-Commit OFF” from the menu.

DBCode will start a transaction, and subsequent queries will not be committed automatically. The status bar will update to show đź”’ Auto-Commit: OFF along with the count of uncommitted changes.

Committing Changes

When you’re ready to permanently save your changes:

  1. Click the transaction status indicator in the status bar.

  2. Select “Commit Transaction” from the menu.

  3. All uncommitted changes will be permanently saved to the database.

Alternative: Execute a COMMIT; statement in your SQL editor.

Rolling Back Changes

If you need to undo uncommitted changes:

  1. Click the transaction status indicator in the status bar.

  2. Select “Rollback Transaction” from the menu.

  3. All uncommitted changes will be discarded, and the database will return to its state before the transaction began.

Alternative: Execute a ROLLBACK; statement in your SQL editor.

Turning Auto-Commit Back On

When you have uncommitted changes and want to commit them and return to auto-commit mode:

  1. Click the transaction status indicator in the status bar.

  2. Select “Turn Auto-Commit ON” from the menu.

  3. DBCode will commit all uncommitted changes and return to auto-commit mode.

Alternatively, you can commit or rollback your changes first, then turn auto-commit back on.

Tracking Uncommitted Changes

DBCode automatically tracks all data modification operations performed while auto-commit is off:

  • INSERT statements - Shows number of rows inserted
  • UPDATE statements - Shows number of rows modified
  • DELETE statements - Shows number of rows removed

The status bar displays a real-time summary of uncommitted changes. Hover over the status bar indicator to view a list of uncommitted operations in a tooltip, showing the SQL statement, statement type, and number of rows affected for each change.

Using SQL Transaction Statements

In addition to the UI controls, you can manage transactions directly with SQL statements:

Starting a Transaction

Execute BEGIN; or BEGIN TRANSACTION; to explicitly start a transaction:

BEGIN;

This automatically turns auto-commit off for the current editor.

Committing a Transaction

Execute COMMIT; or COMMIT TRANSACTION; to save all changes:

COMMIT;

Rolling Back a Transaction

Execute ROLLBACK; or ROLLBACK TRANSACTION; to discard all changes:

ROLLBACK;

Example Transaction Workflow

-- Start transaction
BEGIN;


-- Make changes
UPDATE users SET status = 'active' WHERE last_login > '2024-01-01';
INSERT INTO audit_log (action, timestamp) VALUES ('bulk_activation', NOW());
DELETE FROM sessions WHERE expired = true;


-- Review the changes (optional - check affected rows)


-- Commit if everything looks good
COMMIT;


-- Or rollback if you need to undo
-- ROLLBACK;

Transaction Settings

DBCode provides settings to customize transaction behavior:

Long Transaction Warning

Setting: dbcode.transactions.longTransactionThreshold Default: 5 (minutes) Range: 0-60 minutes

DBCode will warn you when a transaction has been open for longer than the specified threshold. This helps prevent accidentally leaving transactions open, which can lock database resources.

Set to 0 to disable warnings.

Example:

{
  "dbcode.transactions.longTransactionThreshold": 10
}

Auto-Rollback on Error

Setting: dbcode.transactions.autoRollbackOnError Default: false

When enabled, DBCode will automatically rollback the current transaction if a query fails with an error. This is particularly useful for databases like PostgreSQL that abort transactions on error.

Note: Use this setting with caution, as it will discard all uncommitted changes when any query fails.

Example:

{
  "dbcode.transactions.autoRollbackOnError": true
}

Database Compatibility

Transaction control is available for databases that support transactions. The feature automatically detects whether your connected database supports transaction control and enables the functionality accordingly.

Benefits of Transaction Control

  • Data Integrity: Group related operations into atomic units that either all succeed or all fail together.

  • Safe Testing: Test changes in a transaction before committing, allowing you to rollback if results aren’t as expected.

  • Error Recovery: Easily undo changes if something goes wrong during multi-step operations.

  • Consistency: Ensure related changes are applied together, maintaining referential integrity across tables.

  • Development Workflow: Experiment with data modifications safely, knowing you can always rollback to the previous state.

  • Real-Time Feedback: Track exactly what changes you’ve made and how many rows were affected before committing.

Transaction control in DBCode brings enterprise-grade database transaction management directly into Visual Studio Code, enabling safer and more confident database operations during development and maintenance tasks.