Transaction Control Language

A transaction in SQL is a sequence of one or more SQL statements treated as a single unit of work. Transactions are used to ensure data consistency and integrity in a database.

Basic Syntax

BEGIN TRANSACTION;
-- SQL statements
COMMIT;

• BEGIN TRANSACTION: Starts a new transaction.
• COMMIT: Ends the current transaction and makes all changes permanent.
• ROLLBACK: Ends the current transaction and undoes all changes made since the transaction started.
• SAVEPOINT: Creates a savepoint within a transaction that can be used to roll back to a specific point in the transaction.

Example 1 - Basic Transaction

If we want to update the “customer” table and the “order” table as part of a single transaction, we can use the following SQL:

BEGIN TRANSACTION;

UPDATE customer
SET country = 'USA'
WHERE name = 'John';

UPDATE order
SET status = 'Shipped'
WHERE customer_id = 1;

COMMIT;

This will update the “customer” table and the “order” table as part of a single transaction. All changes will be rolled back if any errors occur during the transaction.

Example 2 - Rollback Transaction

If we want to update the “customer” table and the “order” table as part of a single transaction, but we want to roll back the changes if any errors occur, we can use the following SQL:

BEGIN TRANSACTION;

UPDATE customer
SET country = 'USA'
WHERE name = 'John';

UPDATE order
SET status = 'Shipped'
WHERE customer_id = 1;

IF @@ERROR <> 0
BEGIN
    ROLLBACK;
END
ELSE
BEGIN
    COMMIT;
END

This will update the “customer” table and the “order” table as part of a single transaction. All changes will be rolled back if any errors occur during the transaction.

Example 3 - Savepoint

If we want to create a savepoint within a transaction that we can roll back to if necessary, we can use the following SQL:

BEGIN TRANSACTION;

UPDATE customer
SET country = 'USA'
WHERE name = 'John';

SAVEPOINT update_order;

UPDATE order
SET status = 'Shipped'
WHERE customer_id = 1;

IF @@ERROR <> 0
BEGIN
    ROLLBACK TO update_order;
END
ELSE
BEGIN
    COMMIT;
END

This will update the “customer” table and create a savepoint called “update_order”. If errors occur during the second update statement, the transaction will be rolled back to the “update_order” savepoint.

Further Readings

1. MySQL Transactions - MySQL Tutorial
2. BEGIN TRANSACTION (Transact-SQL) - Microsoft Docs
3. COMMIT TRANSACTION (Transact-SQL) - Microsoft Docs
4. ROLLBACK TRANSACTION (Transact-SQL) - Microsoft Docs
5. SAVE TRANSACTION (Transact-SQL) - Microsoft Docs