Learning of SQL Day 30

 

Day 30: Transactions

Introduction:

• Transactions in SQL are used to ensure that a series of operations are executed as a single unit of work. Transactions help maintain the integrity of the database by ensuring that either all operations are successfully completed, or none of them are applied.

Key Concepts:

• Transaction: A sequence of one or more SQL operations treated as a single unit.

• BEGIN TRANSACTION: Starts a new transaction.

• COMMIT: Ends the transaction and makes all changes permanent.

• ROLLBACK: Undoes the changes made by the current transaction.

• ACID Properties: Ensures that transactions are processed reliably. These properties are Atomicity, Consistency, Isolation, and Durability.

SQL Commands and Examples:

1. Starting a Transaction:

   • Begin a transaction using the BEGIN TRANSACTION statement.

sql

-- Start a transaction
BEGIN TRANSACTION;

2. Committing a Transaction:

   • Commit the transaction to make all changes permanent.

sql

-- Commit the transaction
COMMIT;

3. Rolling Back a Transaction:

   • Rollback the transaction to undo all changes made within the transaction.

sql

-- Rollback the transaction
ROLLBACK;

4. Example of a Transaction:

   • Perform a sequence of operations within a transaction.

sql

-- Example of a transaction to transfer credits from one course to another
BEGIN TRANSACTION;

-- Subtract credits from CourseID 101
UPDATE Courses
SET Credits = Credits - 1
WHERE CourseID = 101;

-- Add credits to CourseID 102
UPDATE Courses
SET Credits = Credits + 1
WHERE CourseID = 102;

-- Commit the transaction if no errors
COMMIT;

-- Rollback the transaction in case of errors
ROLLBACK;

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to begin a transaction.

3. Write a query to update the age of a student and then commit the transaction.

4. Write a query to update the age of a student and then rollback the transaction.

sql

-- Begin a transaction
BEGIN TRANSACTION;

-- Update the age of a student
UPDATE Students
SET Age = 22
WHERE StudentID = 1;

-- Commit the transaction
COMMIT;

-- Begin a new transaction
BEGIN TRANSACTION;

-- Update the age of a student
UPDATE Students
SET Age = 23
WHERE StudentID = 2;

-- Rollback the transaction
ROLLBACK;

Important Tips:

• Always use transactions when performing multiple related operations to ensure the integrity of the database.

• Use COMMIT to make the changes permanent only if all operations are successful.

• Use ROLLBACK to undo the changes if any operation fails, ensuring the database remains consistent.

Understanding transactions and how to use them effectively is crucial for maintaining data integrity and ensuring reliable database operations. Keep practicing to master transaction management! 💼🔒📘

Feel free to explore and experiment with different transaction scenarios to reinforce your understanding. Well done on making it to Day 30! 🎉🚀📊