1.
TRANSACTION
Definition
A transaction is a sequence of one or more SQL operations executed as a single logical unit of
work.
Example
INSERT INTO orders VALUES (101, 'Laptop');
UPDATE inventory SET stock = stock - 1 WHERE product = 'Laptop';
Both statements must succeed or both must fail.
ACID Properties
1. Atomicity
Transaction executes completely or not at all
Ensured by rollback segments (UNDO)
2. Consistency
Database moves from one valid state to another
Enforced by constraints, triggers, rules
3. Isolation
Transactions do not interfere with each other
Managed by concurrency control
4. Durability
Once committed, data is permanent
Ensured by REDO logs
2. COMMIT
Definition
COMMIT permanently saves all changes made by a transaction.
Internal Working in Oracle
When COMMIT is issued:
1. Changes are written to redo log buffer
2. LGWR writes redo to disk
3. SCN (System Change Number) is updated
4. Locks are released
5. Changes become visible to others
Key Characteristics
Ends the current transaction
Cannot be undone
Makes data visible to other sessions
Example
COMMIT;
3. ROLLBACK
Definition
ROLLBACK reverses all changes made by the current transaction.
Internal Working
Oracle uses UNDO data
Restores previous data blocks
Releases locks
Rollback to Savepoint
SAVEPOINT s1;
ROLLBACK TO s1;
When Rollback Occurs Automatically
System crash
Session termination
Unhandled errors
4. SAVEPOINT
Definition
A SAVEPOINT marks a point within a transaction to which you can rollback.
Advantage
Partial rollback
Does not affect earlier work
5. DISTRIBUTED DATABASE
Definition
A distributed database is a database in which data is stored at multiple physical locations, but
appears as a single logical database.
Features
Location transparency
Network communication
Data fragmentation or replication
Types
1. Homogeneous
Same DBMS at all sites (Oracle–Oracle)
2. Heterogeneous
Different DBMS (Oracle–MySQL)
Advantages
High availability
Scalability
Local autonomy
Disadvantages
Complexity
Network dependency
Distributed transaction overhead
6. DATABASE LINK
Definition
A database link is a logical connection from one Oracle database to another.
Syntax
CREATE DATABASE LINK sales_db
CONNECT TO scott IDENTIFIED BY tiger
USING 'ORCL';
Types
Private
Public
Fixed user
Connected user
7. DISTRIBUTED TRANSACTION
Definition
A distributed transaction is a transaction that accesses data in more than one database.
Example
UPDATE emp@db1 SET salary = salary + 1000;
UPDATE emp@db2 SET salary = salary - 1000;
COMMIT;
Problems
Partial failure
Network crashes
Inconsistent data
Solution
➡️Two-Phase Commit Protocol
8. CONCURRENCY CONTROL
Definition
Concurrency control ensures that multiple transactions can execute simultaneously without
violating data integrity.
Problems Solved
1. Lost Update
Two transactions overwrite each other’s changes.
2. Dirty Read
Reading uncommitted data.
3. Non-Repeatable Read
Same query returns different values.
4. Phantom Read
New rows appear in repeated queries.
Oracle Concurrency Control
MVCC (Multi-Version Concurrency Control)
Oracle stores old data in UNDO
Readers see consistent snapshots
No read locks required
Isolation Levels in Oracle
READ COMMITTED (default)
SERIALIZABLE
Locks in Oracle
Row-Level Lock
Locked when row is modified
Prevents other updates
Table-Level Lock
Protects table structure
9. ORACLE NET SERVICES
Definition
Oracle Net Services allows communication between Oracle clients and servers over a
network.
Components
Listener
Listens for client requests
Runs on server
Service Name
Logical database service
TNS
Connection descriptor
Example TNS Entry
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=orcl))
)
10. VIEW
Definition
A view is a virtual table created using a SQL query.
Types of Views
Simple View
Single table
DML allowed
Complex View
Join, group by
Limited DML
Advantages
Security
Simplicity
Logical abstraction
Read-Only View
CREATE VIEW v1 AS SELECT * FROM emp WITH READ ONLY;
11. MATERIALIZED VIEW
Definition
A materialized view physically stores query results.
Usage
Performance optimization
Data warehousing
12. TWO-PHASE COMMIT (2PC)
Definition
A protocol to ensure atomicity of distributed transactions.
Participants
Coordinator (local database)
Participants (remote databases)
Phase 1: Prepare Phase
1. Coordinator sends PREPARE
2. Participants write redo
3. Respond YES or NO
Phase 2: Commit Phase
If all YES → COMMIT
If any NO → ROLLBACK
Oracle Recovery
Uses RECO background process
Automatically resolves in-doubt transactions
13. IN-DOUBT TRANSACTION
Definition
A distributed transaction whose final state is unknown due to failure.
Resolution
Automatic recovery
DBA intervention if needed
14. RELATIONSHIP SUMMARY
Concept Purpose
Transaction Unit of work
Commit Save changes
Rollback Undo changes
Distributed DB Multi-location data
DB Link Connect databases
Distributed Transaction Multi-DB transaction
Concurrency Control Multi-user consistency
Oracle Net Communication
Views Logical abstraction
2PC Distributed atomicity