Concurrency Control Techniques in DBMS Quiz

Study Notes

Learning Objectives

Explain the nature and characteristics of transactions in database management.
Understand transaction-commit and rollback operations for handling transaction aborts.
Articulate the purposes, necessity, and methods of concurrency control in databases.
Explain 2-phase locking (2-PL) as a concurrency control technique.
Understand and apply various transaction isolation levels.
Identify causes of transaction deadlocks and propose solutions.

Concept of a Transaction

A transaction is a series of operations that transitions a database from one consistent state to another.
It operates as a unit of work performing multiple read and write operations simultaneously.

ACID Properties of Transactions

Atomicity: Ensures all data modifications occur fully or not at all, maintaining transaction integrity.
Consistency: Guarantees that transactions bring the database to a consistent state, using methods like locking and timestamps to prevent interference.
Isolation: Ensures the integrity of transactions where no other transaction can access data until the current transaction is committed or rolled back.
Durability: Confirms that successful transactions persist in the database, even in the event of failures, often through recovery algorithms.

Transaction Completion Operations

Transaction Commit: Finalizes a transaction by permanently embedding the results into the database.
Transaction Rollback: Reverts database changes made by a transaction, restoring it to its prior state upon a fatal error or explicit command.

Considerations in Transaction Execution

Concurrency implementation enhances throughput and system utilization, while reducing latency.
Quick transaction execution minimizes the chance of conflicts and deadlocks, necessitating a balance between locking duration and performance.

Serializable Schedule Definition

A serializable schedule results in the same outcome as sequential transaction execution, ensuring transaction serialization.

Concurrency Control Techniques

Lock-based Technique: Prevents transactions from reading or writing data until the necessary locks are acquired.
Timestamp-based Technique: Uses system time or logical counters as timestamps to serialize concurrent transactions, prioritizing older transactions.
Multi-version Technique: Creates duplicate record copies to avoid conflicts, allowing simultaneous data reads and writes.
Verification (Optimistic) Technique: Assumes transactions will not interfere and only validates at commit time.

Lock-based Technique

Locks prevent concurrent access to data items, allowing a single user to perform operations safely.
Lock requests are handled by a concurrency-control manager, who grants transaction access upon unlocking.

Timestamp-based Technique

Assigns timestamps to transactions to enforce execution order.
Older transactions are prioritized over newer ones, mitigating the risk of deadlocks.
Example: Transaction T1 (time 0010) executes before T2 (time 0020) and T3 (time 0030).

Advantages and Disadvantages of Timestamp Technique

Advantages: Guarantees serializability like 2PL and prevents transaction wait times, reducing deadlock occurrences.
Disadvantages: Potential for starvation if transactions are repetitively aborted.

Multi-version Technique

MVCC generates record duplicates to facilitate concurrent reads and writes without blocking.
Benefits include reduced lock necessity, improved read performance, and enhanced write isolation, leading to fewer deadlocks.

How MVCC Works

Every database record is assigned a version number.
Concurrent reads are directed to the record version with the highest number, optimizing data access without conflicts.

Description

Test your knowledge on concurrency control techniques in Database Management Systems, including lock-based, timestamp-based, multi-version, and verification-based techniques. Learn about how lock-based protocols help eliminate concurrency problems by isolating transactions for single users.