1.

Introduction to Transaction Management:

A transaction is a logical unit of work that contains one or more SQL statements. It ensures that a
group of operations either all succeed together or all fail together, maintaining the consistency of the
database.

Transaction management ensures:

• Data consistency

• Isolation between concurrent transactions

• Recoverability in case of failures

Example of a transaction:

Transferring money from Account A to Account B:

sql

CopyEdit

BEGIN;

UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 'A';

UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 'B';

COMMIT;

If any step fails, the whole transaction must be rolled back.

2. ACID Properties of Transactions:

A reliable DBMS must ensure ACID properties for transactions. These are:

A – Atomicity:

• A transaction is treated as a single unit.

• All operations must complete, or none at all.

• If one part fails, the entire transaction is rolled back.

Example:
If the debit from Account A succeeds but the credit to Account B fails, the system must undo the
debit to maintain correctness.

C – Consistency:

• A transaction brings the database from one valid state to another.

• It must preserve all integrity constraints.

Example:
If a constraint requires that total bank funds remain constant, the transaction must not violate it.

I – Isolation:

• Concurrent transactions should not interfere with each other.

• Each transaction should act as if it were the only one in the system.

Example:
Two people transferring funds at the same time should not see each other’s partial changes.

Isolation Levels:

• Read Uncommitted

• Read Committed

• Repeatable Read

• Serializable

Higher levels reduce concurrency but improve isolation.

D – Durability:

• Once a transaction is committed, its effects are permanent, even if a system crash follows.

Example:
After a successful money transfer, even if the power goes out, the changes remain intact.

3. States of a Transaction:

1. Active – Transaction is in progress.

2. Partially Committed – Final statement is executed.

3. Committed – All changes are saved.

4. Failed – Error occurred.

5. Aborted – Changes are undone (rolled back).

4. Transaction Control Commands in SQL:

Command Description

BEGIN Starts a new transaction.

COMMIT Saves all changes permanently.

Command Description

ROLLBACK Undoes all changes since the last commit.

SAVEPOINT Sets a save point within a transaction.

Example with SAVEPOINT:

sql

CopyEdit

BEGIN;

UPDATE Products SET Quantity = Quantity - 5 WHERE ProductID = 101;

SAVEPOINT qty_update;

UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1001;

-- Error occurs, rollback to savepoint

ROLLBACK TO qty_update;

COMMIT;
 1. Introduction to Concurrency Control

In a multi-user DBMS environment, multiple transactions may be executed simultaneously. This

leads to concurrency, where two or more transactions access the same data at the same time.

Concurrency control is the technique used to ensure that concurrent execution of transactions does
not lead to inconsistency of data.

2. Need for Concurrency Control

Without proper concurrency control, the database might face several problems:

Problem Type Description

Lost Update Two transactions overwrite each other’s updates.

Temporary Inconsistency One transaction sees uncommitted changes from another.

Uncommitted Dependency (Dirty

Reading data from a transaction that later fails.
Read)

Inconsistent Analysis (Phantom Aggregate functions show wrong results due to concurrent
Read) inserts/deletes.

3. Serializability in DBMS

What is Serializability?

Serializability is the gold standard for correctness in concurrency control. A schedule (order of
operations) is said to be serializable if its outcome is equivalent to some serial execution (i.e.,
transactions executed one after another, with no interleaving).

Types of Serializability:

a) Conflict Serializability:

• Based on reordering of non-conflicting operations.

• Two operations conflict if they:

o Belong to different transactions

o Access the same data item

o At least one is a write operation

Example:

text

CopyEdit

T1: R(A) W(A)

T2: R(A) W(A)

→ Not conflict-serializable due to overlapping access.

b) View Serializability:

• More relaxed than conflict-serializability.

• Ensures same final read/write result, even if operations are reordered.

4. Concurrency Control Techniques

There are multiple techniques to ensure serializability:

a) Lock-Based Protocols

• Use of locks (shared and exclusive) to control access.

• Two-Phase Locking (2PL):

o Growing phase: acquire all required locks

o Shrinking phase: release locks

Ensures: Conflict Serializability

Drawback: May cause deadlock.

b) Timestamp Ordering

• Assigns each transaction a unique timestamp.

• Ensures older transactions execute before newer ones on conflicting data.

Rules:

• If a transaction is too old to update/read a data item, it is rolled back.

c) Optimistic Concurrency Control

• Transactions execute without restriction.

• Validation phase checks for conflict before commit.

Phases:

1. Read

2. Validation

3. Write

Useful in low-conflict environments.

5. Deadlocks in Concurrency
A deadlock occurs when two or more transactions wait indefinitely for resources locked by each
other.

Deadlock Handling Techniques:

• Deadlock Detection and Recovery

