Prev Next

Transactions

A TRANSACTION in SQL is a sequence of operations performed as a single unit of work. Transactions ensure data integrity and consistency by following the ACID properties: Atomicity, Consistency, Isolation, and Durability.

ACID Properties

  • Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.
  • Consistency: Ensures the database remains in a consistent state before and after the transaction.
  • Isolation: Ensures transactions are isolated from each other, preventing data corruption.
  • Durability: Ensures that once a transaction is committed, it remains permanent, even in case of a system failure.

Using Transactions in SQL

Transactions are typically managed using the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

Example: Using a Transaction

BEGIN TRANSACTION;

UPDATE FreedomFighters SET City = 'Mumbai' WHERE Name = 'Mahatma Gandhi';

-- Check some condition and decide to commit or rollback
IF @@ERROR = 0
    COMMIT;
ELSE
    ROLLBACK;

Explanation: This transaction updates the city of Mahatma Gandhi to Mumbai. If the update is successful, the changes are committed; otherwise, they are rolled back.

Savepoints in Transactions

SAVEPOINT allows you to create a point within a transaction to which you can later roll back.

Example: Using SAVEPOINT

BEGIN TRANSACTION;

UPDATE FreedomFighters SET City = 'Delhi' WHERE Name = 'Subhas Chandra Bose';
SAVEPOINT Savepoint1;

UPDATE FreedomFighters SET City = 'Ahmedabad' WHERE Name = 'Bhagat Singh';
-- Rollback to Savepoint1 if needed
ROLLBACK TO Savepoint1;

COMMIT;

Explanation: The first update is saved, but the second update is rolled back to Savepoint1, and then the transaction is committed.

Do's and Don'ts

Do's

  • Use transactions to ensure data integrity in critical operations.
  • Test transactions thoroughly to ensure they work as expected.
  • Use savepoints to create logical checkpoints within a transaction.

Don'ts

  • Don't leave transactions open for a long time; it can lock resources and degrade performance.
  • Don't forget to handle errors and roll back transactions when necessary.
  • Don't use transactions excessively for operations that do not require them.
Prev Next