Differences Between OCC and Other Concurrency Control Techniques
Aspect Optimistic Pessimistic Concurrency Timestamp-Based
Concurrency Control Control
Control
Assumption Conflicts are rare. Conflicts are frequent. Transactions ordered by
timestamps; resolves
conflicts as they arise.
Locking Does not use locks Uses locks to prevent No locks; relies on
Mechanism during execution. conflicts during timestamps for ordering.
transaction execution.
Conflict Detects conflicts at Prevents conflicts by Resolves conflicts by
Handling the end during the locking resources. comparing timestamps.
validation phase.
Performance High performance Better in high-contention Suitable for diverse
in low-contention environments. environments with proper
environments. timestamp management.
Deadlock No risk of Risk of deadlock due to No risk of deadlock.
deadlock. locking.
Key Differences Between Validation-Based Protocols and Two-Phase
Commit (2PC)
Aspect Validation-Based Protocols Two-Phase Commit (2PC)
Purpose Ensures consistency by validating Ensures atomicity of distributed
transactions for conflicts before transactions by coordinating
committing. commits.
Approach Optimistic: Assumes conflicts are Pessimistic: Uses locks and
rare; checks at the end (validation requires agreement before
phase). commit.
Phases Three: Read, Validation, and Write. Two: Prepare and Commit/Abort.
Locking No locks used during execution; Uses locks in the prepare phase;
avoids deadlocks. can cause deadlocks.
Rollback Higher in high-contention due to Rare, as conflicts are avoided via
Frequency failed validations. pre-commit agreement.
Application Works in single or distributed Used in distributed systems for
systems for concurrency control. atomic commit.
Differences Between Basic and Strict Timestamp Ordering
Aspect Basic Timestamp Ordering Strict Timestamp Ordering
Read/Write Transactions execute Adds delays to maintain strict order,
Order operations based strictly on avoiding cascading rollbacks.
timestamps.
Cascading May occur, as transactions are Prevented, as a transaction waits for
Rollbacks not delayed for earlier ones. earlier ones to finish.
Commit No dependency between Transactions depend on the commit
Dependency committing transactions. status of earlier transactions.
Overhead Lower overhead; operations are Higher overhead due to delays and
not delayed. additional checks.
Use Case Suitable for systems where Suitable for systems requiring strict
cascading rollbacks are recovery and consistency.
tolerable.
Differences Between Procedural and Nonprocedural DML
Aspect Procedural DML Nonprocedural DML
Definition Specifies both what to do and how Specifies only what to do.
to do it.
Complexity More complex; requires detailed Simpler; focuses on result
procedural logic. specification.
User Control Offers fine-grained control over Leaves query optimization to the
data operations. DBMS.
Performance Manual optimization is possible. Relies on DBMS for optimization.
Ease of Use Requires technical knowledge and Easy to use; suitable for non-
programming skills. technical users.
Learning Steeper; understanding procedural Easier to learn and use.
Curve logic is necessary.
Examples PL/SQL, T-SQL, embedded SQL SQL SELECT, relational algebra,
with loops and logic. and calculus.
