Complex Queries in MySQL

Complex queries are SQL queries used to:

●​ retrieve advanced data

●​ combine multiple tables
●​ perform filtering using subqueries
●​ generate meaningful reports

Main types:

1.​ Nested Queries (Subqueries)

2.​ Joins

1. Nested Queries (Subqueries)

A subquery is:

A query written inside another query.

Inner query executes first.

Syntax
SELECT column_name
FROM table_name
WHERE column_name operator
(
SELECT column_name
FROM table_name
);
Example Tables
Student
sid sname deptid

1 Ravi 10

2 Anu 20

3 Kiran 10
Department
deptid deptname

10 CSE

20 ISE

Example 1: Single Row Subquery

Find students belonging to CSE department.
SELECT sname
FROM Student
WHERE deptid =
(
SELECT deptid
FROM Department
WHERE deptname='CSE'
);

Execution
Inner query:
SELECT deptid
FROM Department
WHERE deptname='CSE';
Result:
10
Outer query becomes:
SELECT sname
FROM Student
WHERE deptid=10;
Output:
sname

Ravi

Kiran
Types of Subqueries
Type Description

Single-row Returns one row

Multi-row Returns multiple rows

Correlated Depends on outer query

Nested Query inside query

Example 2: Multi-row Subquery

Find students from departments 10 and 20.

SELECT sname
FROM Student
WHERE deptid IN
(
SELECT deptid
FROM Department
);

Example 3: Correlated Subquery

Find employees earning above department average.

empid name salary deptid

SELECT name, salary
FROM Employee e1
WHERE salary >
(
SELECT AVG(salary)
FROM Employee e2
WHERE [Link] = [Link]
);
Inner query runs for every outer row.
2. Joins
Joins combine rows from multiple tables using related columns.

Why Joins?
Data is stored in normalized tables.

Joins help:

●​ retrieve related data

●​ avoid redundancy

Types of Joins
Join Purpose

INNER JOIN Matching rows only

LEFT JOIN All left rows + matching right

RIGHT JOIN All right rows + matching left

FULL JOIN All rows from both

SELF JOIN Table joined with itself

CROSS JOIN Cartesian product

INNER JOIN
Returns matching rows only.

Example
Student
sid sname deptid

Department
deptid deptname

Query
SELECT [Link], [Link]
FROM Student s
INNER JOIN Department d
ON [Link] = [Link];

Output
sname deptname

Ravi CSE

Anu ISE
LEFT JOIN
Returns:

●​ all rows from left table

●​ matching rows from right table

Example
SELECT [Link], [Link]
FROM Student s
LEFT JOIN Department d
ON [Link] = [Link];

Even students without departments appear.

RIGHT JOIN
Returns:

●​ all rows from right table

●​ matching left rows

SELF JOIN
Table joins itself.

Example Employee Manager

empid empname managerid
SELECT [Link] AS Employee,
[Link] AS Manager
FROM Employee e
JOIN Employee m
ON [Link] = [Link];
CROSS JOIN
Returns all possible combinations.

If:

●​ table A has 3 rows

●​ table B has 2 rows

Result = 6 rows

Difference Between Subquery and Join

Subquery Join

Query inside query Combines tables

Simpler logic Faster in many cases

May be slower Efficient for large data

Real-Life Example

Banking
Account Table

| accid | customerid |

Customer Table

| customerid | customername |

Join used to fetch:

●​ account details
●​ customer details together
Assertions in DBMS / MySQL
An Assertion is:
A condition or rule enforced on the database that must always remain true.
Assertions are used to maintain:
●​ data integrity
●​ consistency
●​ business rules

Simple Meaning
Assertions check whether certain conditions are satisfied before data is accepted.
If condition fails:
●​ database rejects operation

Example
Suppose company rule says:
Employee salary must always be greater than 5000.
This condition acts as an assertion.

Syntax (SQL Standard)

CREATE ASSERTION assertion_name
CHECK (condition);

Example
CREATE ASSERTION salary_check
CHECK
(
NOT EXISTS
(
SELECT *
FROM Employee
WHERE salary < 5000
)
);
no employee should have salary less than 5000
Important Note About MySQL
⚠ MySQL does NOT directly support:

CREATE ASSERTION

But similar behavior is achieved using:

●​ CHECK constraints
●​ TRIGGERS
●​ FOREIGN KEYS
●​ STORED PROCEDURES

