Transaction Control

Consider the following schema

Part A
CREATE TABLE Accounts (
    AccNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts VALUES
(101, 'Raj', 10000),
(102, 'Meena', 15000
(103, 'Karan', 20000);


Q1. Start a transaction where:
  • Raj transfers 2000 to Meena.
  • After the transfer, commit the transaction.
  • Show the balances of all accounts.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 2000 WHERE AccNo = 101; -- Raj
UPDATE Accounts SET Balance = Balance + 2000 WHERE AccNo = 102; -- Meena
COMMIT;
SELECT * FROM Accounts;


Q2. Start a transaction where:
  • Raj transfers 5000 to Karan.
  • Before committing, you realize Raj doesn’t have sufficient balance and rollback the transaction.
  • Show the balances of all accounts after rollback.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccNo = 101; -- Raj
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccNo = 103; -- Karan
ROLLBACK;
SELECT * FROM Accounts;



Q3. Use SAVEPOINT:
  • Raj deposits 1000, then set a SAVEPOINT sp1.
  • Next, Meena withdraws 2000, then set a SAVEPOINT sp2.
  • Then, Karan withdraws 5000.
  • Rollback to sp2.
  • Show the final balances of all accounts.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccNo = 101; -- Raj
SAVEPOINT sp1;

UPDATE Accounts SET Balance = Balance - 2000 WHERE AccNo = 102; -- Meena
SAVEPOINT sp2;

UPDATE Accounts SET Balance = Balance - 5000 WHERE AccNo = 103; -- Karan

ROLLBACK TO sp2;
COMMIT;
SELECT * FROM Accounts;



Part B:
Your college uses a table StudentFees:
CREATE TABLE StudentFees (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    PaidAmount DECIMAL(10,2)
);

INSERT INTO StudentFees VALUES
(1, 'Amit', 20000),
(2, 'Sneha', 15000),
(3, 'Rohit', 10000);


A transaction is carried out as follows:
  1. Sneha pays an additional 5000.
  2. Rohit pays an additional 7000.
  3. Set a SAVEPOINT after these operations
  4. By mistake, Amit’s fee is deducted by 5000.
  5. Rollback to the SAVEPOINT.
  6. Commit the transaction.

START TRANSACTION;
UPDATE StudentFees SET PaidAmount = PaidAmount + 5000 WHERE RollNo = 2; -- Sneha
UPDATE StudentFees SET PaidAmount = PaidAmount + 7000 WHERE RollNo = 3; -- Rohit
SAVEPOINT sp_fees;

UPDATE StudentFees SET PaidAmount = PaidAmount - 5000 WHERE RollNo = 1; -- Mistake Amit

ROLLBACK TO sp_fees;
COMMIT;
SELECT * FROM StudentFees;



Assignment: Transaction Control in E-Commerce 
Website
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Stock INT,
    Price DECIMAL(10,2)
);

CREATE TABLE Customers (
    CustID INT PRIMARY KEY,
    Name VARCHAR(50),
    Balance DECIMAL(10,2)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustID INT,
    ProductID INT,
    Quantity INT,
    Amount DECIMAL(10,2)
);



INSERT INTO Products VALUES
(1, 'Laptop', 10, 50000),
(2, 'Mobile', 20, 20000),
(3, 'Headphones', 30, 2000);

INSERT INTO Customers VALUES
(101, 'Amit', 80000),
(102, 'Sneha', 30000),
(103, 'Raj', 15000);

INSERT INTO Orders VALUES
(1, 101, 1, 1, 50000),  -- Amit bought 1 Laptop
(2, 102, 2, 1, 20000);  -- Sneha bought 1 Mobile



Q1. Successful Transaction
  • Customer Raj (CustID=103) buys 2 Headphones.
  • Deduct amount from Raj’s balance.
  • Reduce stock of Headphones.
  • Insert order record.
  • Commit the transaction.
  • Show updated tables.
START TRANSACTION;

UPDATE Customers SET Balance = Balance - (2*2000) WHERE CustID = 103; -- Raj
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 3; -- Headphones
INSERT INTO Orders VALUES (3, 103, 3, 2, 4000);

COMMIT;

SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;


Q2. Rollback Example
  • Customer Sneha (CustID=102) tries to buy 2 Laptops.
  • Laptop cost is more than her balance.
  • After deduction, rollback the transaction.
  • Show that her balance and product stock remain unchanged.
START TRANSACTION;

UPDATE Customers SET Balance = Balance - (2*50000) WHERE CustID = 102; -- Sneha
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 1;
INSERT INTO Orders VALUES (4, 102, 1, 2, 100000);

ROLLBACK;

SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;



Q3. Using SAVEPOINT
  • Amit buys 1 Mobile.
  • Set SAVEPOINT sp1.
  • Then he adds 1 Headphone to the same order.
  • Set SAVEPOINT sp2.
  • Next, Amit tries to add 1 Laptop (but balance insufficient).
  • Rollback to sp2 and commit.
  • Show the final state of orders, customers, and products.
START TRANSACTION;

UPDATE Customers SET Balance = Balance - 20000 WHERE CustID = 101; -- Amit buys Mobile
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 2;
INSERT INTO Orders VALUES (5, 101, 2, 1, 20000);
SAVEPOINT sp1;

UPDATE Customers SET Balance = Balance - 2000 WHERE CustID = 101; -- Amit buys Headphone
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 3;
INSERT INTO Orders VALUES (6, 101, 3, 1, 2000);
SAVEPOINT sp2;

UPDATE Customers SET Balance = Balance - 50000 WHERE CustID = 101; -- Amit tries Laptop
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 1;
INSERT INTO Orders VALUES (7, 101, 1, 1, 50000);

ROLLBACK TO sp2;
COMMIT;

SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;



Q4. During a Festival Sale:
  1. Sneha buys 1 Mobile and 2 Headphones.
  2. Raj buys 1 Laptop.
  3. Create SAVEPOINT sale_sp.
  4. Amit mistakenly charged for 2 Mobiles (not ordered).
  5. Rollback to sale_sp.
  6. Commit the correct transactions.
  7. Show the final state of all three tables.

START TRANSACTION;

-- Sneha
UPDATE Customers SET Balance = Balance - (20000 + 2*2000) WHERE CustID = 102;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 2;
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 3;
INSERT INTO Orders VALUES (8, 102, 2, 1, 20000);
INSERT INTO Orders VALUES (9, 102, 3, 2, 4000);

-- Raj
UPDATE Customers SET Balance = Balance - 50000 WHERE CustID = 103;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 1;
INSERT INTO Orders VALUES (10, 103, 1, 1, 50000);

SAVEPOINT sale_sp;

-- Mistake Amit charged for 2 Mobiles
UPDATE Customers SET Balance = Balance - 40000 WHERE CustID = 101;
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 2;
INSERT INTO Orders VALUES (11, 101, 2, 2, 40000);

ROLLBACK TO sale_sp;
COMMIT;

SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;






Comments

Post a Comment

Popular posts from this blog

Data Control Language

 Data Control Language (DCL) 🔹 Step 1: Login as Admin (root) Open MySQL command line and login as root (or another admin account): mysql -u root -p Enter your root password. 🔹 Step 2: Create Database and Table CREATE DATABASE UniversityDB; USE UniversityDB; CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR ( 50 ), Marks INT ); INSERT INTO Students VALUES ( 1 , 'Amit' , 85 ), ( 2 , 'Raj' , 70 ), ( 3 , 'Neha' , 92 ), ( 4 , 'Sneha' , 60 ); 🔹   Step 3: Create Users CREATE USER 'user1' @ 'localhost' IDENTIFIED BY 'pass1' ; CREATE USER 'user2' @ 'localhost' IDENTIFIED BY 'pass2' ; CREATE USER 'user3' @ 'localhost' IDENTIFIED BY 'pass3' ; 'localhost' means these users can log in only from the same computer where MySQL is running. If you want t...
