DBMS IN TELUGU PART 23 - CONCURRENCY CONTROL IN TELUGU | LOCK BASED | TIME STAMP | TWO PHASE LOCK |

What is Concurrency Control?

Introduction to Concurrency Control

• The speaker, Arun Reddy, introduces the topic of concurrency control in database management systems (DBMS).

• He encourages viewers to subscribe and like his channel for more content related to DBMS.

Definition of Concurrency Control

• Concurrency refers to operations being executed simultaneously or in parallel.

• Concurrency control involves managing these simultaneous operations to ensure data integrity and consistency.

Importance of Managing Concurrent Transactions

Need for Protocols

• It is crucial to control concurrent transactions as multiple transactions can occur at the same time.

• The speaker mentions that there are specific concurrency control protocols designed to ensure properties like atomicity, isolation, serializability, and concurrency.

Problems with Concurrent Execution

• Before discussing protocols, he highlights potential problems arising from concurrent execution.

Types of Problems in Concurrent Execution

Main Operations: Read and Write

• In a database transaction, the two primary operations are read and write.

• Proper management of these operations is essential during concurrent execution; otherwise, data may become inconsistent.

Performance Impact

• If transactions do not perform read/write operations in an interleaved manner, it can lead to performance issues.

Specific Issues Arising from Concurrent Transactions

Types of Conflicts

1. Last Update Problem:

• Occurs when two transactions perform write operations on the same data item leading to inconsistency.

2. Dirty Reading Problem:

• Happens when one transaction reads data modified by another transaction that has not yet been committed.

3. Non-repeatable Read Problem:

• This occurs when a transaction reads the same row twice but gets different values due to updates made by other transactions.

Explaining Last Update Problem

Conflict Scenario

• The last update problem arises when two transactions attempt to write on the same data item concurrently.

Example Explanation

• For instance, if one transaction reads a balance as 1000 while another adds 500 before committing changes, inconsistencies arise if both try writing back their results without proper controls.

Understanding Dirty Reading Issue

Transaction Rollback Scenario

• A dirty reading occurs if a transaction reads uncommitted changes from another transaction that later rolls back its changes.

Transaction Rollback and Dirty Reads

Understanding Transaction Rollbacks

• The transaction was rolled back due to issues, possibly network-related, indicating that the rollback occurred at the sixth position.

• After reading a value of 1500, an addition of 500 was made. However, when the transaction rolled back, this addition was canceled, leading to a balance adjustment.

Dirty Read Conflicts

• A dirty read occurs when one transaction reads data from another transaction that has not yet been committed. This is referred to as a dirty read conflict.

• The issue arises when two transactions read the same data item but receive different values due to uncommitted changes in between their reads.

Non-repeatable Reads

• In a non-repeatable read scenario, if a transaction reads a value and then another transaction modifies it before the first transaction completes, subsequent reads may yield different results.

• This inconsistency leads to unpredictable outcomes within transactions that are executed concurrently.

Concurrency Control Protocols

Importance of Concurrency Control

• To manage conflicts arising from concurrent transactions, concurrency control protocols are essential for maintaining consistency.

• These protocols aim to prevent inconsistencies during simultaneous execution of transactions by ensuring proper isolation.

Types of Concurrency Control Protocols

• Key protocols include:

• Lock-based protocol

• Timestamp protocol

• Validation-based protocol

Lock-Based Protocol Overview

Achieving Consistency with Locking

• Lock-based protocols help achieve consistency by isolating transactions until they complete their operations on shared data.

Types of Locks in Lock-Based Protocol

• There are two main types of locks:

• Shared lock: Allows multiple users to read data simultaneously without interference.

• Exclusive lock: Restricts access so only one user can modify the data until they release the lock.

Understanding Shared and Exclusive Locks

Functionality of Shared Locks

• A shared lock (denoted as S) allows multiple transactions to read the same data concurrently without causing conflicts since no updates occur during this time.

Functionality of Exclusive Locks

• An exclusive lock (denoted as X), however, prevents other transactions from accessing or modifying locked data until it is released after completion.

Unlocking Mechanism

Understanding Shade Locks and Exclusive Locks

Overview of Shade Locks

• A shade lock allows multiple transactions to read data simultaneously, but only permits reading operations.

• When a transaction requests a shade lock, it can be granted even if another transaction is currently holding the lock, as long as no write operations are involved.

• An exclusive lock prevents any other locks from being granted until it is released; thus, a shade lock cannot coexist with an exclusive lock.

Interaction Between Lock Types

• If an exclusive lock is held by one transaction (e.g., t1), subsequent requests for shade locks will only be granted after the exclusive lock is released.

• While an exclusive lock allows both read and write operations, a shade lock strictly permits reads only. This distinction is crucial in understanding their functionalities.

Requesting Locks

• Transactions cannot request an exclusive lock while another transaction holds it; they must wait until the current holder releases it.

• Only when there are no active exclusive locks can additional transactions acquire shade locks for reading purposes.

Locking Protocols Explained

Types of Locking Protocols