• Deadlock Prevention (e.g., wait-die, wound-wait schemes)

• Timeouts (abort after waiting too long)

6. Example – Locking & Serializability

Schedule:

css

CopyEdit

T1: Lock(A), Read(A), Write(A), Unlock(A)

T2: Lock(B), Read(B), Write(B), Unlock(B)

Since T1 and T2 operate on different data items, schedule is conflict-serializable.

If both operate on same item:

css

CopyEdit

T1: Lock(A), Read(A), Write(A)

T2: Lock(A)... → Wait (blocked)

This follows 2PL and ensures serializability.

Conclusion:

Serializability ensures correctness in concurrent transactions, while concurrency control techniques

like locking, timestamp ordering, and optimistic methods are essential for enforcing it. Proper
concurrency management avoids conflicts, maintains data integrity, and ensures system reliability in
multi-user environments.
 1. Introduction to Lock-Based Concurrency Control

In a multi-user environment, transactions often access and modify the same data simultaneously.
Lock-based concurrency control is a method used in DBMS to synchronize concurrent transactions
by locking data items to prevent conflicts and ensure data consistency.

2. What is a Lock?

A lock is a mechanism used to restrict access to a data item. When a transaction holds a lock on a
data item, other transactions may be blocked from accessing that item depending on the type of
lock.

Types of Locks:

Type Symbol Use Case

Shared Lock S For reading data. Other shared locks allowed.

Exclusive Lock X For writing data. No other locks allowed.

3. Two-Phase Locking Protocol (2PL)

Two-Phase Locking (2PL) is a concurrency control protocol that ensures conflict serializability by
following two phases:

Phases of 2PL:

1. Growing Phase:

o A transaction acquires all required locks.

o No locks are released during this phase.

2. Shrinking Phase:

o A transaction releases all locks.

o No new locks can be acquired.

Example of 2PL:

css

CopyEdit

T1: Lock(A), Read(A), Lock(B), Write(B), Unlock(A), Unlock(B)

T2: Waits for B if T1 has it locked

This ensures a serial order equivalent, preserving data consistency.

4. Strict Two-Phase Locking (Strict 2PL)

In Strict 2PL, a transaction holds all its exclusive locks until commit or abort. This prevents cascading
rollbacks.

Benefits:

• Easy recovery.

• Ensures serializability and recoverability.

5. Deadlocks in Lock-Based Protocols

A deadlock occurs when two or more transactions are waiting for each other to release locks,
forming a cycle of dependencies.

Example of Deadlock:

css

CopyEdit

T1: Lock(A) → waits for Lock(B)

T2: Lock(B) → waits for Lock(A)

→ Deadlock

6. Deadlock Handling Methods

Method Description

Deadlock Prevention Preemptive strategies like Wait-Die or Wound-Wait based on timestamps.

Deadlock Detection Periodically check for cycles in wait-for graph.

Deadlock Recovery Abort one or more transactions involved in the deadlock.

Timeouts Abort if a lock is held for too long.

7. Advantages of Lock-Based Concurrency Control

• Simple and widely implemented

• Ensures conflict serializability

• Compatible with recovery techniques

8. Limitations

• May lead to deadlocks

• Can cause starvation

 • Requires careful management of lock granularity (row-level vs. table-level)

Conclusion:

Lock-based concurrency control, particularly through the Two-Phase Locking protocol, ensures
transaction isolation and serializability. However, it must be complemented with effective deadlock
handling strategies to ensure smooth and efficient database operations in concurrent environments.
1. Introduction to Concurrency Control

Concurrency control ensures the correct execution of simultaneous transactions in a multi-user

database system. While locking protocols are common, time stamping and optimistic methods are
alternative techniques that avoid or reduce locking.

2. Timestamp-Based Concurrency Control

Definition:

In Timestamp-Based Concurrency Control, each transaction is assigned a unique timestamp when it

starts. The timestamp determines the transaction’s order in the schedule, ensuring serializability.

Timestamps Used:

Each data item is associated with two timestamp values:

Name Description

read_TS(X) The largest timestamp of a transaction that successfully read data item X

write_TS(X) The largest timestamp of a transaction that successfully wrote to X

Working:

Assume transaction T has timestamp TS(T). When T tries to read or write a data item X, the following
rules are applied:

1. Read(X):

• If TS(T) < write_TS(X) → T is rolled back (because X has been modified by a newer
transaction).

• Else → T is allowed to read X, and read_TS(X) is updated to max(read_TS(X), TS(T)).

2. Write(X):

• If TS(T) < read_TS(X) or TS(T) < write_TS(X) → T is rolled back.

• Else → T is allowed to write X, and write_TS(X) is updated to TS(T).

Advantages:

• Ensures serializability.

• No need for locks → no deadlocks.

