BEGIN

In SQL, a transaction refers to a unit of work performed against a database. Transactions in SQL are used to ensure the integrity of the database. The keywords used in SQL to control the transactions are BEGIN, COMMIT, ROLLBACK.
BEGIN

In the context of SQL transactions, BEGIN is a keyword used to start a transaction. It marks the point at which the data referenced by a connection is logically consistent. After the BEGIN statement, the transaction is considered to be “open” and remains so until it is committed or rolled back.

Once you’ve initiated a transaction with BEGIN, all the subsequent SQL statements will be a part of this transaction until an explicit COMMIT or ROLLBACK is given.
Syntax

The syntax to start a transaction is:

BEGIN TRANSACTION;

//or simply,

BEGIN;

BEGIN;

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

COMMIT;

COMMIT

The SQL COMMIT command is used to save all the modifications made by the current transaction to the database. A COMMIT command ends the current transaction and makes permanent all changes performed in the transaction. It is a way of ending your transaction and saving your changes to the database.

After the SQL COMMIT statement is executed, it can not be rolled back, which means you can’t undo the operations. COMMIT command is used when the user is satisfied with the changes made in the transaction, and these changes can now be made permanent in the database.

//syntax
COMMIT;

Syntax with ROLLBACK:

START TRANSACTION;
UPDATE Account SET amount = amount - 2000 WHERE name = 'A';
UPDATE Account SET amount = amount + 2000 WHERE name = 'B';
IF @@ERROR != 0 
   ROLLBACK  
ELSE 
   COMMIT;

ROLLBACK

The ROLLBACK command is a transactional control language (TCL) instruction that undoes an unsuccessful or unsatisfactory running transaction. This process also applies to SQL Server where all individual statements in SQL Server are treated as a single atomic transaction.

When a ROLLBACK command is issued, all the operations (such as Insert, Delete, Update, etc.) are undone and the database is restored to its initial state before the transaction started.

When to use ROLLBACK

If the transaction is unacceptable or unsuccessful.
If you want to revert the unwanted changes.

BEGIN TRANSACTION;  

-- This would delete all rows from the table.
DELETE FROM Employee;

-- Oh no! That's not what I wanted. Let's roll that back.
ROLLBACK;

SQL also allows the usage of SAVEPOINTs along with the ROLLBACK command, which allows rolling back to a specific point in a transaction, instead of rolling back the entire transaction.

BEGIN TRANSACTION;

-- Adding new employee.
INSERT INTO Employee(ID, Name) VALUES(1, 'John');

-- Create a savepoint to be able to roll back to this point.
SAVEPOINT SP1;

-- Oh no! I made a mistake creating this employee. Let's roll back to the savepoint.
ROLLBACK TO SAVEPOINT SP1;

-- Now I can try again.
INSERT INTO Employee(ID, Name) VALUES(1, 'Jack');

-- Commit the changes.
COMMIT;

In this example, ROLLBACK TO SAVEPOINT SP1 would undo the first insert into the Employee table while preserving the state of the database as it was at the savepoint SP1. So, the second insert command would properly add ‘Jack’ in place of ‘John’.

SAVEPOINT

A savepoint is a way of implementing subtransactions (nested transactions) within a relational database management system by indicating a particular point within a transaction that a user can “roll back” to in case of failure. The main property of a savepoint is that it enables you to create a rollback segment within a transaction. This allows you to revert the changes made to the database after the Savepoint without having to discard the entire transaction.

A Savepoint might be used in instances where if a particular operation fails, you would like to revert the database to the state it was in before the operation was attempted, but you do not want to give up on the entire transaction.

//syntax
SAVEPOINT savepoint_name;

START TRANSACTION;
INSERT INTO Table1 (Column1) VALUES ('Value1');

SAVEPOINT SP1;

INSERT INTO Table1 (Column1) VALUES ('Value2');

ROLLBACK TO SP1;

COMMIT;

Release Savepoint

The RELEASE SAVEPOINT deletes a savepoint within a transaction.

RELEASE SAVEPOINT savepoint_name;

The action of releasing a savepoint removes the named savepoint from the set of savepoints of the current transaction. No changes are undone.

Remove Savepoint

The ROLLBACK TO SAVEPOINT removes a savepoint within a transaction.

ROLLBACK TRANSACTION TO savepoint_name;

This statement rolls back a transaction to the named savepoint without terminating the transaction.

Please note, savepoint names are not case sensitive and must obey the syntax rules of the server.

SQL - Transactions