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

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more