Optimistic concurrency control in DBMS Theory - Full Explanation

Introduction

Imagine multiple people trying to edit the same document at the same time without locking it. How can we make sure their changes don't overwrite each other? This is the problem optimistic concurrency control solves in databases.

Explanation

Basic Idea

Optimistic concurrency control assumes conflicts are rare and lets transactions proceed without locking data. It checks for conflicts only when a transaction tries to save changes. If a conflict is found, the transaction is rolled back and must try again.

It avoids locking by checking for conflicts only at commit time.

Transaction Phases

There are three phases: read, validation, and write. First, a transaction reads data and makes changes locally. Then, before saving, it validates that no other transaction changed the data. If validation passes, it writes changes to the database.

Validation ensures no conflicting changes happened during the transaction.

Conflict Detection

Conflicts happen when two transactions modify the same data. The system detects this by comparing timestamps or versions of data read and current data. If data changed since reading, the transaction must restart.

Conflict detection relies on comparing data versions or timestamps.

Advantages

This method works well when conflicts are rare, allowing many transactions to run in parallel without waiting. It improves performance by reducing locking overhead and avoids deadlocks.

Optimistic control improves performance when conflicts are uncommon.

Disadvantages

If conflicts happen often, many transactions may be rolled back and retried, causing delays. It is less efficient in highly contested data environments compared to locking methods.

High conflict rates reduce efficiency due to repeated rollbacks.

Real World Analogy

Imagine a group of friends writing a shared story on separate sheets of paper. Each writes their part without checking others. Before combining, they check if any parts contradict. If yes, they rewrite their parts to fit together.

Basic Idea → Friends writing separately assuming no conflicts will happen.

Transaction Phases → Writing separately (read), checking for contradictions (validation), and combining sheets (write).

Conflict Detection → Finding contradictions between friends' parts before combining.

Advantages → Writing separately saves time when contradictions are rare.

Disadvantages → If many contradictions happen, rewriting wastes time.

Diagram

Diagram

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Transaction │       │   Validation  │       │     Commit    │
│     Reads     │──────▶│  Checks for   │──────▶│ Writes Changes│
│   Data State  │       │  Conflicts    │       │   if Valid    │
└───────────────┘       └───────────────┘       └───────────────┘
         │                      │                      │
         │                      │                      ▼
         │                      │             ┌─────────────────┐
         │                      │             │ Conflict Found? │
         │                      │             └───────┬─────────┘
         │                      │                     Yes/No
         │                      │                      │
         │                      │                      ▼
         │                      │             ┌─────────────────┐
         │                      │             │  Rollback and   │
         │                      │             │  Retry Needed   │
         │                      │             └─────────────────┘

This diagram shows the three phases of optimistic concurrency control and the decision to commit or rollback based on conflict detection.

Key Facts

Optimistic concurrency control → A method that allows transactions to proceed without locking and checks for conflicts at commit time.

Validation phase → The step where the system checks if data read by a transaction has been changed by others.

Conflict detection → The process of identifying overlapping changes to the same data by multiple transactions.

Rollback → Undoing a transaction when a conflict is detected to maintain data consistency.

Performance advantage → Optimistic concurrency control reduces waiting time when conflicts are rare.

Code Example

DBMS Theory

import threading
import time

class Data:
    def __init__(self):
        self.value = 0
        self.version = 0

    def read(self):
        return self.value, self.version

    def write(self, new_value, old_version):
        if self.version == old_version:
            self.value = new_value
            self.version += 1
            return True
        else:
            return False

data = Data()

# Transaction function simulating optimistic concurrency control

def transaction(name, new_value):
    value, version = data.read()
    print(f"{name} reads value {value} with version {version}")
    time.sleep(1)  # Simulate work
    success = data.write(new_value, version)
    if success:
        print(f"{name} writes value {new_value} successfully")
    else:
        print(f"{name} detects conflict and retries")

# Run two transactions concurrently
thread1 = threading.Thread(target=transaction, args=("T1", 10))
thread2 = threading.Thread(target=transaction, args=("T2", 20))

thread1.start()
thread2.start()
thread1.join()
thread2.join()

Common Confusions

Optimistic concurrency control means no conflicts ever happen.

Optimistic concurrency control means no conflicts ever happen. Conflicts can happen; this method assumes they are rare and handles them by retrying transactions after detecting conflicts.

Validation happens continuously during the transaction.

Validation happens continuously during the transaction. Validation occurs only at the end, before committing, not during the transaction's execution.

Optimistic concurrency control uses locks like pessimistic control.

Optimistic concurrency control uses locks like pessimistic control. It avoids locking data during transaction execution, unlike pessimistic concurrency control which locks data upfront.

Summary

Optimistic concurrency control lets transactions run without locking and checks for conflicts only at commit time.

It works best when conflicts are rare, improving performance by avoiding waiting and deadlocks.

If conflicts occur, transactions are rolled back and retried to keep data consistent.