Concurrency Control in DBMS

Concurrency control is a very important concept of DBMS which ensures the simultaneous execution or manipulation of data by several processes or user without resulting in data inconsistency.

Concurrency Control deals with interleaved execution of more than one transaction.

Concurrency control provides a procedure that is able to control concurrent execution of the operations in the database. 

Concurrency Control Problems

The database transaction consists of two major operations “Read” and “Write”. It is very important to manage these operations in the concurrent execution of the transactions in order to maintain the consistency of the data. 

1. Dirty Read Problem (Write-Read conflict)

Dirty read problem occurs when one transaction updates an item but due to some unconditional events that transaction fails but before the transaction performs rollback, some other transaction reads the updated value. Thus creates an inconsistency in the database.

Ex: Consider two transactions T1 and T2

·         Transaction T1 modifies a database record without committing the changes.

·         T2 reads the uncommitted data changed by T1

·         T1 performs rollback

·         T2 has already read the uncommitted data of T1 which is no longer valid, thus creating inconsistency in the database.

2. Lost Update Problem

Lost update problem occurs when two or more transactions modify the same data, resulting in the update being overwritten or lost by another transaction.

Ex: Consider two transactions T1 and T2

·         T1 reads the value of an item from the database.

·         T2 starts and reads the same database item.

·         T1 updates the value of that data and performs a commit.

·         T2 updates the same data item based on its initial read and performs commit.

·         This result in the modification of T1 gets lost by the T2’s write which causes a lost update problem in the database.

Concurrency control ensures transaction atomicity, isolation, consistency, and serializability.

 

 

 

 

 

Recursive Queries in SQL

Recursive queries in SQL are queries that involve self-referential relationships within a table. They allow you to perform operations that require iterative processing, enabling you to traverse and manipulate hierarchical data structures efficiently.

Recursive queries are SQL queries that can call themselves.

In SQL, recursion can be performed using Common Table Expressions (CTEs). CTEs are temporary result sets that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs are special kinds of CTEs that allow you to perform recursive tasks and queries.

The general structure of a recursive CTE is:

WITH RECURSIVE cte_name (column_name(s))

AS (

    -- Base case

    SELECT ...

    UNION ALL

    -- Recursive case

    SELECT ...

    FROM cte_name ...

)

 

SELECT * FROM cte_name;

Here,

1.      WITH RECURSIVE: This clause indicates that recursion is going to be used in the query.

2.      cte_name (column_name(s)): Here the name of  CTE  is defined and the names of the columns that it will contain.

3.      SELECT ... UNION ALL SELECT ...: This is the heart of recursion. The first SELECT statement is the base case(anchor), and the second SELECT statement is the recursive case. UNION ALL is used to combine the results of both.

4.      FROM cte_name ...: In the recursive SELECT statement, the CTE is referenced to itself.

5.      SELECT * FROM cte_name;: Finally, query the CTE to get our results.

Example:

WITH RECURSIVE employee_hierarchy AS (

    SELECT employee_id, employee_name, manager_id

    FROM employees

    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id

    FROM employees e

    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id

)

SELECT * FROM employee_hierarchy;