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
Concurrency Control in DBMS
Concurrency kya hoti hai DBMS mein?
Concurrency ka matlab hota hai ki multiple transactions ek hi samay par chal rahe hote hain database mein.
Kyun zaroori hai concurrency?
-
Multiple users ek saath kaam kar sakein
-
System fast aur efficient chale
-
Time bache, performance achhi ho
Problem kya hota hai concurrency se?
Agar do ya zyada transactions ek hi data pe ek saath kaam karein bina kisi control ke, to data inconsistent ya galat ho sakta hai.
Common Problems in Concurrency:
| Problem | Meaning (Hinglish) |
|---|---|
| Lost Update | Dono transactions same data ko update karte hain, aur ek ka update gayab ho jata hai |
| Dirty Read | Ek transaction doosre ke incomplete (abhi commit nahi hua) data ko read kar leti hai |
| Unrepeatable Read | Ek hi query baar-baar alag result deti hai |
| Phantom Read | Query baar-baar chalane pe nayi rows milti hain |
Concurrency Control ka kaam kya hai?
Concurrency Control DBMS ka wo mechanism hai jo ensure karta hai ki:
Multiple transactions ek saath chal sakti hain
Data galat ya inconsistent na ho
Result aise aaye jaise ki transactions ek-ek karke (serially) chale ho
Concurrency Control ke Main Techniques:
1. Lock-Based Protocols
2. Timestamp-Based Protocols
3. Optimistic Concurrency Control
4. Validation-Based Protocols
1. Lock-Based Protocols
Is method mein system data items ko lock karta hai taaki ek samay pe sirf ek transaction us data ko use kare.
Lock Types:
-
Shared Lock (S-Lock): sirf read ke liye
-
Exclusive Lock (X-Lock): read + write dono ke liye
Two-Phase Locking (2PL)
2PL mein 2 phases hote hain:
-
Growing Phase – transaction locks leta hai
-
Shrinking Phase – transaction locks release karta hai
Example:
| T1 Steps | T2 Steps |
|---|---|
| Lock(A) (read) | |
| Read A | |
| Lock(A) (write) | T2 wait karega |
| Write A | |
| Unlock(A) | Ab T2 continue karega |
T2 wait karta hai jab tak T1 lock release nahi karta
2. Timestamp-Based Protocol
Is method mein har transaction ko ek timestamp milta hai jab wo start hoti hai.
-
Jo pehle start hota hai, usko priority milti hai
-
System ensure karta hai ki data older transaction ke hissaab se access ho
Rules:
-
Naye (younger) transaction agar purane data ko overwrite kare to rollback ho jata hai
Example:
| Transaction | Timestamp | Action | Allowed? |
|---|---|---|---|
| T1 | 5 | Read A | Yes |
| T2 | 10 | Write A | No(Rollback hota hai) |
3. Optimistic Concurrency Control
Yeh assume karta hai ki conflicts rare hote hain, isliye transactions bina lock ke chalti hain.
3 Phases:
-
Read Phase – data read karo
-
Validation Phase – check karo conflict to nahi
-
Write Phase – agar sab safe ho to data write karo
Pros: High performance
Cons: Agar conflict mile to rollback hoga
4. Validation-Based Protocol
Optimistic control jaisa hi hai, par ismein validation process thoda strict hota hai.
-
Transaction ko validate kiya jata hai ki wo kisi aur running transaction ke saath conflict na kare
-
Agar sab safe ho, tabhi commit hota hai
Summary Table
| Method | Basic Idea | Pros | Cons |
|---|---|---|---|
| Lock-Based | Data pe lock lagana | Simple and reliable | Deadlock ho sakta hai |
| Timestamp-Based | Time ke base pe access control | Deadlock-free | Rollbacks hote hain |
| Optimistic | Assume karo conflict nahi hoga | Fast execution | Validation/rollback |
| Validation-Based | End mein check karo safety | Safe & consistent | Complex logic |
ACID Properties yaad rakhna:
Concurrency control ensure karta hai ki har transaction ye 4 properties follow kare:
-
Atomicity – Complete ya fail, beech mein nahi rukti
-
Consistency – Data valid state mein hi rahe
-
Isolation – Ek transaction dusre se independent lage
-
Durability – Commit hone ke baad data permanent ho jaye
Final Summary:
-
Concurrency control DBMS ka important part hai
-
Ye ensure karta hai ki multiple transactions ke beech data conflict ya loss na ho
-
Iske popular methods: Locking, Timestamps, Optimistic aur Validation