Guide to Transaction Control Language (TCL) in SQL

Transaction Control Language (TCL) is a set of commands in SQL that are used to manage transactions in a database. Transactions are units of work that are executed on a database, and the purpose of TCL is to control the outcome of these transactions. In this guide, we will cover the various TCL commands and their usage in managing transactions.

The three main TCL commands are:

1. COMMIT: The COMMIT command is used to end a transaction and permanently save the changes made to the database. When a COMMIT command is issued, all the changes made to the database during the transaction are saved permanently. The syntax for the COMMIT command is as follows:

   COMMIT;

2. ROLLBACK: The ROLLBACK command is used to undo the changes made during a transaction and restore the database to its previous state. When a ROLLBACK command is issued, all the changes made during the transaction are undone, and the database is returned to its previous state. The syntax for the ROLLBACK command is as follows:

   ROLLBACK;

3. SAVEPOINT: The SAVEPOINT command is used to create a point in a transaction that can be used to roll back to a specific point in the transaction. SAVEPOINT is used in conjunction with the ROLLBACK command to undo a portion of a transaction without undoing the entire transaction. The syntax for the SAVEPOINT command is as follows:

   SAVEPOINT savepoint_name;

   To roll back to a savepoint, the following syntax is used:

   ROLLBACK TO savepoint_name;

Now let's look at some examples to better understand the usage of these commands.

Example 1: Using COMMIT

Suppose we have a table called 'employees' and we want to update the salary of all employees whose id is greater than 100. We start a transaction using the
BEGIN command and execute the update query. We then issue the COMMIT command to save the changes to the database. Here is an example:

BEGIN;
UPDATE employees SET salary = salary * 1.1 WHERE id > 100;
COMMIT;

Example 2: Using ROLLBACK

Suppose we have a table called 'customers' and we want to insert a new record into the table. We start a transaction using the BEGIN command and execute the insert query. We then realize that we have made a mistake and want to undo the changes. We issue the ROLLBACK command to undo the insert query. Here is an example:

BEGIN;
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');
ROLLBACK;

Example 3: Using SAVEPOINT

Suppose we have a table called 'orders' and we want to update the status of an order. We start a transaction using the BEGIN command and execute the update query. We then create a savepoint using the SAVEPOINT command. We then execute another update query, but this time we realize that we made a mistake and want to undo only the second update query. We use the ROLLBACK TO command to roll back to the savepoint we created earlier. Here is an example:

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 100;
SAVEPOINT update_status;
UPDATE orders SET status = 'delivered' WHERE id = 100;
-- realize we made a mistake, undo the last update
ROLLBACK TO update_status;
COMMIT;

In conclusion, TCL commands are used to manage transactions in a database. The COMMIT command is used to permanently save changes to the database, the ROLLBACK command is used to undo changes made during a transaction, and the SAVEPOINT command is used to create a point in a transaction that can be used to roll back to a specific point in the transaction.