🎓 BCA 4th Semester

📊 Database Management Systems

🔄 UNIT – 4
Transaction Management and Concurrency Control

📋 Table of Contents
1. Concept of Transaction 💡
2. ACID Properties 🔒

3. Schedules – Serial and Serializable 📅

4. Concurrency Control Techniques ⚡

5. Locking Mechanisms 🔐
6. Timestamping ⏰
7. Deadlock Handling ⚠️

💡 1. Concept of Transaction
A transaction is a fundamental concept in database management systems
that represents a logical unit of work performed on a database. It is a
collection of operations that are executed as a single, indivisible unit.

🎯 Key Characteristics of Transactions:

 Atomicity: All operations within a transaction must be completed
successfully, or none at all

Unit of Work: Represents a complete business operation

State Transformation: Changes the database from one consistent
state to another
Isolation: Transactions execute independently without interference

Durability: Once committed, changes are permanent

🔄 Transaction States:
Transactions can exist in various states during their lifecycle:

State Description Action

Operations are being

Active Transaction is executing
performed

Partially Waiting for commit

Final statement executed
Committed confirmation

Transaction completed
Committed Changes are permanent
successfully

Normal execution cannot

Failed System detected error
proceed

Database restored to initial

Aborted Transaction rolled back
state
 

🚀 Transaction Operations:
BEGIN TRANSACTION: Marks the start of a transaction

COMMIT: Makes all changes permanent

 ROLLBACK: Undoes all changes made during the transaction

SAVEPOINT: Creates intermediate checkpoints within a transaction

🔒 2. ACID Properties
The ACID properties are fundamental principles that ensure database
transactions are processed reliably and maintain data integrity.

🔸 A - Atomicity ⚛️
Definition: Transactions are "all-or-nothing" operations.

Key Features:

Either all operations in a transaction are executed, or none are

executed
No partial execution is allowed

If any operation fails, the entire transaction is rolled back

Ensures database consistency by preventing incomplete operations

Real-world Example: Bank transfer operations where money must be

debited from one account and credited to another completely, or not at
all.

🔸 C - Consistency ✅
Definition: Transactions must transform the database from one valid state
to another valid state.

Key Features:
 Database integrity constraints must be maintained
Business rules and validation checks are enforced

Data relationships remain valid after transaction completion

Prevents data corruption and maintains logical correctness

Important Aspects:

Referential integrity is preserved

Domain constraints are satisfied

User-defined constraints are maintained

🔸 I - Isolation 🏝️
Definition: Concurrent transactions must execute independently without
interfering with each other.

Key Features:

Multiple transactions can run simultaneously

Each transaction appears to execute in isolation

Intermediate results are not visible to other transactions

Prevents data inconsistency due to concurrent access

Isolation Levels:

Read Uncommitted: Lowest isolation level

Read Committed: Prevents dirty reads

Repeatable Read: Prevents non-repeatable reads

Serializable: Highest isolation level

🔸 D - Durability 💪
Definition: Once a transaction is committed, its effects must persist
permanently.

Key Features:

Committed changes survive system failures

Data is stored in non-volatile storage

Recovery mechanisms ensure data persistence

Backup and logging mechanisms support durability

Implementation Methods:

Write-ahead logging

Database checkpointing

Redundant storage systems

Transaction log maintenance

📅 3. Schedules – Serial and Serializable

A schedule defines the chronological order in which operations of
concurrent transactions are executed.

🔄 Serial Schedules
Definition: A schedule where transactions are executed completely one
after another without any interleaving.

Characteristics:
 No concurrency between transactions

One transaction completes entirely before another begins

Always produces consistent results

Provides maximum isolation but minimum performance

Advantages:

✅ Guarantees consistency
✅ Easy to understand and implement
✅ No concurrency control overhead
Disadvantages:

❌ Poor resource utilization

❌ Reduced system throughput
❌ Increased response time

🔀 Serializable Schedules
Definition: A schedule that produces the same result as some serial
execution of the same transactions.

Key Properties:

Operations of different transactions can be interleaved

Final result is equivalent to some serial schedule

Maintains consistency while allowing concurrency

Optimal balance between performance and correctness

Types of Serializability:
 Type Description Checking Method

Conflict No conflicting operations are

Conflict graph analysis
Serializable reordered

View equivalence
View Serializable Same read-write dependencies
testing
 

🎯 Benefits of Serializable Schedules:

Performance: Better resource utilization through concurrency

Consistency: Maintains ACID properties

Flexibility: Allows multiple execution orders

Scalability: Supports multiple concurrent users

⚡ 4. Concurrency Control Techniques

Concurrency control mechanisms ensure that concurrent transactions
execute in a way that maintains database consistency and isolation.

🎯 Objectives of Concurrency Control:

Prevent Interference: Stop transactions from interfering with each
other

Maintain Consistency: Ensure database remains in valid state

Maximize Performance: Allow maximum safe concurrency level

Ensure Serializability: Produce serializable schedules

🚨 Concurrency Problems:
Lost Update Problem:

Two transactions modify the same data item

One update overwrites another

Results in data loss

Dirty Read Problem:

Reading uncommitted data from another transaction

May lead to inconsistent results

Transaction may later be rolled back

Non-repeatable Read:

Same query returns different results within a transaction

Another transaction modifies data between reads
Affects transaction consistency

Phantom Read:

New rows appear between successive reads

Another transaction inserts matching records

Changes the result set unexpectedly

🛠️ Main Concurrency Control Approaches:

1. Lock-based Protocols 🔐
2. Timestamp-based Protocols ⏰

3. Validation-based Protocols ✅
 4. Multiversion Concurrency Control 🔄