Disadvantages:

• Frequent rollbacks if many transactions conflict.

• Not suitable for high-contention systems.

 3. Optimistic Concurrency Control (OCC)

Definition:

Optimistic Concurrency Control assumes that conflicts are rare. Transactions proceed without
restrictions and only check for conflicts at the end, during the validation phase.

OCC Phases:

1. Read Phase:

o Transaction reads data from the database into local variables.

o No updates are made to the database.

2. Validation Phase:

o The system checks whether the transaction’s read/write operations conflict with
other concurrently running transactions.

o If no conflict, proceed to the next phase.

o If conflict, the transaction is rolled back.

3. Write Phase:

o If validation is successful, changes are applied to the database.

Example:

Assume two transactions T1 and T2:

• T1: reads and writes X and Y.

• T2: reads X during T1’s write phase.

Conflict detected → T2 rolled back.

Advantages:

• No locks, so no deadlocks.

• Suitable for read-heavy workloads with low conflict probability.

• High parallelism.

Disadvantages:

• Validation can be expensive.

• Wasted computation in case of rollback.

 4. Comparison Table

Feature Timestamp Ordering Optimistic Concurrency

Lock Usage No No

Deadlocks Not possible Not possible

Rollbacks Frequent in high conflict Only on validation failure

Best Suited For Low-contention workloads Read-heavy, low-write-conflict systems

Conclusion

Both timestamp-based and optimistic concurrency control methods offer alternatives to traditional
lock-based mechanisms. While they prevent deadlocks and enable high concurrency, they are best
used in environments with low data contention to avoid excessive rollbacks or validation failures.
 Database Recovery Management

(12 Marks)

1. Introduction to Database Recovery

Database Recovery refers to the techniques and procedures used to restore a database to a correct
state after a failure. Failures may occur due to hardware errors, software bugs, power outages,
transaction errors, or system crashes.

The goal is to ensure data consistency, atomicity, durability, and reliability.

2. Types of Failures

Failure Type Description

Transaction Due to logical errors (e.g., divide by zero) or system errors (e.g., deadlock
Failure timeout)

System Crash Software crashes or hardware failures (CPU, memory) causing the system to halt

Disk Failure Physical issues with storage like bad sectors or complete crash

Media Failure Entire disk is destroyed (e.g., fire or flood)

3. Recovery Concepts

a. ACID Properties

Recovery ensures:

• Atomicity: Transactions are all-or-nothing.

• Consistency: Database remains in a valid state.

• Isolation: Transactions execute independently.

• Durability: Once committed, changes persist.

b. Transaction States

Transactions go through states such as Active → Partially Committed → Committed or Failed →

Aborted.

4. Recovery Techniques

A. Log-Based Recovery

Logs are a sequence of records that maintain a history of transaction execution.

Each log contains:

sql

CopyEdit

<TID, Operation, Data item, Old value, New value>

• Write-Ahead Logging (WAL): Logs are written before actual data updates.

• If a system crashes, the log is used to:

o Redo committed transactions.

o Undo uncommitted transactions.

Example Log Entries:

sql

CopyEdit

<T1, START>

<T1, X, 20, 50>

<T1, COMMIT>

B. Deferred Update (Lazy Update)

• Updates are not written to the database until the transaction commits.

• If failure occurs before commit → no changes are made.

• If committed → redo the updates using the log.

C. Immediate Update

• Changes are applied immediately to the database.

• Requires both undo and redo mechanisms.

• If failure happens:

o Undo uncommitted transactions.

o Redo committed ones.

D. Shadow Paging

• Creates a copy of the database page (shadow page) before modification.

• Changes are made on a new page.

• If a transaction commits → pointer is updated.

 • If it fails → discard the new page.

Advantages: No logs required

Disadvantages: Expensive copying, not scalable

5. Checkpointing

• A checkpoint is a snapshot of the database at a particular time.

• Reduces recovery time by limiting how far back logs must be scanned.

Steps:

1. Suspend transactions temporarily.

2. Flush all buffers to disk.

3. Record a checkpoint log.

4. Resume transactions.

6. Recovery with Concurrent Transactions

Recovery systems must handle multiple transactions executing at once. This involves:

• Maintaining a transaction table

• Ensuring serializability

• Carefully ordering undo/redo operations

7. Recovery Algorithms

Algorithm Use Case

UNDO/REDO When updates occur before commit

UNDO only In deferred update

REDO only If only committed data is updated

ARIES Advanced Recovery using logs, checkpoints, and write-ahead logging

8. Conclusion

Database recovery management is essential to maintain the integrity and durability of data in the
presence of failures. Through mechanisms like logging, checkpointing, and shadow paging, DBMS
can recover reliably and ensure that transactions complete correctly or rollback cleanly.