Read more

Checkpoint

Image
  What is a Checkpoint in DBMS? A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash. It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file . Purpose of Checkpoint Reduce recovery time — only redo or undo transactions after the last checkpoint. Ensure consistency between database and log files. Flush dirty pages (modified data not yet written to disk). Mark a safe state from which the system can recover. How It Works  When a checkpoint occurs: All committed transactions before the checkpoint are written to disk . The checkpoint record is written to the log file . Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs). During recovery: Start from the last checkpoint , not from the b...
Read more

SUB Queries

 SUB Queries in SQL Table 1:  Students student_id name age department 1 Asha 19 Computer 2 Ravi 20 Mechanical 3 Priya 21 Computer 4 Suresh 20 Civil 5 Meena 19 Computer Table 2: Marks mark_id student_id subject marks 1 1 DBMS 85 2 1 Networks 78 3 2 DBMS 69 4 2 Networks 75 5 3 DBMS 92 6 3 Networks 88 7 4 DBMS 55 8 4 Networks 60 9 5 DBMS 70 10 5 Networks 72 CREATE TABLE Students (     student_id INT PRIMARY KEY,     name VARCHAR(50) NOT NULL,     age INT,     department VARCHAR(50) ); CREATE TABLE Marks (     mark_id INT PRIMARY KEY,     student_id INT,     subject VARCHAR(50),     marks INT,     FOREIGN KEY (student_id) REFERENCES Students(student_id) ); INSERT INTO Students (student_id, name, age, department) VALUES (1, 'Asha', 19, 'Computer'), (2, 'Ravi', 20, 'Mechanical'), (3, 'P...
Read more