Assertions vs Constraints
Assertions Constraints

Apply to entire database Apply to single table/column

Complex conditions possible Simpler rules

Can involve multiple tables Usually local rules

Example of Assertion-like Rule

Banking Rule
Total loan amount should not exceed bank limit.

This involves multiple rows/tables.

Assertion checks such business conditions.

Assertions Using CHECK Constraint in
MySQL
Example
CREATE TABLE Employee
(
empid INT PRIMARY KEY,
salary INT CHECK(salary >= 5000)
);

Assertions Using Trigger

CREATE TRIGGER salary_validation
BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN
IF [Link] < 5000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary too low';
END IF;
END;

Why Assertions Important?

They help:

●​ enforce business rules

●​ maintain consistency
●​ avoid invalid data
●​ improve integrity
Real-Life Examples
Rule Assertion

Bank balance ≥ 0 Prevent negative balance

Marks between 0–100 Valid score check

Age ≥ 18 Eligibility validation

Difference Between Assertion and Trigger

Assertion Trigger

Checks condition Executes action automatically

Validation rule Event-based operation

Triggers in MySQL
A Trigger is:
A special stored program that automatically executes when a specific event occurs
on a table.
Events include:
●​ INSERT
●​ UPDATE
●​ DELETE

Simple Meaning
Whenever data changes in a table,​
MySQL can automatically perform some action.

Why Triggers Used?

Triggers help:
●​ automate tasks
●​ maintain logs
●​ validate data
●​ enforce business rules
●​ maintain integrity

Types of Triggers
Based on Timing
Type Meaning

BEFORE Trigger Executes before event

AFTER Trigger Executes after event

Based on Event
Event Description

INSERT New row added

UPDATE Existing row modified

DELETE Row removed

Trigger Syntax
CREATE TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN

-- trigger statements

END;

Example Table
Employee
empid name salary

1. BEFORE INSERT Trigger

Prevent salary less than 5000.

CREATE TRIGGER check_salary

BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN

IF [Link] < 5000 THEN

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be >= 5000';
END IF;

END;

Explanation
NEW Keyword
Represents:

●​ new row values being inserted/updated

Example:

[Link]

means:

●​ salary of new row

2. AFTER INSERT Trigger

Maintain log after inserting employee.

Log Table
logmsg

Trigger
CREATE TRIGGER employee_log
AFTER INSERT
ON Employee
FOR EACH ROW
BEGIN

INSERT INTO LogTable

VALUES(CONCAT('Employee inserted: ', [Link]));

END;

3. BEFORE UPDATE Trigger

CREATE TRIGGER salary_update_check
BEFORE UPDATE
ON Employee
FOR EACH ROW
BEGIN

IF [Link] < 5000 THEN

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid salary';
END IF;

END;

4. AFTER DELETE Trigger

Store deleted employee info.

CREATE TRIGGER delete_log

AFTER DELETE
ON Employee
FOR EACH ROW
BEGIN

INSERT INTO DeletedEmployees

VALUES([Link], [Link]);

END;

OLD Keyword
Represents:

●​ existing row values before delete/update

Example:

[Link]

NEW vs OLD
 Keyword Used In

NEW INSERT, UPDATE

OLD DELETE, UPDATE

Real-Life Examples
Use Case Trigger Purpose

Banking Log transactions

E-commerce Update stock automatically

Attendance system Maintain audit logs

Payroll Validate salary

Advantages of Triggers
●​ Automatic execution
●​ Improves integrity
●​ Reduces manual coding
●​ Useful for auditing

Disadvantages
●​ Harder debugging
●​ Can affect performance
●​ Hidden logic may confuse developers

View Existing Triggers

SHOW TRIGGERS;

Delete Trigger
DROP TRIGGER trigger_name;
Views in MySQL
A View is:

A virtual table created from one or more existing tables.

A view does not store data physically.​

It stores only:

●​ the SQL query

Whenever the view is accessed:

●​ data is fetched from original tables.

Why Views Used?

Views help:

●​ simplify complex queries

●​ improve security
●​ hide unnecessary columns
●​ provide abstraction

Simple Example
Employee Table
empid name salary department

Suppose users should see only:

●​ empid
●​ name

Create a view.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example
CREATE VIEW employee_view AS
SELECT empid, name
FROM Employee;

Using the View

SELECT * FROM employee_view;

Output:

empid name

Important Point
View behaves like a table but:

●​ data is actually stored in original table

So it is called:

Virtual Table
View from Multiple Tables
Views can use joins.

Example
Employee Table
empid name deptid

Department Table
deptid deptname

Create View
CREATE VIEW emp_dept_view AS
SELECT [Link], [Link]
FROM Employee e
JOIN Department d
ON [Link] = [Link];

Advantages of Views
1. Security
Hide sensitive columns.
Example:
●​ salary
●​ passwords

2. Simplicity
Complex joins hidden inside view.

3. Reusability
Same query reused multiple times.

4. Data Abstraction
Users see only required data.
Types of Views
Type Description

Simple View Based on one table

Complex View Based on multiple tables

Updating Through Views

Sometimes views allow:

●​ INSERT
●​ UPDATE
●​ DELETE

But not always.

Example Update
UPDATE employee_view
SET name='Ravi Kumar'
WHERE empid=1;

Updates original table too.

View Restrictions
Complex views may not support updates if they contain:

●​ GROUP BY
●​ DISTINCT
●​ Aggregate functions
●​ UNION

Replace Existing View

CREATE OR REPLACE VIEW employee_view AS
SELECT empid, name, department
FROM Employee;
Delete View
DROP VIEW employee_view;

Difference Between Table and View

Table View

Stores data physically Virtual table

Occupies storage Stores query only

Independent Depends on base tables

Transaction Concepts in DBMS / MySQL
A Transaction is:

A logical unit of work consisting of one or more SQL operations executed together.

A transaction ensures:

●​ data consistency
●​ reliability
●​ integrity

Simple Meaning
A transaction is a group of database operations treated as one single task.

Either:

●​ all operations succeed​

OR
●​ all operations fail

Example
Bank Transfer
Transfer ₹1000 from Account A to Account B.

Steps:

1.​ Deduct ₹1000 from A

2.​ Add ₹1000 to B

Both operations together form one transaction.

Transaction Properties
Transactions follow:
ACID Properties
Property Meaning

Atomicity All or nothing

Consistency Valid data maintained

Isolation Transactions independent

Durability Data permanently saved

Transaction States
A transaction moves through different states.

1. Active State
Transaction is executing.

Example:

UPDATE account
SET balance = balance - 1000
WHERE id = 1;

2. Partially Committed State

Last statement executed,​
but changes not permanently saved yet.

3. Committed State
Changes permanently saved.

COMMIT;

4. Failed State
Error occurs during transaction.

Example:

●​ server crash
●​ invalid query

5. Aborted State
Transaction rolled back.

ROLLBACK;

Database restored to previous consistent state.

Transaction Control Commands

Command Purpose

START TRANSACTION Begin transaction

COMMIT Save changes

ROLLBACK Undo changes

SAVEPOINT Create checkpoint

Example Transaction
START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE acc_no = 101;

UPDATE accounts
SET balance = balance + 500
WHERE acc_no = 102;

COMMIT;
If Error Occurs
ROLLBACK;

All changes undone.

Why Transactions Important?

Transactions prevent:

●​ incomplete updates
●​ inconsistent data
●​ data corruption

Real-Life Examples
Application Transaction

Banking Money transfer

Ticket booking Seat reservation

Shopping Order + payment

ATM Withdrawal

Concurrent Transactions
Many users may access database simultaneously.

Transactions help:

●​ maintain correctness
●​ avoid conflicts
Common Transaction Problems
Problem Meaning

Dirty Read Reading uncommitted data

Lost Update One update overwrites another

Non-repeatable Read Data changes during transaction

Phantom Read New rows appear during transaction

Isolation Levels
Used to control concurrency.

Level Description

Read Uncommitted Lowest isolation

Read Committed Reads committed data only

Repeatable Read Same results during transaction

Serializable Highest isolation

Transaction Lifecycle
START TRANSACTION
↓
Execute Queries
↓
COMMIT or ROLLBACK

Difference Between Transaction and

Query
Query Transaction

Single SQL statement Group of statements

Independent Logical unit of work

System Concepts in DBMS – Transaction
Handling
Transaction handling in DBMS refers to:

The process of managing transactions to ensure data consistency, reliability, and

integrity.

The DBMS ensures:

●​ correct execution of transactions

●​ recovery from failures
●​ safe concurrent access

What is a Transaction?
A transaction is:

A logical unit of work containing one or more SQL operations.

Example:

●​ money transfer
●​ ticket booking
●​ online payment

Example
Transfer ₹1000 from Account A to B.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE acc_no = 101;

UPDATE accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT;

Both queries together form one transaction.

Goals of Transaction Handling
DBMS transaction handling ensures:

●​ Atomicity
●​ Consistency
●​ Isolation
●​ Durability

These are called:

ACID Properties: Components of

Transaction Handling
Component Purpose

Transaction Manager Controls transactions

Scheduler Manages execution order

Recovery Manager Handles failures

Concurrency Control Handles simultaneous access

Log Manager Maintains logs

1. Transaction Manager
Responsible for:

●​ starting transactions
●​ committing transactions
●​ rolling back transactions

Example
COMMIT;

Transaction manager permanently saves changes.

2. Scheduler
Controls execution order of concurrent transactions.

Purpose:

●​ avoid conflicts

●​ maintain serializability

Example
Two users updating same bank account simultaneously.

Scheduler ensures:

●​ correct execution order

3. Concurrency Control
Manages simultaneous transactions.

Prevents:

●​ dirty reads
●​ lost updates
●​ inconsistent data

Common Techniques
Locking
DBMS locks data while transaction executes.
Shared Lock
●​ read allowed
Exclusive Lock
●​ write allowed

Example
Transaction T1 locks row
Transaction T2 waits
4. Recovery Manager
Handles system failures.

Restores database to consistent state using:

●​ logs
●​ checkpoints
●​ rollback

Example Failure
Power failure during transaction.

Recovery manager:

●​ undoes incomplete changes

●​ restores committed data

5. Log Manager
Maintains transaction logs.

Log stores:

●​ transaction start
●​ updates
●​ commit/rollback

Example Log
T1 START
T1 UPDATE account
T1 COMMIT

Used for recovery.

Transaction States
Transactions move through states.

State Meaning

Active Executing

Partially Committed Last statement executed

Committed Changes saved

Failed Error occurred

Aborted Rolled back

Transaction Problems
Without proper handling:

Problem Description

Dirty Read Reading uncommitted data

Lost Update One update overwrites another

Non-repeatable Read Data changes during transaction

Phantom Read New rows appear unexpectedly

Isolation Levels
DBMS controls transaction visibility using isolation levels.

Level Description

Read Uncommitted Lowest isolation

Read Committed Reads committed

data

Repeatable Read Stable reads

Serializable Highest isolation

MySQL default:

REPEATABLE READ

Checkpoints
DBMS periodically saves database state.

Purpose:

●​ faster recovery

●​ reduced log processing

ACID Properties in DBMS / MySQL
ACID properties are rules that ensure:

●​ reliable transactions
●​ correct database operations
●​ data consistency and integrity

Used in:

●​ banking systems
●​ ticket booking
●​ e-commerce
●​ payment systems

ACID Full Form

Letter Meaning

A Atomicity

C Consistency

I Isolation

D Durability

What is a Transaction?
A transaction is:

A logical unit of work containing one or more SQL operations.

Example:

●​ transferring money from one account to another

Example Transaction
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE acc_no = 101;

UPDATE accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT;

Both operations must succeed together.

1. Atomicity
Meaning
Either all operations happen or none happen.

Transaction acts as one indivisible unit.

Example
Money transfer:

●​ deduct from Account A

●​ add to Account B

If second step fails:

●​ first step must also be undone

Using ROLLBACK
ROLLBACK;

Real-Life Example
ATM:

●​ money deducted
●​ cash not dispensed
Transaction is rolled back.

2. Consistency
Meaning
Database must remain valid before and after transaction.

Rules and constraints should never break.

Example
Before transfer:

A = 5000

B = 3000

Total = 8000

After transfer:

A = 4000

B = 4000

Total = 8000

Database remains consistent.

Constraints Maintained
●​ Primary Key
●​ Foreign Key
●​ Unique
●​ CHECK constraints

3. Isolation
Meaning
Multiple transactions should not interfere with each other.

Each transaction behaves independently.

Example
Two users booking same movie seat simultaneously.

Without isolation:

●​ both may book same seat

Isolation prevents conflict.

Isolation Levels in MySQL

Level Description

Read Uncommitted Reads uncommitted data

Read Committed Reads committed data only

Repeatable Read Same data during transaction

Serializable Highest isolation

MySQL default:

REPEATABLE READ

4. Durability
Meaning
Once transaction is committed, data is permanently stored.

