DBMS
DBMS Part-1
- DBMS Introduction
- DBMS Architecture
- Database Approach vs Traditional File System
- Advantages of DBMS
- Data Models in DBMS
- Schemas in DBMS
- Instances in DBMS
- Data Independence in DBMS
- Database Languages in DBMS
- Interfaces in DBMS
- Structure of DBMS
- Functions of DBA and Designer
- Entities and Attributes in DBMS
- ER Diagram in DBMS
- Generalization, Specialization and Aggregation in DBMS
- Converting ER Diagram to Tables in DBMS
- Difference between Object Oriented, Network and Relational Data Models
DBMS Part-2
- Relational Data Model in DBMS
- Keys in DBMS
- SQL Introduction
- DDL(Data Definition Language)
- DML(Data Manipulation Language)
- Integrity Constraints in DBMS
- Complex SQL Queries
- Joins in DBMS
- Indexing in DBMS
- Triggers in DBMS
- Assertions in DBMS
- Relational Algebra in DBMS
- Tuple Relational Calculus in DBMS
- Domain Relational Calculus in DBMS
DBMS Part-3
- Introduction to Normalization in DBMS
- Normal Forms in DBMS
- Functional Dependency in DBMS
- Decomposition in DBMS
- Dependency Preserving Decomposition in DBMS
- Lossless Join Decomposition in DBMS
- Problems with Null Values and Dangling Tuples
- Multivalued Dependency in DBMS
- Query Optimization in DBMS
- Algorithms for Select, Project and Join Operations in DBMS
- Query Optimization Methods in DBMS
DBMS Part-4
- Transactions in DBMS
- Serializability in DBMS
- Recoverability in DBMS
- Recovery Techniques in DBMS
- Log Based Recovery in DBMS
- Checkpoint in DBMS
- Deadlock in DBMS
- Concurrency Control in DBMS
- Lock Based Protocol in DBMS
- Timestamp Based Protocol in DBMS
- Validation Based Protocol in DBMS
- Multiple Granularity in DBMS
- Multi-Version Concurrency Control(MVCC) in DBMS
- Recovery with Concurrent Transactions in DBMS
DBMS Part-5
Multi-Version Concurrency Control (MVCC)
MVCC ek aisa technique hai jo DBMS (Database Management System) mein use hota hai multiple transactions ko ek saath chalane ke liye — bina kisi conflict ke.
MVCC ka basic idea:
Jab koi data read ya write hota hai, to system us data ki purani aur nayi versions ko alag-alag sambhalta hai.
MVCC kyun use hota hai?
Jab traditional locking system use hota hai, to:
Agar koi transaction data read kar raha ho, to koi doosra transaction us data ko update nahi kar sakta
Aur agar koi transaction data write kar raha ho, to read bhi block ho jata hai
MVCC is problem ko solve karta hai:
Readers writers ko block nahi karte
Writers readers ko block nahi karte
Example:
Maan lo ek table hai Employee, jisme ek row hai:
Ab maan lo:
Transaction T1 salary ko read kar raha hai
Transaction T2 salary ko update kar raha hai to 60,000
MVCC kya karega?
| Version | Salary | Kisne Banaya | Kab Valid Hai |
|---|---|---|---|
| V1 | 50,000 | T0 (pehle ka txn) | T1 jese old readers ke liye |
| V2 | 60,000 | T2 | T2 ke baad ke readers ke liye |
T1 ko salary 50,000 dikhega
T2 ek nayi version banayega 60,000 ke saath
Dono transactions bina rukhe chalenge
MVCC kaise kaam karta hai?
1. Read Operation:
Jab koi transaction data read karta hai, to wo us version ko padhta hai jo uske start time ke liye valid ho.
2. Write Operation:
Jab koi transaction write karta hai, to wo nayi version banata hai — purani version ko touch nahi karta.
MVCC vs Locking Protocol
| Feature | MVCC | Locking Protocol (2PL) |
|---|---|---|
| Blocking | Nahin hota | Hota hai |
| Performance | Fast for read-heavy systems | Slow if reads/writes mixed |
| Storage Usage | Zyada (version store hoti hai) | Kam storage chahiye |
MVCC ke Advantages
| Fayda | Description |
|---|---|
| No Blocking | Readers aur writers ek dusre ko block nahi karte |
| Fast Read | Read-heavy systems mein bahut fast hota hai |
| Snapshot View | Har transaction ko apna “snapshot” milta hai |
| Concurrency | Multiple transactions smooth chalti hain |
MVCC ke Disadvantages
| Nuksaan | Description |
|---|---|
| Storage Use | Har version ko store karne ki wajah se space lagta hai |
| Clean-up Needed | Purane versions ko delete karna padta hai (garbage collection) |
| Complex Logic | Version track karna aur visibility rules banana mushkil hota hai |
Summary Table
| Point | Explanation |
|---|---|
| Concurrency | High — multiple txn together |
| Locking | Nahin chahiye |
| Versioning | Haan, har data ka version hota hai |
| Example | Read gets old, Write creates new |
| Best For | Analytical systems (zyada reads) |
Real-Life Use
MVCC ka use popular DBMS systems mein hota hai:
PostgreSQL
Oracle
MySQL (InnoDB Engine)