SQL Component Part-3

 SQL Components:

• SQL (Structured Query Language) consists of several key components that facilitate the management and manipulation of relational databases.
• Here’s an overview of the primary components of SQL:

• TCL:

• Transaction Control Language (TCL) is a subset of SQL that deals with managing transactions in a database. 
• Transactions are sequences of one or more SQL statements that are treated as a single unit. 
• TCL commands help ensure that all operations in a transaction are executed successfully, and if not, they allow rolling back changes to maintain data consistency.

• Key Features of TCL:

• Helps manage how changes made by DML statements are applied.
• Ensures the integrity of data by committing changes only when all operations are successful.
• Provides mechanisms to undo changes in case of errors.

• Common TCL Commands:
• COMMIT: Saves all changes made in the current transaction to the database. Once a transaction is committed, the changes become permanent.
• ROLLBACK: Reverts the database to the state it was in before the current transaction, undoing any changes made since the last commit.
• SAVEPOINT: Creates a point within a transaction that you can later roll back to without affecting the rest of the transaction.
• SET TRANSACTION: Defines properties for the current transaction, such as its isolation level or whether it is read-only.

• COMMIT Command:

• The COMMIT command in SQL is part of the Transaction Control Language (TCL) and is used to make all changes made during the current transaction permanent in the database. 
• Once a transaction is committed, the changes cannot be rolled back, ensuring that the data modifications are finalized.

• Purpose:

• To save all changes made during a transaction.
• To finalize the modifications made to the database so that other users and transactions can see them.

• Syntax:

COMMIT;

• Example:

• Basic Usage:

UPDATE Employees SET Salary = Salary + 5000 WHERE Department = 'IT'; 

COMMIT;

• In this example, a salary increase for all employees in the "IT" department is made permanent with the COMMIT command.
• Multiple Operations in a Transaction:

BEGIN; -- Start a transaction  

INSERT INTO Employees (Name, Department, Salary) VALUES ('John', 'HR', 60000); 

UPDATE Employees SET Salary = Salary * 1.10 WHERE Name = 'Alice'; 

COMMIT; -- Save all changes made in this transaction

• Here, a new employee is added, and Alice's salary is increased. The COMMIT command ensures that both changes are saved permanently.
• Important Notes:
• Irreversible: 
• Once a COMMIT is executed, the changes cannot be undone. If an error occurs before committing, you can use ROLLBACK to undo the changes.
• Concurrency: 
• After committing, the changes become visible to other users and transactions.
• Transaction Finalization: 
• COMMIT is typically used at the end of a transaction to finalize all operations performed within it.

• ROLLBACK Command:

• The ROLLBACK command in SQL is part of the Transaction Control Language (TCL) and is used to undo changes made during the current transaction. 
• It reverts the database back to the state it was in before the transaction started or to a specific savepoint. 
• This ensures that any changes made during the transaction are discarded if they are not correct or if an error occurs.

• Purpose:

• To undo all the changes made during a transaction.
• To ensure that the database remains in a consistent state if an error occurs or if the changes should not be applied.

• Syntax:

ROLLBACK;

• To roll back to a specific savepoint:

ROLLBACK TO savepoint_name;

• Example:

• Basic Usage:

DELETE FROM Employees WHERE Department = 'Marketing'; 

ROLLBACK; -- Undo the deletion of all records from Marketing

• In this example, all employees from the Marketing department were deleted, but the ROLLBACK command undoes this operation.
• Using with Savepoint:

BEGIN; -- Start a transaction 

UPDATE Employees SET Salary = 60000 WHERE Name = 'John'; 

SAVEPOINT sp1; -- Set a savepoint 

UPDATE Employees SET Salary = 70000 WHERE Name = 'Alice'; 

ROLLBACK TO sp1; -- Undo Alice's salary update but keep John's change

• Here, a savepoint is created after John’s salary is updated. Alice’s update is undone using ROLLBACK TO sp1, but John’s salary change remains.

• Important Notes:

• Partial Rollbacks: 
• You can roll back to a specific savepoint to undo part of the transaction without affecting the entire set of changes.
• Transaction Scope: 
• A rollback undoes all operations in the current transaction unless a specific savepoint is provided.
• Error Handling: 
• ROLLBACK is often used when errors occur during a transaction, ensuring that no partial or incorrect data is committed.