Even if:

●​ power failure occurs

●​ system crashes

data remains safe.

Example
After online payment:

●​ transaction committed
●​ server crashes

Payment information still exists.

How DBMS Ensures Durability?

Using:

●​ transaction logs
●​ redo logs
●​ disk storage

Why ACID Important?

ACID properties:

●​ prevent data corruption

●​ maintain consistency
●​ ensure reliable multi-user operations

Real-Life Applications
 Application Need for ACID

Banking Safe money transfer

ATM Correct withdrawals

Railway booking Prevent double booking

E-commerce Correct order/payment

Without ACID
Problems may occur:

●​ incomplete transactions
●​ duplicate bookings
●​ inconsistent balances
Recoverability in DBMS
Recoverability means:

The ability of a database system to restore data to a consistent state after a failure.

Failures may occur due to:

●​ power failure
●​ system crash
●​ hardware failure
●​ software error
●​ transaction failure

Goal of Recoverability
Recoverability ensures:

●​ committed transactions remain saved

●​ incomplete transactions are undone
●​ database consistency is maintained

Simple Example
Banking Transaction
Transfer ₹1000 from A to B.

Steps:

1.​ Deduct from A

2.​ Add to B

Suppose system crashes after step 1.

Without recovery:

●​ money deducted from A

●​ not added to B

Database becomes inconsistent.

Recoverability restores correct state.

Recovery Techniques
DBMS uses:

●​ logs
●​ checkpoints
●​ rollback
●​ redo operations

Transaction Log
A log file stores transaction activities.

Example:

T1 START

T1 UPDATE account

T1 COMMIT

Used during recovery.

Types of Recovery Operations

1. UNDO
Reverses incomplete transactions.

Used when:

●​ transaction failed
●​ transaction not committed

Example
Transaction failed before COMMIT

DBMS performs:

UNDO

Restores old values.

2. REDO
Reapplies committed transactions.

Used when:

●​ transaction committed
●​ changes not yet written permanently

Example
Transaction committed

System crashed immediately

DBMS performs:

REDO

Restores committed changes.

Types of Failures
Failure Type Description

Transaction Failure Logical error

System Crash Power/OS failure

Media Failure Disk crash

Communication Failure Network issue

Recoverable Schedule
A schedule is recoverable if:

A transaction commits only after the transaction whose data it read has committed.

Example
Correct Recoverable Schedule
T1 writes X

T1 commits

T2 reads X

T2 commits

Safe because:

●​ T2 reads committed data

Non-Recoverable Schedule
T1 writes X

T2 reads X

T2 commits

T1 aborts

Problem:

●​ T2 used invalid data

Database becomes inconsistent.

Cascading Rollback
Occurs when:

●​ one transaction failure causes others to rollback

Example
T2 depends on T1

T1 fails

T2 must rollback

Cascadeless Schedule
Transactions read only committed data.

Avoids cascading rollback.

Strict Schedule
Even stronger rule:

●​ no transaction can read/write until previous transaction commits

Improves recoverability.

Checkpoints
DBMS periodically saves stable database state.

Benefits:

●​ faster recovery
●​ less log scanning

Recovery Manager
DBMS component responsible for:

●​ restoring database
●​ undo/redo operations
●​ crash recovery
Serializability in DBMS
Serializability is:

A property that ensures concurrent transactions execute correctly without affecting

database consistency.

It guarantees that:

●​ concurrent execution produces the same result as serial execution.

What is Serial Execution?

Transactions execute:

one after another.

Example:

T1 → T2

or

T2 → T1

No overlap occurs.

What is Concurrent Execution?

Multiple transactions execute simultaneously.

Example:

T1 and T2 execute together

This improves:

●​ performance
●​ CPU utilization
●​ multi-user support

But may cause inconsistency.

Why Serializability Needed?
Without serializability:

●​ lost updates occur

●​ dirty reads happen
●​ inconsistent data appears

Serializability ensures correctness.

Example
Initial balance:

Account Balance

A 1000

Transaction T1
Withdraw ₹200

UPDATE account
SET balance = balance - 200
WHERE id = 1;

Transaction T2
Deposit ₹500

UPDATE account
SET balance = balance + 500
WHERE id = 1;

Serial Execution
T1 then T2
1000 - 200 = 800
800 + 500 = 1300
Final balance = 1300
Concurrent Problem
If both transactions read balance simultaneously:

