Transactions and Rollback

Transactions and rollbacks are fundamental concepts for ensuring data integrity and consistency, especially in environments where multiple operations must succeed or fail as a unit.

What is a Transaction?

A transaction is a sequence of one or more SQL operations (e.g., INSERT, UPDATE, DELETE, DROP) that are executed as a single logical unit of work. Transactions ensure that all operations either complete successfully (commit) or fail entirely (rollback), maintaining the ACID properties:

• Atomicity: All operations in a transaction are completed; if one fails, all are rolled back.
• Consistency: The database remains in a consistent state before and after the transaction.
• Isolation: Transactions are isolated from each other until they are completed.
• Durability: Once committed, changes are permanent, even in the event of a system failure.

Transaction Commands

VanadiumDB provides the following commands to manage transactions:

START TRANSACTION;

Starts a new transaction.

COMMIT

Saves all changes made during the transaction permanently.

ROLLBACK

Undoes all changes made during the transaction.

ROLLBACK works in VanadiumDB for DROP TABLE, DROP DATABASE or DROP USER!

How Transactions Work

Basic Example

START TRANSACTION;

-- Transfer $100 from account A to account B
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

If both UPDATE statements succeed, the transaction is committed, and the changes are permanent.
If either UPDATE fails, the transaction is rolled back, and no changes are applied.

Using ROLLBACK

START TRANSACTION;

-- Deduct $100 from account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Simulate an error (e.g., insufficient funds)
-- This could be a constraint violation or an explicit check in your application
ROLLBACK;

The ROLLBACK undoes the UPDATE statement, and the balance of account A remains unchanged.

Best Practices

• Keep transactions short: Long-running transactions can block other users and reduce performance.
• Avoid user input during transactions: User delays can hold locks unnecessarily.