Forem: LOKHITHA S

Transactions, Deadlocks & Log Based Recovery

LOKHITHA S — Thu, 09 Oct 2025 01:31:13 +0000

Managing database transactions safely is crucial to ensure data integrity and prevent issues like partial updates or deadlocks. In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.

First, let’s create a sample table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

*Transaction – Atomicity & Rollback
*
Suppose we want to transfer 500 from Alice to Bob. Using a transaction ensures that either both operations succeed or none.

START TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

ROLLBACK;
UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

Deadlock Simulation

Deadlocks occur when two transactions block each other waiting for resources.

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;
UPDATE Accounts SET balance = balance - 100 WHERE name='Alice';RESULT
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Log-Based Recovery

Modern DBMS (MySQL/PostgreSQL) automatically maintain transaction logs. These logs help undo changes if a transaction fails.

UPDATE Accounts SET balance = balance - 300 WHERE name='Alice';Log-based recovery ensures that undo operations are possible. Even if the database crashes, the system can restore a consistent state.

Summary

Transactions guarantee atomicity; either all operations succeed or none.

Deadlocks occur when transactions block each other; they must be handled with care.

Log-based recovery ensures durability and recoverability

Normalization

LOKHITHA S — Thu, 09 Oct 2025 01:28:00 +0000

Database normalization is the process of organizing data to reduce redundancy and improve data integrity. In this tutorial, we’ll go step-by-step from Unnormalized Table → 1NF → 2NF → 3NF, and implement it using **MySQL.

We’ll also write a JOIN query to display students along with their courses and instructors.

Insertion Anomaly: Cannot add a new course without assigning it to a student.

Update Anomaly: If an instructor’s phone number changes, multiple rows must be updated.
Deletion Anomaly: Removing a student could delete information about the course and instructor. 1.First Normal Form (1NF)
Rule: Each column should have atomic values.

Our table already satisfies 1NF.

Cursor + Trigger

LOKHITHA S — Thu, 09 Oct 2025 01:23:01 +0000

Cursor + Trigger — SQL Practice
In this post, we’ll explore two powerful SQL concepts — Cursors and Triggers — with complete examples that you can run directly in Oracle LiveSQL.

CURSOR — Process Cursor with Condition
Problem Statement:

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.
CREATE TABLE Employee (
EmpID NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
Salary NUMBER(10,2)
);
INSERT INTO Employee VALUES (1, 'Ankit', 45000);
INSERT INTO Employee VALUES (2, 'Zeena', 52000);
INSERT INTO Employee VALUES (3, 'Mukesh', 60000);
INSERT INTO Employee VALUES (4, 'Vishal', 48000);
COMMIT;
SET SERVEROUTPUT ON;

DECLARE
v_EmpName Employee.EmpName%TYPE;
v_Salary Employee.Salary%TYPE;
CURSOR emp_cursor IS
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_EmpName, v_Salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_EmpName || ' | Salary: ' || v_Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

*TRIGGER *— AFTER INSERT Trigger (Student Table)
Problem Statement:

Whenever a new student is added to the Students table, automatically insert a log entry into the Student_Audit table to keep track of the registration.
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(100),
Course VARCHAR2(100)
);
CREATE TABLE Student_Audit (
AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID NUMBER,
StudentName VARCHAR2(100),
ActionTaken VARCHAR2(100),
ActionDate DATE
);
CREATE OR REPLACE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, ActionTaken, ActionDate)
VALUES (:NEW.StudentID, :NEW.StudentName, 'New Student Registered', SYSDATE);
END;
INSERT INTO Students (StudentID, StudentName, Course)
VALUES (1, 'hareesh', 'Computer Science');
COMMIT;
SELECT * FROM Student_Audit;

SQL query

LOKHITHA S — Mon, 25 Aug 2025 18:03:19 +0000