• SAVEPOINT Command:

• The SAVEPOINT command in SQL is part of the Transaction Control Language (TCL) and is used to set a point within a transaction to which you can later roll back without affecting the entire transaction. 
• It allows you to partially undo changes made during a transaction, providing more control over complex operations.

• Purpose:

• To create a temporary marker in a transaction.
• To allow partial rollbacks to a specific point within a transaction without undoing the entire set of changes.
• Useful in managing large transactions with multiple operations.

• Syntax:

SAVEPOINT savepoint_name;

• savepoint_name: A user-defined name for the savepoint to identify it within the transaction.

• Example:

• Basic Usage:

BEGIN; -- Start a transaction 

INSERT INTO Employees (Name, Department, Salary) VALUES ('John', 'HR', 50000); 

SAVEPOINT sp1; -- Create a savepoint 

UPDATE Employees SET Salary = 60000 WHERE Name = 'Alice'; 

ROLLBACK TO sp1; -- Undo Alice's salary update, but keep John's insertion 

COMMIT; -- Finalize the transaction

• In this example, John's insertion is kept, but Alice's salary update is undone using the ROLLBACK TO sp1 command, reverting only changes made after the savepoint.

• Using Multiple Savepoints:

BEGIN; 

SAVEPOINT sp1; -- Create the first savepoint 

UPDATE Employees SET Salary = 60000 WHERE Name = 'Bob'; 

SAVEPOINT sp2; -- Create the second savepoint 

UPDATE Employees SET Department = 'Sales' WHERE Name = 'Charlie'; 

ROLLBACK TO sp1; -- Undo changes after sp1 (Bob's and Charlie's updates) 

COMMIT;

• Here, both Bob's salary update and Charlie's department change are rolled back when we rollback to sp1, effectively undoing everything after the first savepoint.

• Important Notes:

• Multiple Savepoints: 
• You can create multiple savepoints within a transaction and roll back to any of them.
• Transaction Scope: 
• Savepoints are valid only within the transaction in which they were created and can be rolled back to multiple times.
• Efficiency: 
• Using savepoints allows for efficient management of transactions, avoiding the need to undo all changes when only a subset of operations needs to be reverted.

• SET TRANSACTION Command:

• The SET TRANSACTION command in SQL is used to specify properties for the current transaction, such as its isolation level or whether it should be read-only or read-write. 
• This command is typically used at the beginning of a transaction to control its behavior and how it interacts with other transactions.

• Purpose:

• To control how the current transaction interacts with other concurrent transactions (isolation level).
• To define whether the transaction is allowed to modify data (read-only or read-write).
• To improve performance and data consistency depending on the needs of the transaction.

• Syntax:

SET TRANSACTION [READ WRITE | READ ONLY] 

[ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}];

• READ WRITE: Allows the transaction to modify the data (default mode).
• READ ONLY: Restricts the transaction to read operations only; it cannot modify the data.
• ISOLATION LEVEL: Controls the level of visibility that a transaction has of changes made by other concurrent transactions.

• Example:

• Setting a Read-Only Transaction:

SET TRANSACTION READ ONLY; 

SELECT * FROM Employees WHERE Department = 'HR'; COMMIT;

• In this example, the transaction is set to READ ONLY, which means the transaction can only read data and not modify it.

• Setting a Serializable Isolation Level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'IT'; 

COMMIT;

• Here, the transaction's isolation level is set to SERIALIZABLE, the strictest level, ensuring that no other transaction can modify the data being read or updated by this transaction until it is completed.

• Isolation Levels:

• READ UNCOMMITTED: 
• Allows transactions to read uncommitted changes made by other transactions. This can lead to "dirty reads."
• READ COMMITTED: 
• Ensures that a transaction can only read committed data from other transactions (avoids dirty reads).
• REPEATABLE READ: 
• Guarantees that if a transaction reads data twice, the data will not change in between the two reads (avoids non-repeatable reads).
• SERIALIZABLE: 
• The highest level of isolation; ensures complete transaction isolation, where transactions are executed in a sequence as if they were running one after the other.

• Important Notes:

• Performance Considerations: 
• Higher isolation levels like SERIALIZABLE can provide more data consistency but may reduce performance due to locking.
• Read-Only Transactions: 
• Using READ ONLY can optimize queries that do not need to modify data, improving performance in read-heavy scenarios.