TCL Commands in MySQL

Transaction Control Language (TCL) commands in MySQL are used to manage transactions in the database. Transactions allow you to execute a series of SQL statements as a single unit of work, ensuring that the database remains in a consistent state. The primary TCL commands in MySQL are START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT.

1.START TRANSACTION
Begins a new transaction.

START TRANSACTION;

2. COMMIT

Saves all changes made during the current transaction. Once a COMMIT is issued, the changes are permanent and visible to other transactions.

COMMIT;

3. ROLLBACK

Reverts all changes made during the current transaction. This command is used to undo changes if an error occurs or if the transaction needs to be aborted.

ROLLBACK;

4. SAVEPOINT

Sets a point within a transaction to which you can later roll back. This is useful for creating sub-transactions or checkpoints within a larger transaction.

SAVEPOINT savepoint_name;


5. ROLLBACK TO SAVEPOINT

Rolls back the transaction to the specified savepoint, undoing changes made after that savepoint was set.

ROLLBACK TO SAVEPOINT savepoint_name;


6. RELEASE SAVEPOINT

Removes a previously defined savepoint. Once a savepoint is released, you cannot roll back to it.

RELEASE SAVEPOINT savepoint_name;


Example
-- Begin a new transaction
START TRANSACTION;

-- Insert a new customer
INSERT INTO Customers (name, age) VALUES ('Tom Hardy', 40);

-- Create a savepoint
SAVEPOINT sp1;

-- Insert a product for the new customer
INSERT INTO Products (customer_id, name) VALUES (LAST_INSERT_ID(), 'Product X');

-- Rollback to the savepoint
ROLLBACK TO SAVEPOINT sp1;

-- Insert another product for the new customer
INSERT INTO Products (customer_id, name) VALUES (LAST_INSERT_ID(), 'Product Y');

-- Commit the transaction
COMMIT;

Explanation
START TRANSACTION: Begins a new transaction.
INSERT INTO Customers: Inserts a new customer into the Customers table.
SAVEPOINT sp1: Creates a savepoint named sp1.
INSERT INTO Products: Attempts to insert a product for the newly added customer.
ROLLBACK TO SAVEPOINT sp1: Rolls back the transaction to the savepoint sp1, undoing the insertion of the first product.
INSERT INTO Products: Inserts a different product for the newly added customer.
COMMIT: Commits the transaction, saving all changes made since the transaction started.

Using TCL commands allows you to ensure that a series of operations either complete successfully as a whole or leave the database unchanged if any part of the transaction fails. This is crucial for maintaining data integrity and consistency in your database.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more