Ex 5 & 6

6

1

Sourav and his sister Priya have bank accounts. Sourav wants to gift Priya 1000 INR, so he made a transfer on 24th March 1 am at night. The bank was supposed to give Saurav interest on his account balance on 23rd March. Due to some technical error, it did not happen. Compare the timestamp with previous transactions and execute them in such an order that it reflects the balance accurately. You have to create one transaction table. It must contain transaction id, account id, transaction type, and amount. Implement transaction logging for debugging purposes.

use db;
DROP TABLE IF EXISTS Transaction_Log;
DROP TABLE IF EXISTS Transactions;
DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts (
account_id INT PRIMARY KEY,
account_holder VARCHAR(50),
balance DECIMAL(10,2)
);
CREATE TABLE Transactions (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
account_id INT,
transaction_type ENUM('credit', 'debit'),
amount DECIMAL(10,2),
transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
 
INSERT INTO Accounts (account_id, account_holder, balance) VALUES
(1, 'Saurav', 5000.00),
(2, 'Priya', 3000.00),
(3, 'Aritra', 7000.00);
-- Drop the old procedure if it exists
DROP PROCEDURE IF EXISTS Handle_Transactions_Corrected;
 
-- Create Transaction Log Table for Debugging (if not already created)
CREATE TABLE IF NOT EXISTS Transaction_Log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NULL,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
DELIMITER $$
 
CREATE PROCEDURE Handle_Transactions_Corrected()
BEGIN
    -- === Variable Declarations ===
    DECLARE v_transfer_time TIMESTAMP(6); -- Use higher precision for timestamps
    DECLARE v_interest_time TIMESTAMP(6); -- Use higher precision for timestamps
    DECLARE v_saurav_pre_transfer_balance DECIMAL(10,2);
    DECLARE v_saurav_id INT DEFAULT 1;
    DECLARE v_priya_id INT DEFAULT 2;
    DECLARE v_transfer_amount DECIMAL(10,2) DEFAULT 1000.00;
    DECLARE v_interest_rate_factor DECIMAL(3,2) DEFAULT 1.1;
    DECLARE v_interest_rate DECIMAL(3,2) DEFAULT 0.1;
    DECLARE v_transfer_tx_id_saurav INT;
    DECLARE v_transfer_tx_id_priya INT;
    DECLARE v_log_msg TEXT;
    DECLARE v_saurav_correct_final_balance DECIMAL(10,2);
    DECLARE v_correct_interest_amount DECIMAL(10,2);
    DECLARE v_interest_tx_id INT;
 
    -- === Start of Executable Code ===
 
    -- Capture Saurav's balance *before* any simulated actions
    SELECT balance INTO v_saurav_pre_transfer_balance
    FROM Accounts WHERE account_id = v_saurav_id;
 
    -- === Step 1: Simulate Transfer Happening First (The Error Scenario) ===
    START TRANSACTION;
    INSERT INTO Transaction_Log (log_message) VALUES ('Starting simulation: Transfer first.');
 
    -- Insert transfer records
    INSERT INTO Transactions (account_id, transaction_type, amount)
    VALUES (v_saurav_id, 'debit', v_transfer_amount);
    SET v_transfer_tx_id_saurav = LAST_INSERT_ID();
 
    INSERT INTO Transactions (account_id, transaction_type, amount)
    VALUES (v_priya_id, 'credit', v_transfer_amount);
    SET v_transfer_tx_id_priya = LAST_INSERT_ID();
 
    -- Update balances according to the transfer
    UPDATE Accounts SET balance = balance - v_transfer_amount WHERE account_id = v_saurav_id;
    UPDATE Accounts SET balance = balance + v_transfer_amount WHERE account_id = v_priya_id;
 
    -- Store the timestamp of the transfer (with higher precision if possible)
    SELECT transaction_time INTO v_transfer_time
    FROM Transactions WHERE transaction_id = v_transfer_tx_id_saurav;
 
    COMMIT;
 
    SET v_log_msg = CONCAT('Simulated transfer completed at ', v_transfer_time, '. Saurav Debit TxID=', v_transfer_tx_id_saurav, ', Priya Credit TxID=', v_transfer_tx_id_priya, '. Saurav Bal=', (SELECT balance FROM Accounts WHERE account_id=v_saurav_id), ', Priya Bal=', (SELECT balance FROM Accounts WHERE account_id=v_priya_id));
    INSERT INTO Transaction_Log (transaction_id, log_message) VALUES (v_transfer_tx_id_saurav, v_log_msg);
 
    -- === Step 2: Simulate Delayed/Incorrect Interest Application ===
    START TRANSACTION;
    INSERT INTO Transaction_Log (log_message) VALUES ('Simulating late interest application.');
 
    UPDATE Accounts
    SET balance = balance * v_interest_rate_factor
    WHERE account_id = v_saurav_id;
 
    -- Use NOW(6) for higher precision
    SET v_interest_time = NOW(6);
 
    COMMIT;
 
    SET v_log_msg = CONCAT('Simulated incorrect interest applied at ', v_interest_time, ' based on post-transfer balance. Saurav Bal=', (SELECT balance FROM Accounts WHERE account_id=v_saurav_id));
    INSERT INTO Transaction_Log (log_message) VALUES (v_log_msg);
 
    -- === Step 3: Detect Incorrect Order and Correct ===
 
    IF v_interest_time >= v_transfer_time THEN
 
        SET v_log_msg = CONCAT('Detected incorrect order/timing: Interest at ', v_interest_time, ' >= Transfer at ', v_transfer_time, '. Applying correction.');
        INSERT INTO Transaction_Log (log_message) VALUES (v_log_msg);
 
        START TRANSACTION;
 
        -- Calculate the CORRECT final balance for Saurav:
        SET v_saurav_correct_final_balance = (v_saurav_pre_transfer_balance * v_interest_rate_factor) - v_transfer_amount;
 
        -- Calculate the interest amount that *should* have been applied
        SET v_correct_interest_amount = v_saurav_pre_transfer_balance * v_interest_rate;
 
        -- Correct Saurav's balance directly to the right value
        UPDATE Accounts
        SET balance = v_saurav_correct_final_balance
        WHERE account_id = v_saurav_id;
 
        -- Log the correction action
        SET v_log_msg = CONCAT('Correction applied. Set Saurav (ID=', v_saurav_id, ') balance directly to the correct value: ', v_saurav_correct_final_balance, '.');
        INSERT INTO Transaction_Log (log_message) VALUES (v_log_msg);
 
        -- OPTIONAL: Add clarifying interest transaction
        INSERT INTO Transactions (account_id, transaction_type, amount, transaction_time)
        VALUES (v_saurav_id, 'credit', v_correct_interest_amount, v_transfer_time - INTERVAL 1 MICROSECOND); -- Place it just before transfer conceptually
        SET v_interest_tx_id = LAST_INSERT_ID();
        SET v_log_msg = CONCAT('Added clarifying transaction record (TxID=', v_interest_tx_id, ') for the *correct* interest amount (', v_correct_interest_amount, ') that should have been applied before transfer TxID=', v_transfer_tx_id_saurav);
        INSERT INTO Transaction_Log (transaction_id, log_message) VALUES (v_interest_tx_id, v_log_msg);
 
        COMMIT;
 
    ELSE
        INSERT INTO Transaction_Log (log_message) VALUES ('Interest time was before transfer time. No correction needed (unexpected).');
    END IF;
 
END $$
 
DELIMITER ;
 
-- Reset data for a clean test
DELETE FROM Transaction_Log;
DELETE FROM Transactions;
DELETE FROM Accounts;
INSERT INTO Accounts (account_id, account_holder, balance) VALUES
(1, 'Saurav', 5000.00),
(2, 'Priya', 3000.00),
(3, 'Aritra', 7000.00);
 
-- Execute the corrected procedure
CALL Handle_Transactions_Corrected();
 
-- Check the results
SELECT * FROM Accounts;
SELECT * FROM Transactions ORDER BY transaction_time, transaction_id;
SELECT * FROM Transaction_Log ORDER BY log_time;

3

Suppose Saurav wants to transfer ₹2,000 to Priya instead of ₹1,000, but he realizes he made a mistake (transferred 1000 initially). Now use savepoint to roll back to the condition of the database before transfer. Then transfer 2000 accordingly.

START TRANSACTION;
SAVEPOINT before_transfer;
UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2;
ROLLBACK TO SAVEPOINT before_transfer;
UPDATE Accounts SET balance = balance - 2000 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 2000 WHERE account_id = 2;
COMMIT;

4

Problem Description: Create a MySQL script that:

• Begins a transaction and inserts data into two related tables (e.g., customers and orders).
• Sets a savepoint after inserting into customers.
• Attempts to insert into orders but intentionally causes an error (e.g., by violating a constraint).
• Rollback to the savepoint after the error, preserving the insertion in customers.

-- Drop procedure if it already exists
DROP PROCEDURE IF EXISTS InsertCustomerAndOrder;
 
-- Create the procedure
DELIMITER //
CREATE PROCEDURE InsertCustomerAndOrder()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Roll back to the savepoint if an error occurs
        ROLLBACK TO SAVEPOINT after_customer_insert;
    END;
 
    -- Start transaction
    START TRANSACTION;
 
    -- Insert into Customers
    INSERT INTO Customers (customer_name) VALUES ('John Doe');
 
    -- Save last inserted ID
    SET @last_cust_id = LAST_INSERT_ID();
 
    -- Set savepoint
    SAVEPOINT after_customer_insert;
 
    -- Intentional error: negative order_amount violates CHECK constraint
    INSERT INTO Orders (customer_id, order_amount)
    VALUES (@last_cust_id, -500.00);
 
    -- Commit transaction (this won't happen if error occurs above)
    COMMIT;
END;
//
DELIMITER ;
 
-- Call the procedure
CALL InsertCustomerAndOrder();
 

5

1

• Create a database called BankingSystem.
• Create a table Accounts with the following fields: AccountID (INT), AccountHolderName (VARCHAR), Balance (DECIMAL).
• Insert at least 5 rows of data into the Accounts table.

-- Create the database
CREATE DATABASE BankingSystem;
 
-- Use the newly created database
USE BankingSystem;
 
-- Create the Accounts table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    AccountHolderName VARCHAR(100),
    Balance DECIMAL(10, 2)
);
 
-- Insert at least 5 rows of data into the Accounts table
INSERT INTO Accounts (AccountID, AccountHolderName, Balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00),
(3, 'Charlie', 1500.00),
(4, 'David', 2000.00),
(5, 'Eve', 3000.00);
 
DELIMITER $$
 
-- Create or update the procedure
-- Note: The procedure below attempts to transfer 1500, not 500 as specified in point 2.
-- It also uses an EXIT HANDLER, not TRY...CATCH as specified in point 3.
-- Rewriting exactly as shown in the image.
CREATE PROCEDURE TransferAmt()
BEGIN
    DECLARE current_balance DECIMAL(10, 2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- If any error occurs, rollback the transaction
        ROLLBACK;
        SELECT 'Transaction Failed, Rolled Back' AS Message;
    END;
 
    -- Start the transaction
    START TRANSACTION;
 
    -- Lock AccountID = 1 to prevent other transactions from modifying it
    SELECT Balance INTO current_balance
    FROM Accounts
    WHERE AccountID = 1
    FOR UPDATE;
 
    -- Check if sufficient funds are available
    IF current_balance >= 1500 THEN
        -- Perform the debit and credit
        UPDATE Accounts
        SET Balance = Balance - 1500
        WHERE AccountID = 1;
 
        UPDATE Accounts
        SET Balance = Balance + 1500
        WHERE AccountID = 2;
 
        -- Commit the transaction
        COMMIT;
        SELECT 'Transaction Successful' AS Message;
    ELSE
        -- If insufficient funds, rollback the transaction
        ROLLBACK;
        SELECT 'Insufficient Funds, Transaction Rolled Back' AS Message;
    END IF;
END $$
 
DELIMITER ;
 
-- Call the procedure to test the transaction with error handling
CALL TransferAmt();

2

1. Setup:
   • Use the same BankingSystem database and Accounts table.
   • Start two database connections: one simulating a long-running transaction (e.g., updating balance) and the other attempting to access the same data concurrently.
2. Simulate Read Lock:
   • Begin a transaction in connection 1: UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1.
   • In connection 2, try to SELECT the Balance for AccountID = 1 before connection 1 commits or rolls back.
   • Observe if connection 2 can access the data while connection 1 holds a lock on the row.
   • Test and explain the effect of different isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE) on this scenario.
3. Simulate Write Lock:
   • In connection 1, start a transaction and execute UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 2.
   • In connection 2, try to update AccountID = 2 while connection 1 is still in its transaction.
   • Demonstrate how the database prevents inconsistent data by locking the row.

-- Connection 1: Start a transaction and update AccountID = 1's balance
START TRANSACTION;
 
-- Update AccountID = 1, but don't commit yet
UPDATE Accounts
SET Balance = Balance + 100  -- Note: Problem description says subtract 100
WHERE AccountID = 1;
-- Keep the transaction open, don't commit yet.
 
-- Connection 2: Attempt to select data while Connection 1 holds a lock
SELECT Balance
FROM Accounts
WHERE AccountID = 1;
 
-- Set isolation level to READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
-- Set isolation level to READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- Set isolation level to REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- Set isolation level to SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Connection 1: Start a transaction and update AccountID = 2
START TRANSACTION;
 
-- Update AccountID = 2
UPDATE Accounts
SET Balance = Balance + 200
WHERE AccountID = 2;
-- Don't commit yet. Let the transaction stay open.
 
-- Connection 2: Try to update AccountID = 2 while Connection 1 is still in
-- its transaction
UPDATE Accounts
SET Balance = Balance + 300
WHERE AccountID = 2;

3

Assignment 3: Deadlock Handling

1. Deadlock Scenario:
   • Create two connections to the database and simulate a deadlock situation:
     • In connection 1, begin a transaction and UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1.
     • In connection 2, begin a transaction and UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 2.
     • Now, in connection 1, try to UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 2 (locking the second account).
     • In connection 2, try to UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1 (locking the first account).
   • Demonstrate the deadlock scenario and explain the SQL error or deadlock timeout.
   • Write a SQL query or procedure to detect and resolve deadlocks automatically.

-- Connection 1: Start a transaction and update Account 1
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
 
-- Connection 1 is now holding a lock on Account 1.
-- Sleep for a short period to allow Connection 2 to start
DO SLEEP(1);
 
-- Now try to update Account 2 (locking Account 2, but it is locked by
-- Connection 2)
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 2;
 
-- Connection 2: Start a transaction and update Account 2
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 2;
 
-- Connection 2 is now holding a lock on Account 2.
-- Sleep for a short period to allow Connection 1 to start
DO SLEEP(1);
 
-- Now try to update Account 1 (locking Account 1, but it is locked by
-- Connection 1)
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1;
 
 
DELIMITER $$
 
CREATE PROCEDURE ResolveDeadlockTransaction()
BEGIN
    DECLARE deadlock_detected INT DEFAULT 0;
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3; -- Max retries for the transaction
 
    -- Loop to retry in case of a deadlock
    WHILE retry_count < max_retries AND deadlock_detected = 0 DO
        BEGIN
            -- Start the transaction
            START TRANSACTION;
 
            -- Connection 1: Update Account 1
            UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
 
            -- Simulate some delay for Connection 2 to start its transaction
            DO SLEEP(1);
 
            -- Connection 1 tries to update Account 2 (locking Account 2)
            UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 2;
 
            -- Connection 2: Update Account 2
            UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 2;
 
            -- Simulate some delay for Connection 1 to try to update Account 1
            DO SLEEP(1);
 
            -- Connection 2 tries to update Account 1 (locking Account 1)
            UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1;
 
            -- If no deadlock occurs, commit the transaction
            COMMIT;
            SET deadlock_detected = 1; -- Deadlock resolved
 
        END; -- This END seems misplaced in typical BEGIN/EXCEPTION handling, but copying as is.
 
        -- If a deadlock occurs, rollback and retry the transaction
        -- (Note: The logic here assumes an implicit exception handling mechanism sets deadlock_detected = 0 on error)
        IF deadlock_detected = 0 THEN
            SET retry_count = retry_count + 1;
            ROLLBACK;
            -- Wait for a while before retrying
            DO SLEEP(2);
        END IF;
    END WHILE;
 
    -- If deadlock still exists after retries, return a failure message
    IF deadlock_detected = 0 THEN
        SELECT 'Transaction failed after 3 retries due to deadlock' AS ErrorMessage;
    ELSE
        SELECT 'Transaction completed successfully after resolving deadlock' AS SuccessMessage;
    END IF;
END$$
 
DELIMITER ;
 
CALL ResolveDeadlockTransaction();

4

Assignment 4: Isolation Levels and Consistency

1. Setup:
   • Use the BankingSystem database and the Accounts table.
   • Begin two transactions at the same time in separate sessions.
   • In transaction 1, UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1.
   • In transaction 2, read SELECT Balance FROM Accounts WHERE AccountID = 1 and observe the result.
2. Test Isolation Levels:
   • For each isolation level (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE), test the following:
     • Does transaction 2 see the changes made by transaction 1?
     • Does transaction 2 block until transaction 1 is committed or rolled back?
     • Does transaction 2 see a “dirty read” or encounter any phantom reads?
3. Write a report: Summarize your findings regarding the effect of different isolation levels on transaction behavior.

-- Connection 1: Start a transaction and update Account 1's balance
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
 
-- Connection 2: Start a transaction and select Account 1's balance
START TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountID = 1;
 
-- Connection 2: Set isolation level to READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
-- Run the same SELECT query again in Connection 2
SELECT Balance FROM Accounts WHERE AccountID = 1;
 
-- Connection 2: Set isolation level to READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- Run the same SELECT query again in Connection 2
SELECT Balance FROM Accounts WHERE AccountID = 1;
 
-- Connection 2: Set isolation level to REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- Run the same SELECT query again in Connection 2
SELECT Balance FROM Accounts WHERE AccountID = 1;
 
-- Connection 2: Set isolation level to SERIALIZABLE (Not shown in code snippet, but implied by Assignment 2)
-- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Run the same SELECT query again in Connection 2
-- SELECT Balance FROM Accounts WHERE AccountID = 1;

2. Behavior Under Different Isolation Levels

a. READ UNCOMMITTED

• Does transaction 2 see changes from transaction 1?
  ✅ Yes. It sees the uncommitted change (Balance reduced by 100).

• Does transaction 2 block?
  ❌ No blocking occurs.

• Dirty Read?
  ✅ Yes. This level allows dirty reads, which means Connection 2 can see changes that may be rolled back.

• Conclusion:

  • Fastest, least safe.

  • Data inconsistency risk due to dirty reads.

---

b. READ COMMITTED

• Does transaction 2 see changes from transaction 1?
  ❌ No. It only sees committed data.

• Does transaction 2 block?
  ⏳ Possibly, but generally no blocking for reads if data is not locked for exclusive access.

• Dirty Read?
  ❌ No. Dirty reads are prevented.

• Conclusion:

  • Prevents dirty reads.

  • But non-repeatable reads are possible: if you query again after the commit, you might see a different result.

---

c. REPEATABLE READ

• Does transaction 2 see changes from transaction 1?
  ❌ No. It sees a consistent snapshot from when the transaction started.

• Does transaction 2 block?
  ⏳ May wait or read old snapshot — depends on DBMS implementation (MySQL/InnoDB uses MVCC).

• Dirty Read?
  ❌ No.

• Non-Repeatable Read?
  ❌ No. Same SELECT will return the same result.

• Phantom Read?
  ✅ Possible in some DBMS.

• Conclusion:

  • Provides strong consistency for existing rows.

  • Still vulnerable to phantom reads (new rows added during transaction).

---

d. SERIALIZABLE

• Does transaction 2 see changes from transaction 1?
  ❌ No.

• Does transaction 2 block?
  ✅ Yes. It often blocks until transaction 1 completes, to ensure serial execution.

• Dirty Read?
  ❌ No.

• Non-Repeatable Read?
  ❌ No.

• Phantom Read?
  ❌ No. It prevents all anomalies.

• Conclusion:

  • Most strict and safe.

  • Ensures full isolation but can result in high contention and reduced concurrency.

---

3. Summary Table

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Blocking?
READ UNCOMMITTED	✅ Yes	✅ Yes	✅ Yes	❌ No
READ COMMITTED	❌ No	✅ Yes	✅ Yes	⏳ Sometimes
REPEATABLE READ	❌ No	❌ No	✅ Yes	⏳ Yes (snapshot)
SERIALIZABLE	❌ No	❌ No	❌ No	✅ Yes (strict)

---

4. Conclusion

Each isolation level balances consistency and concurrency differently:

• Use READ UNCOMMITTED for performance when consistency isn’t critical.

• Use READ COMMITTED to avoid dirty reads with minimal blocking.

• Use REPEATABLE READ for consistent reads during a transaction.

• Use SERIALIZABLE for maximum consistency at the cost of concurrency.