T1 reads 1000
T2 reads 1000

Possible incorrect results:

●​ 800​
or
●​ 1500

instead of:

1300

This is called:

Lost Update Problem

Serializability Solves This

Concurrent schedule must behave like a serial schedule.

Serializable Schedule
A schedule is serializable if:

Its final result is equivalent to some serial execution.

Types of Serializability
Type Meaning

Conflict Serializability Based on conflicting operations

View Serializability Based on final database view

1. Conflict Serializability
Most common type.

Two operations conflict if:

●​ they access same data item

●​ at least one operation is WRITE

Conflicting Operations
Operation 1 Operation 2 Conflict?

Read Read No

Read Write Yes

Write Read Yes

Write Write Yes

Example
R1(X)
W1(X)
R2(X)
W2(X)

Conflict occurs on X.

Precedence Graph
Used to check conflict serializability.

Steps:

1.​ Create node for each transaction

2.​ Draw edge for conflicts
3.​ Check cycle
Rule
●​ No cycle → Serializable
●​ Cycle exists → Not serializable

2. View Serializability
Less strict than conflict serializability.
Checks:
●​ same final result
●​ same read/write relationships

Advantages of Serializability
●​ maintains consistency
●​ prevents anomalies
●​ ensures correct concurrent execution

Problems Prevented
Problem Meaning

Dirty Read Reading uncommitted data

Lost Update Updates overwritten

Non-repeatable Read Data changes during transaction

Phantom Read Extra rows appear

How DBMS Achieves Serializability?

Using:
●​ locks
●​ timestamps
●​ concurrency control protocols
●​ isolation levels

Serializable Isolation Level

Highest isolation [Link] behave serially.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL Transaction Support in MySQL
SQL provides transaction support using commands like:

●​ COMMIT
●​ ROLLBACK
●​ SAVEPOINT

These commands help:

●​ control transactions
●​ maintain consistency
●​ recover from errors

What is a Transaction?
A transaction is:

A logical unit of work consisting of multiple SQL operations executed together.

Example:

●​ bank money transfer

●​ ticket booking
●​ online payment

Transaction Flow
START TRANSACTION
↓
Execute SQL Queries
↓
COMMIT / ROLLBACK

Main Transaction Commands

Command Purpose

START TRANSACTION Begins transaction

COMMIT Permanently save changes

ROLLBACK Undo changes

 SAVEPOINT Create checkpoint

ROLLBACK TO SAVEPOINT Partial rollback

1. START TRANSACTION
Begins a transaction.

START TRANSACTION;

Example
START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

Changes are temporary until COMMIT.

2. COMMIT
Permanently saves transaction changes.

COMMIT;

Example
START TRANSACTION;

UPDATE employee
SET salary = salary + 2000
WHERE empid = 1;

COMMIT;

After commit:

●​ changes become permanent

●​ cannot rollback
3. ROLLBACK
Undoes transaction changes.

ROLLBACK;

Example
START TRANSACTION;

DELETE FROM student

WHERE sid = 10;

ROLLBACK;

Deleted row restored.

Why ROLLBACK Used?

Used when:

●​ error occurs
●​ transaction fails
●​ system crashes

4. SAVEPOINT
Creates a checkpoint inside transaction.

SAVEPOINT s1;

Allows partial rollback.

Example
START TRANSACTION;

INSERT INTO student VALUES(1,'Ravi');

SAVEPOINT s1;

INSERT INTO student VALUES(2,'Anu');

5. ROLLBACK TO SAVEPOINT
Rollback only till savepoint.

ROLLBACK TO s1;

Removes changes after savepoint only.

Complete Example
START TRANSACTION;

INSERT INTO student VALUES(1,'Ravi');

SAVEPOINT s1;

INSERT INTO student VALUES(2,'Anu');

ROLLBACK TO s1;

COMMIT;

Final table:

ID Name

1 Ravi

Anu record removed.

AUTOCOMMIT in MySQL
By default:

AUTOCOMMIT = ON

Each query commits automatically.

Disable Autocommit
SET AUTOCOMMIT = 0;
Check Autocommit Status
SELECT @@autocommit;

Transaction Support Storage Engines

In MySQL:

●​ InnoDB supports transactions

●​ MyISAM does not fully support transactions

Why Transaction Support Important?

Ensures:

●​ consistency
●​ reliability
●​ recovery from failures
●​ safe concurrent operations