TCL Statements

Introduction of TCL Statements

• TCL statements play a crucial role in managing transactions in a DBMS, providing mechanisms to ensure data integrity and consistency.

Definition

• In a Database Management System (DBMS), Transaction Control Language (TCL) statements are used to manage the changes made by Data Manipulation Language (DML) statements.
• TCL (Transaction Control Language) statements in DBMS (Database Management System) are used to manage transactions within a database.

Characteristics

• TCL statements are essential in managing the integrity and consistency of databases by controlling transactions effectively.
• TCL statements ensure the integrity of the database by allowing users to control transactions.
• Transactions are sequences of operations performed as a single logical unit of work, which must either be fully completed or fully failed, ensuring data integrity and consistency.

Importance/Advantages 

• Data Integrity: This statement ensures that transactions are completed successfully and consistently, maintaining the integrity of the database.
• Error Handling: This statement allows recovery from errors by rolling back incomplete transactions, and preventing partial updates.
• Consistency: This statement helps maintain a consistent state of the database by committing only fully successful transactions.
• Control: This statement provides finer control over transaction management with savepoints, allowing partial rollbacks without affecting the entire transaction.

Examples

• The common examples of TCL statements are – COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT, and SET TRANSACTION, allowing for effective control over database transactions.

• COMMIT
  • The COMMIT statement saves all changes made during the current transaction to the database.
  • Once a COMMIT is issued, the changes become permanent and visible to other users.
  • Syntax : COMMIT;
  • Example

BEGIN TRANSACTION;

UPDATE employees SET salary = salary + 500 WHERE department_id = 101;
COMMIT;

(The above example increases the salary of employees in department 101 by 500 and then saves the changes permanently.)

• ROLLBACK
  • The ROLLBACK statement undoes/reverses all changes made in the current transaction.
  • This statement is useful for reverting the database to its previous state in case of an error or other conditions requiring a transaction to be discarded.
  • Syntax : ROLLBACK;
  • Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = salary + 500 WHERE department_id = 101;

— An error may occur here
ROLLBACK;

(This example increases the salary of employees in department 101 by 500 but then undoes the change, leaving the database unchanged.)

• SAVEPOINT
  • The SAVEPOINT statement sets a point within a transaction to which you can later roll back. This allows finer control over the transaction, enabling partial rollbacks.
  • Syntax : SAVEPOINT savepoint_name;
  • Example : 

UPDATE employees SET salary = salary + 500 WHERE department_id = 101;
SAVEPOINT before_bonus;

UPDATE employees SET bonus = bonus + 1000 WHERE department_id = 101;
ROLLBACK TO before_bonus;

(In the above example, salaries are increased, a savepoint is set, and then bonuses are updated. If the bonus update needs to be undone, the ROLLBACK TO before_bonus statement reverts changes only to the point of the savepoint, keeping the salary increase intact.)

• RELEASE/ROLLBACK SAVEPOINT
  • The RELEASE SAVEPOINT statement removes a previously defined savepoint from the current transaction. This action ensures that the savepoint can no longer be used in the transaction.
  • Syntax : RELEASE SAVEPOINT savepoint_name;
  • Example: 

SAVEPOINT before_bonus;
UPDATE employees SET bonus = bonus + 1000 WHERE department_id = 101;
RELEASE SAVEPOINT before_bonus;

(This example sets a savepoint, updates bonuses, and then releases the savepoint, ensuring that it cannot be used for a rollback later in the transaction.)

• SET TRANSACTION
  • The SET TRANSACTION statement is used to set the properties of the current transaction, such as isolation level.
  • Syntax : SET TRANSACTION [ READ WRITE | READ ONLY | ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ } ];
  • Example

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

(This example sets the isolation level of the current transaction to SERIALIZABLE, ensuring the highest level of isolation from other transactions.)