• The discussion introduces four main locking protocols: Simplistic Lock Protocol, Preclaim Lock Protocol, Two-phase Locking Protocol, and Strict Two-phase Locking Protocol.

Simplistic Lock Protocol

• This protocol allows transactions to obtain locks on data before performing any insertions or updates. Once the transaction completes its work on that data item, it releases the lock.

Preclaim Lock Protocol

• In this protocol, transactions check what locks they will need before starting execution. They preemptively acquire all necessary locks to ensure smooth operation once they begin.

Execution Flow in Preclaim Lock Protocol

• Before executing a transaction, all required locks must be acquired first. If not all requested locks are granted initially, the transaction waits until they are available before proceeding.

Two-phase Locking Mechanism

Structure of Two-phase Locking

• The two-phase locking mechanism consists of two distinct phases: Growing Phase and Shrinking Phase.

Growing Phase

• During this phase, transactions can acquire new locks but cannot release any existing ones. This ensures that once a transaction starts acquiring resources, it maintains control over them until it's ready to complete its operations.

Shrinking Phase

• After reaching a certain point in execution where some resources may be released (not covered in provided text).

Understanding Locking Protocols in Database Transactions

Overview of Locking Mechanisms

• The discussion begins with the concept of releasing a lock and the implications of making requests after a lock has been released. It emphasizes that once a transaction enters the growing phase, it can make multiple requests for locks.

• The transition from the growing phase to the shrinking phase is explained, highlighting that once a task is completed, no new locks should be acquired.

• The difference between two-phase locking (2PL) and strict two-phase locking protocols is introduced. In strict 2PL, locks must be committed before they can be released.

Strict Two-Phase Locking vs. Two-Phase Locking

• A key distinction is made: while two-phase locking allows for potential dirty reads if locks are released prematurely, strict two-phase locking ensures that all locks are held until commit.

• This guarantees that problems such as dirty reads do not occur since locks are only released after successful commits.

Types of Locks in Protocols

• The session discusses various types of locks within locking protocols: simple lock, preemptive lock, and exclusive lock. Each serves different purposes in managing database transactions.

• Four main types of locking protocols are identified:

• Simple Lock

• Preemptive Lock

• Two-Phase Locking

• Strict Two-Phase Locking

Time Stamp Ordering Protocol

• Transitioning to time-stamping methods, it explains how each transaction receives a unique token or timestamp which determines its order in processing.

• Lower timestamps indicate older transactions; thus, they have priority over newer ones when processed by the system.

Transaction Management Based on Timestamps

• Timestamps serve as unique identifiers for transactions and help maintain their order based on arrival time. This method prevents long wait times for older transactions due to newer ones arriving continuously.

• The system clock or logical counting can determine timestamps ensuring efficient management of transaction priorities without excessive delays.

Handling Transaction Conflicts

• Older transactions receive preference over newer ones to ensure none remain pending for extended periods. This approach minimizes waiting times across all transactions involved.

• An example illustrates how conflicts arise when one transaction reads data while another modifies it simultaneously; this highlights the importance of proper timestamp management to avoid inconsistencies.

Conclusion on Timestamp Usage

• The discussion concludes with an emphasis on using timestamps effectively to manage transaction states and prevent issues like reading outdated data during concurrent operations.

Understanding Transaction Issues in Database Systems

Key Concepts of Read and Write Transactions

• The discussion begins with the introduction of a problem arising from an additional 500 units added during the first transaction, highlighting the importance of checking conditions when performing read transactions.

• It is explained that in terms of ranking, a write transaction must be greater than a read transaction for it to be accepted; otherwise, it will be rejected.

• A scenario is presented where if a read transaction completes before a write transaction, the system can accept this sequence without issues.

• Problems arise when a write transaction occurs after a read transaction but is smaller; such transactions should also be rejected to maintain consistency.

• The concept of overwriting is introduced, emphasizing that if two transactions clash (one being older), only the older one should proceed while rejecting any younger ones.

Timestamp-Based Protocols

• Timestamp-based protocols are discussed as they provide preference to older transactions over newer ones, ensuring that only valid sequences are executed.

• The validation-based protocol is introduced as another method for concurrency control alongside locking mechanisms and timestamping systems.

Validation Phase in Transactions

• The validation phase consists of three main steps: reading, validating, and writing. Each step ensures that transactions do not interfere with each other improperly.

• During the read phase, all data items are temporarily stored without updating the actual database until validation confirms their correctness against existing data.

• In the validation phase, checks are performed to ensure that no violations occur regarding serializability rules between transactions.

Finalizing Transactions

• If both previous phases validate correctly, results can be committed back to the database; otherwise, they will roll back to prevent inconsistencies.

• Each phase operates under different timestamps which help manage execution order and maintain consistency across multiple concurrent operations.

Types of Concurrency Control Protocols

• Three primary types of concurrency control protocols are identified: locking protocols (which prevent inconsistent data access), timestamp protocols (which prioritize older transactions), and validation-based protocols (which check validity before committing).