Q: Explain query processing and query optimization techniques in DBMS.

Answer:

Query processing is the process of turning a SQL query into a good execution plan and then running
that plan.

Phases of Query Processing:

1. Parsing and Translation:

 Syntax and meaning of the SQL query are checked to make sure they are right.
 Changed into an internal form like a relational algebra formula or a parse tree
2. Query Optimization:
 The machine checks out several similar ways to run queries.
 Chooses the best plan based on cost (CPU, I/O, etc.).
3. Query Evaluation (Execution):
 The selected plan is executed using database operations like: Selection,
Projection, and Join.
 The final result will be produced.

Query Processing Flow:

SQL Query → Parser → Translator → Optimizer → Execution Engine → Result

Query Optimization: It is the process of choosing the best execution plan among many alternatives.
The main objective of Query Optimization: Minimize Disk I/O, CPU time, and Memory usage and
Improve query response time.

Types of Query Optimization Techniques:

1. Heuristic-Based Optimization (Rule-Based): Uses predefined rules to improve query

performance. The following are some common Heuristic Rules:
 Selection Pushdown: Apply selection early.
 Projection Pushdown: Reduce number of attributes early.
 Join Ordering: Perform joins in an efficient order.
 Replace Cartesian Product with Join.

The advantage is fast work, no cost calculation but it is not always optimal.
 2. Cost-Based Optimization (CBO): It evaluates different execution plans using statistics (table
size, indexes, selectivity) and chooses the plan with minimum estimated cost.
3. Semantic Query Optimization: It uses integrity constraints (like UNIQUE or NOT NULL) to
simplify the query. For example, if a query searches for a value in a column where a
constraint says no such value can exist, the optimizer can skip the scan entirely
Q. Discuss concurrency control techniques and explain deadlock handling

Answer: Concurrency control is the process of managing simultaneous transactions without causing
inconsistency in the database. The main objectives are maintaining data consistency, ensuring
isolation among transactions and preventing lost update, dirty read, and unrepeatable read problem.
Problems due to Concurrent Transactions:
1. Lost Update Problem

Suppose that the operations of T1 and T2 are interleaved in such a way that T2 reads the value of
account A before T1 updates its value in the database. Now, when T2 updates the value of account A in
the database, the value of account A updated by the transaction T1 is overwritten and hence, is lost.
This is known as lost update problem.

The value of account A at the end of both the transactions is $2040 instead of $1938 which leads to
data inconsistency.

The interleaved execution of transactions T1 and T2 leads to lost update problem.

2. Dirty Read Problem: when a transaction fails after updating a data item, and before this data
item is changed back to its original value, another transaction reads this updated value.

For example, assume that T1 fails after debiting $100 from account A, but before crediting
this amount to account B. This will leave the database in an inconsistent state. The value of
account A is now $1900, which must be changed back to original one, that is, $2000.
However, before the transaction T1 is rolled back, let another transaction T2 reads the
incorrect value of account A. This incorrect value of account A that is read by transaction T2
is called dirty data, and the problem is called dirty read problem.
 3. Unrepeatable read: The third problem occurs when a transaction tries to read the value of the
data item twice, and another transaction updates the same data item in between the two read
operations of the first transaction. As a result, the first transaction reads varied values of same
data item during its execution. This is known as unrepeatable read.

For example, consider a transaction T3 that reads the value of account A. At this point, let another
transaction T4 updates the value of account A. Now if T3 again tries to read the value of account A, it
will get a different value. As a result, the transaction T3 receives different values for two reads of
account A. This different values of A that is read by Transaction T3 is called unrepeatable read. and
the problem is called unrepeatable read problem.
The interleaved schedule of transactions T3 and T4 that leads to a problem of unrepeatable read.
The three anomalies, namely, lost update, dirty read, and unrepeatable read can also be described in
terms of when the actions of two transactions, say T1 and T2, conflict with each other. In this case, the
lost update problem is known as write-write (WW) conflict, the dirty read problem is known as write-
read (WR) conflict, and unrepeatable problem is known as read-write (RW) conflict.
Deadlock: A deadlock occurs when two or more transactions wait indefinitely for each other’s locked
resources.
T1 locks A
T2 locks B
T1 requests B → waiting
T2 requests A → waiting
Neither transaction can proceed.
Deadlock Prevention
The system prevents deadlocks by following rules that avoid circular waiting.
1. Wait-Die Scheme (Non-preemptive): If an older transaction requests a lock held by a
younger one → it waits. If a younger one requests a lock held by an older → it dies (rolls
back).
2. Wound-Wait Scheme (Preemptive): If an older transaction requests a lock held by a younger
→ younger one rolls back. If younger requests from older → it waits.
3. Resource Ordering: Transactions request locks in a fixed order (like Graph-Based
Locking).

Deadlock Detection
System allows deadlocks to occur but detects them using a Wait-For Graph (WFG).
Each transaction = a node.
Edge T1 → T2 means T1 is waiting for T2.
Cycle in the graph → Deadlock exists.
Example:
T1 → T2 → T3 → T1. Cycle found → Deadlock detected.
Detection is automatic
Deadlock Recovery
Once a deadlock is detected, system recovers by rolling back one or more transactions.
Methods:
• Abort victim: Choose transaction to rollback (based on priority, age, or cost).
• Restart transaction after some delay.
System continues normally after recovery.

