Database Concepts: Comprehensive Guide
Multi-Version Scheme
The multi-version scheme (MVCC) allows multiple versions of data to exist simultaneously,
improving concurrency and avoiding conflicts.
Key points:
- Keeps multiple versions of data.
- Readers access old versions; writers create new ones.
- Readers and writers don't block each other.
Advantages:
- High concurrency.
- Better performance for complex transactions.
Example:
Imagine editing a notebook where new updates go to a new page, while others can still read the old
page.
Difference Between Single-Version and Multi-Version Schemes
| Aspect | Single-Version Scheme | Multi-Version Scheme |
|----------------------|----------------------------------------|-------------------------------------|
| Data Storage | Only the latest version is stored. | Multiple versions are stored. |
| Concurrency | Uses locks; may block readers/writers.| Readers and writers don't block. |
| Updates | Overwrites data directly. | Creates new versions for updates. |
| Storage Needs | Requires less space. | Needs more storage. |
| Performance | Slower with high traffic. | Faster for concurrent users. |
Recovery with Concurrent Transactions
Recovery ensures the database returns to a consistent state after a failure, even when transactions
run concurrently.
Steps:
1. Logs record all changes (before and after values).
2. Undo incomplete transactions to maintain consistency.
3. Redo completed transactions to ensure durability.
Example:
If T1 is complete but T2 isn't, redo T1's changes and undo T2's.
Importance:
- Ensures consistency and durability even during failures.
Single-User vs Multi-User Environments
| Aspect | Single-User Environment | Multi-User Environment |
|-----------------|----------------------------------------|------------------------------------|
| Definition | One user accesses the system. | Multiple users access simultaneously.|
| Concurrency | No conflicts; simple. | Needs concurrency control. |
| Complexity | Easy to manage. | More complex. |
| Examples | Personal apps. | Banking systems, enterprise DBs. |
Case Study: Oracle's Concurrency Control
Oracle uses advanced techniques to handle concurrency in multi-user environments.
Techniques:
1. **MVCC**: Readers see old data while writers update it.
2. **Locks**: Shared for reading, exclusive for writing.
3. **Row-Level Locking**: Locks only specific rows, not the entire table.
4. **Deadlock Detection**: Detects and resolves blocking loops.
Benefits:
- High performance and scalability.
- Maintains consistency and avoids conflicts.