🔐 5. Locking Mechanisms
Locking is a concurrency control technique that uses locks to control
access to database items by concurrent transactions.

🔑 Types of Locks:
Shared Lock (S-Lock):

Purpose: Read access to data item

Compatibility: Multiple shared locks allowed

Restriction: No exclusive locks permitted

Usage: SELECT operations

Exclusive Lock (X-Lock):

Purpose: Write access to data item

Compatibility: No other locks allowed
Restriction: Complete isolation required

Usage: UPDATE, DELETE, INSERT operations

📊 Lock Compatibility Matrix:

Current Lock Shared (S) Exclusive (X)

Shared (S) ✅ Compatible ❌ Not Compatible

Exclusive (X) ❌ Not Compatible ❌ Not Compatible
 
🔒 Locking Protocols:
Basic Two-Phase Locking (2PL):

Growing Phase: Acquire locks, cannot release any locks

Shrinking Phase: Release locks, cannot acquire new locks

Guarantee: Ensures serializability

Limitation: May cause deadlocks

Strict Two-Phase Locking:

All exclusive locks held until transaction commits

Prevents cascading rollbacks

More restrictive than basic 2PL

Widely used in commercial systems

Rigorous Two-Phase Locking:

All locks (shared and exclusive) held until commit

Maximum isolation but reduced concurrency

Prevents all types of cascading problems

Used in high-consistency requirements

⚙️ Lock Granularity Levels:

 Granularity Description Concurrency Overhead

Database Entire database locked Very Low Very Low

Table Complete table locked Low Low

Page Database page locked Medium Medium

Row Individual record locked High High

Field Specific column locked Very High Very High

 

⏰ 6. Timestamping
Timestamp-based concurrency control assigns unique timestamps to
transactions and uses these to determine the execution order.

🕐 Timestamp Assignment Methods:

System Clock Timestamps:

Use system time when transaction begins

Simple and intuitive approach

May have resolution limitations

Suitable for single-site systems

Logical Counter Timestamps:

Incremental counter for each new transaction

Guarantees uniqueness

Independent of system clock

Preferred for distributed systems

🔄 Timestamp Ordering Protocol:
Basic Timestamp Ordering:

Read Rule: Transaction can read if its timestamp > write timestamp of
data

Write Rule: Transaction can write if its timestamp > both read and
write timestamps
Conflict Resolution: Abort and restart conflicting transactions

Thomas Write Rule:

Modification of basic timestamp ordering

Ignores obsolete write operations

Improves performance by reducing aborts

Maintains serializability

📈 Advantages of Timestamping:
Deadlock-Free: No circular wait conditions

Starvation-Free: Older transactions get priority

Simple Implementation: Clear ordering rules

Distributed Systems: Works well across multiple sites

📉 Disadvantages of Timestamping:
High Abort Rate: Many transactions may be restarted

Cascading Rollbacks: May cause chain reactions

Timestamp Overhead: Additional storage and computation
 Poor Performance: Under high contention scenarios

⚠️ 7. Deadlock Handling
A deadlock occurs when two or more transactions are waiting indefinitely
for each other to release locks, creating a circular wait condition.

🔄 Deadlock Characteristics:
Necessary Conditions for Deadlock:

1. Mutual Exclusion: Resources cannot be shared

2. Hold and Wait: Holding resources while waiting for others

3. No Preemption: Resources cannot be forcibly taken

4. Circular Wait: Circular chain of waiting transactions

🛡️ Deadlock Prevention Strategies:

Wait-Die Scheme:

Rule: Older transaction waits, younger dies

Advantage: Prevents deadlocks

Disadvantage: May cause unnecessary aborts

Wound-Wait Scheme:

Rule: Older transaction wounds younger, younger waits

Advantage: Reduces unnecessary waiting

Disadvantage: More complex implementation
🔍 Deadlock Detection Methods:
Wait-for Graph:

Construction: Nodes represent transactions, edges represent waits

Detection: Look for cycles in the graph

Frequency: Periodic or triggered detection

Resolution: Abort one transaction in the cycle

Timeout-Based Detection:

Method: Set maximum wait time for lock requests

Assumption: Long waits indicate potential deadlocks

Action: Abort transaction after timeout

Limitation: May abort non-deadlocked transactions

🏥 Deadlock Recovery Strategies:

Strategy Method Criteria Impact

Choose transaction Minimum Restart selected

Victim Selection
to abort cost/work done transaction

Undo transaction Complete or Restore database

Rollback
operations partial state

Starvation Limit restart Maximum retry Ensure eventual

Prevention attempts count completion
 

🎯 Best Practices for Deadlock Management:

Lock Ordering: Acquire locks in predefined order
 Lock Timeout: Set reasonable timeout values

Transaction Design: Keep transactions short and simple

Resource Planning: Minimize resource contention

Monitoring: Regular deadlock detection and analysis

📚 Summary
Transaction Management and Concurrency Control are critical components
of database systems that ensure:

🔹 Data Integrity through ACID properties 🔹 Concurrent Access

without compromising consistency
🔹 System Performance through optimized resource utilization 🔹
Reliability through proper transaction handling 🔹 Scalability to support
multiple users simultaneously

Understanding these concepts is essential for designing robust database

applications and maintaining system performance in multi-user
environments.

🎯 Key Takeaways
✅ Transactions ensure atomicity, consistency, isolation, and durability
✅ Serializability maintains consistency while allowing concurrency
✅ Locking and timestamping are primary concurrency control
mechanisms
✅ Deadlock prevention and detection are crucial for system reliability
✅ Proper concurrency control balances performance with data integrity
End of Unit 4 - Transaction Management and Concurrency Control