Q: Explain locking techniques and serializability.

A lock is a control mechanism used to manage concurrent access to data items in a database. It acts
as a control block, containing information about the type of lock (e.g., shared or exclusive) and
identity the transaction that holds the lock.
The locking or unlocking of the data items is implemented by a subsystem of the database
system known as lock manager.
Functions of the Lock Manager:
i) Receives lock requests from transactions: Replies with ✅ Lock Grant message → if the lock
can be assigned and ❌ Rollback message → if a deadlock is detected.
ii) Unlock requests: Sends an acknowledgement. May trigger lock grants to other transactions
waiting for the data item.
iii) Handling Waiting Transactions: When multiple transactions are waiting for the same data
item, the lock manager must decide the order in which locks are granted following some
priority technique, Otherwise, it may result in starvation.
Types of Lock:
 Shared Lock (S-Lock): Used for read operations. Multiple transactions can hold shared locks
simultaneously.
 Exclusive Lock (X-Lock): Used for writing operations. Only one transaction can hold an
exclusive lock at a time
 Two-Phase Locking Protocol (2PL): Widely used concurrency control protocol. A transaction
executes in two phases:
Growing Phase: Transaction acquires locks and cannot release locks.
Shrinking Phase: Transaction releases locks and cannot acquire new locks.
Serializability: The consistency of the database under concurrent execution can be ensured by
interleaving the operations of transactions in such a way that the concurrent final output is same as
that of some serial schedule of those transactions. Such a schedule is referred to as serializable
schedule. The process is known as serializability.

A concurrent schedule resulting in an A concurrent schedule resulting in

inconsistent state of database consistent state of the database

The final values of accounts A, B, and C are The final values of accounts A, B, and C are $1900,
$1900, $1600, and $550. Thus, the sum A + B $1550, and $550. Thus, the sum A + B + C is
+ C is not preserved preserved and hence, it is a serializable schedule.

Q; Explain transaction management and types of failures in DBMS

Answer:
Transaction: A collection of operations that form a single logical unit of work is called a
transaction.
A transaction may contain: Read operations, Write operations, and Insert/Delete/Update
commands.
Transaction management is the process of handling transactions to maintain: Data
consistency, Concurrency, and Recovery from failures.
Desirable properties of transaction:
 To ensure the integrity of the data, the database system must maintain some desirable
properties of the transaction.
 These properties are known as ACID properties, the acronym derived from the first
letter of the terms a tomicity, c onsistency, i solation, and d urability.
 Atomicity implies that either all of the operations that make up a transaction should
execute or none of them should occur. It is the responsibility of the transaction
management component of a DBMS to ensure atomicity.
 Consistency implies that if all the operations of a transaction are executed
completely, the database is transformed from one consistent state to another. It is the
responsibility of the application programmers who code the transactions to ensure
consistency of the database.
 Isolation implies that each transaction appears to run in isolation with other
concurrently running transactions. That is, the execution of a transaction should not be
interfered by any other concurrently running transaction. It is the responsibility of the
concurrency control component (will discussed later in detail) of the database system
to allow concurrent execution of transactions without any interference from each.
 Durability (also known as permanence) implies that once a transaction is completed
successfully, the changes made by the transaction persist in the database, even if the
system fails. The durability property is ensured by the recovery management
component of the database system.
Example:
T : read(A);
1

A:=A–100;

write(A);

read(B);

B:= B+100;

write(B);

 If transaction T1 is executed, either $100 should be transferred from account A to B or

neither of the accounts should be affected. If T1 fails after debiting $100 from account
A, but before crediting $100 to account B, the effects of this failed transaction on
account A must be undone. This is the atomicity property of transaction T1.
 The execution of transaction T1 should also preserve the consistency of the database,
that is, the sum of the values of account A and B should be $3500 even after the
execution of T1.
 Now, let us consider the isolation property of T1. Suppose that during the execution of
transaction T1 when $100 is debited from account A and not yet credited to account
 B, another concurrently running transaction, say T2 reads the values of account A and
B. Since T1 has not yet completed, T2 will read inconsistent values. The isolation
property ensures that the effects of the transaction T1 are not visible to other
transaction T2 until T1 is completed.
 If the transaction T1 is successfully completed and the user who initiated the
transaction T1 has been notified about successful transfer of funds, then the data
regarding the transfer of funds should not be lost even if the system fails. This is the
durability property of T1.
State of Transaction: Whenever a transaction is submitted to a DBMS for execution,
either it executes successfully or fails due to some reasons. During its execution, a
transaction passes through various states that are active, partially committed, committed,
failed, and aborted.

Types of Failures in DBMS:

Transaction Failure: Occurs when a transaction cannot compete successfully. Invalid input,
Arithmetic error, Logical error, Deadlock.
System Failure (System Crash): Occurs due to operating system or hardware malfunction.
Power failure, OS crash, Hardware malfunction.
Media Failure: Physical damage to storage devices. Disk crash, Head crash, Corrupted
storage media
Communication Failure: Common in distributed databases. Network failure or
Communication interruption.