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;

Trigger

A trigger is a statement that is executed upon modification to the database. A trigger is triggered automatically when an associated DML statement is executed.

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. 

A trigger description contains three parts:

Event: A change to database that activates the trigger

Condition: A query or test that is run when the trigger is executed

Action: A procedure that is executed when the trigger is activated and its condition is true.

Users may not be aware that a trigger was executed as a side effect of the procedure.

Syntax:

create trigger [trigger_name]

[before | after] 

{insert | update | delete} 

on [table_name] 

[for each row] 

[trigger_body]

Explanation of syntax:

1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired

BEFORE and AFTER of Trigger:

BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

Procedures and Functions

A procedures or function is a group or set of SQL and PL/SQL statements that perform a specific task.

A function and procedure is a named PL/SQL Block which is similar. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

Procedures:

A procedure is a named PL/SQL block which performs one or more specific task.  A procedure has a header and a body.

The header consists of the name of the procedure and the parameters or variables passed to the procedure. 

The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is named for repeated usage.

Parameters to procedures can be passes in three ways:

Parameters	Description
IN type	These types of parameters are used to send values to stored procedures.
OUT type	These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
IN OUT type	These types of parameters are used to send values and get values from stored procedures.

A procedure may or may not return any value.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}    <Datatype>,…) 

IS

  Declaration section<variable, constant> ;

BEGIN

  Execution section

EXCEPTION

  Exception section 

END

There are two ways to execute a procedure :

• From the SQL prompt : EXECUTE [or EXEC] procedure_name;
• from embedded SQl by call statement: call procedure-name;

Functions:

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

 Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 

 RETURN return_datatype;  {IS, AS}

 Declaration_section <variable,constant> ;

 BEGIN  

    Execution_section

    Return return_variable;  

 EXCEPTION

   exception section  

    Return return_variable; 

 END;

A function can be executed in the following ways.

• As a part of a SELECT statement : SELECT emp_details_func FROM dual;
• In a PL/SQL Statements like,  :  dbms_output.put_line(emp_details_func);

Procedures VS Functions:

• A function MUST return a value
• A procedure cannot return a value
• Procedures and functions can both return data in OUT and IN OUT parameters
• The return statement in a function returns control to the calling program and returns the results of the function
• The return statement of a procedure returns control to the calling program and cannot return a value
• Functions can be called from SQL, procedure cannot
• Functions are considered expressions, procedure are not

Accessing SQL from Programming Languages

A database programmer must have access to a general-purpose programming language for at least two reasons.

• Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language.
• Non-declarative actions – such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface – cannot be done from within SQL

To access SQL from other programming languages, there are two approaches

• Dynamic SQL
• Embedded SQL

I. Dynamic SQL

A general purpose programming language can connect to and communicate with a database server using a collection of functions or methods.

The dynamic SQL component of SQL allows programs to construct and submit SQL queries at runtime.

The two standards for connecting to an SQL database and perform queries and updates

        1. JDBC (Java Database Connectivity)

        2. ODBC (Open Database Connectivity)

1. Java Database Connectivity (JDBC)

JDBC is an API (Application Programming Interface) for communicating with database systems supporting SQL.

JDBC supports a variety of features for querying and updating data. JDBC also supports metadata retrieval.

The communication with a database can be done as

1. Open a Connection

2. Create a “statement” object

3. Execute queries using statement object to fetch results

4. Close the connection.

2. Open Database Connectivity

The ODBC standard defines an API (Application Programming Interface) that applications can use to open a connection with a database, send queries and updates, and get back the results.

Every database supporting ODBC provides a library that must be linked with the client program. When client program makes an ODBC call, the code in library communicates with the server to carry out the requested action and fetch results.

The first step is to set up a connection with the server. The program opens the database connection by using SQLConnect call. This call takes parameters including connection handle, the server to connect, the user identifier and the password to the database.

Once the connection is established, the program can send SQL commands to the database by using SQLExecDirect.

At the end of the session, the program frees the statement handle, disconnects from the database, and frees up the connection and SQL environment handles.

II. Embedded SQL

It provides a means by which a program can interact with a database server. The SQL statements are identified at compile time using a preprocessor. The preprocessor submits the SQL statements to the database system for pre compilation and optimization then replaces the SQL statements in the program with appropriate code and functions before invoking the programming language compiler.

SQL queries are embedded in a host language. The SQL structures permitted in the host language is called embedded SQL. Host language programs can use embedded SQL syntax to access and update data stored in a database.

EXEC SQL statement is used to identify embedded SQL request to the processor.

EXEC SQL <embedded SQL statement>;

The syntax depends on the host languages.

EXEC  SQL connect to sever user username using password;

This statement establishes connection between the program and database.

To write an embedded SQL query, the declare cursor statement is used.

            declare c cursor for <SQL query>;

Here, ‘c’ is a variable used to identify the query.

EXEC SQL open c;

This statement executes the query and save the results in a temporary relation.

EXEC SQL fetch c into :si,:sn;

This statement fetches tuple in the query result. Repeated calls to fetch statement get successive tuples in the query result.

EXEC SQL close c;

This statement causes the database system to delete the temporary relation that holds the results of the query.

Extended E-R Features

Extended ER is a high-level data model that incorporates the extensions to the original ER model. Enhanced entity-relationship diagrams are advanced database diagrams very similar to regular ER diagrams which represent the requirements and complexities of complex databases. 

Enhanced ER Model concepts are:

• Generalization

• Specialization

• Aggregation

Generalization

Generalization is a bottom-up approach in which two or more entities can be generalized to a higher-level entity if they have some attributes in common.

It is the process of extracting common properties from a set of entities and creating a generalized entity from it. This process combines a number of entity sets that share the same features into higher-level entity sets.

Generalization is used to emphasize the similarities among the lower level entity sets and to hide the differences.

Specialization

In specialization, an entity is divided into sub-entities based on its characteristics. It is a top-down approach where the higher-level entity is specialized into two or more lower-level entities. 

The process of designing sub groupings within an entity set is called specialization. Lower level entity sets inherits all the attributes and relationship of the higher level entity set to which it is linked.

Specialization can be depicted in E-R diagram by a hallow arrow-head pointing from specialized entity to the other entity.

Aggregation

Aggregation allows us to indicate that a relationship set participates in another relationship set. It is an abstraction in which relationship sets are treated as higher level entity sets and can participate in relationships.

Aggregation allows relationships between relationships. Aggregation treat relationship as an abstract entity.