[Concurrency Control in Databases]

VISVESVARAYA TECHNOLOGICAL UNIVERSITY

JNANA SANGAMA, BELAGAVI-590014

Report on

“Concurrency Control in Databases”

Submitted in the partial fulfillment of the requirement of IV Semester in
DATABASE MANAGEMENT SYSTEM -(BCS403)

Submitted By
Jayanth G - 1DT22IS066
Keerthan Raj B - 1DT22IS072
Kavya B A - 1DT22IS071
Lava Kumar - 1DT22IS081

Under the guidance of

[Link] B R

DEPARTMENT OF INFORMATION SCIENCE and ENGINEERING

DAYANANDA SAGAR ACADEMY OF TECHNOLOGY and MANAGEMENT
Udayapura, Kanakapura Main Road, Opp. Art of Living, Bengaluru-82

2023-2024
[Concurrency Control in Databases]

DAYANANDA SAGAR ACADEMY OF TECHNOLOGY AND MANAGEMENT

(Affiliated to Visvesvaraya Technological University, Belagavi and Approved by AICTE, New Delhi)
(Accrediated by NAAC with A+ Grade)
Udayapura, Kanakapura Main Road, Opp. Art of Living, Bengaluru-560082
(Accredited 3 years by NBA, New Delhi)
Department of Information Science and Engineering
CERTIFICATE

This is to certify that the case study work entitled “Concurrency Control in

Databases” is carried out by JAYANTH G (1DT22IS066),KAVYA B

A(1DT22IS071),KEERTHANRAJ B (1DT22IS072), Lava kumar (1DT22IS081)
in the partial fulfillment for the requirement of IV semester DBMS in Information
Science and Engineering of the Visvesvaraya Technological University, Belagavi,
during the year 2023-2024. It is certified that all corrections/suggestions indicated for the
internal assessment have been incorporated in the report. This report has been approved
as it satisfies the academic requirements in respect of case study work.

GAGANA B R Dr. Nandini Prasad K S

Assistant Professor Dean-Foreign Affairs
DBMS &HOD
DSATM,Bangalore Dept. of ISE
DSATM, Bangalore
[Concurrency Control in Databases]

TABLE OF CONTENTS

[Link] TITLE PAGE NO

1 Introduction 1
2 Two-phase locking techniques for Concurrency control 2
3 Concurrency control based on Timestamp ordering 3
4 Multiversion Concurrency control techniques 4
5 Validation Concurrency control techniques 5

6 Conclusion 6
[Concurrency Control in Databases]

Concurrency Control in Databases

INTRODUCTION:

Concurrency control in databases is an essential component of database management

systems (DBMS) designed to handle simultaneous operations without compromising
data integrity and consistency. When multiple users or applications access and modify
the database at the same time, the system must ensure that these concurrent
transactions do not interfere with each other in a way that leads to incorrect or
inconsistent data. Concurrency control mechanisms achieve this by employing various
strategies such as locking protocols, timestamp ordering, and multiversion
concurrency control (MVCC). Locking protocols, including Two-Phase Locking
(2PL), ensure that transactions acquire and release locks in a manner that prevents
conflicts and maintains serializability. Timestamp ordering assigns unique timestamps
to each transaction to maintain a consistent execution order. MVCC, on the other
hand, provides each transaction with a "snapshot" of the database, allowing read
operations to occur without locking and reducing contention. These techniques
collectively uphold the ACID properties (Atomicity, Consistency, Isolation,
Durability) of transactions, ensuring that even in a high-concurrency environment, the
database remains reliable and performs efficiently. As databases continue to grow in
complexity and scale, robust concurrency control mechanisms are increasingly vital
for maintaining the seamless operation and accuracy of data in multi-user
environment

Dept. of ISE,DSATM 2023-24 1

[Concurrency Control in Databases]

Two-phase locking techniques for Concurrency control

1. Growing Phase:

o During this phase, a transaction may acquire locks but may not release any locks.
o This means that as the transaction proceeds, it requests the necessary locks on the data items
it wants to read or write.
o The transaction can obtain shared (read) or exclusive (write) locks depending on the
operation it needs to perform.
o The growing phase continues until the transaction has acquired all the locks it needs.
2. Shrinking Phase:
o In the shrinking phase, the transaction releases the locks it has acquired but cannot acquire
any new locks.
o Once a transaction starts releasing locks, it cannot request any more locks.
o This phase begins when the transaction releases its first lock and continues until all locks held
by the transaction are released.

Types of 2PL

1. Strict Two-Phase Locking (Strict 2PL):

o In this variation, a transaction holds all its exclusive (write) locks until it commits or aborts.
o This ensures that no other transaction can read or write to the data items being modified by
the transaction until it completes.
o Strict 2PL provides a higher level of isolation and is widely used in practice.
2. Rigorous Two-Phase Locking (Rigorous 2PL):
o A transaction holds all its locks (both shared and exclusive) until it commits or aborts.
o This ensures that no other transaction can read or write to any data items being accessed by
the transaction until it completes.
o Rigorous 2PL provides the highest level of isolation.
3. Conservative Two-Phase Locking (Conservative 2PL):
o Also known as Static 2PL, this variation requires a transaction to lock all the data items it
needs before it begins execution.
o If it cannot acquire all the required locks at the beginning, it waits until all locks are
available.
o This approach prevents deadlocks but can lead to delays if locks are not readily available.

Benefits of Two-Phase Locking

 Serializability: 2PL ensures that the schedule of transactions is serializable, meaning that the
outcome is the same as if the transactions were executed one after the other in some order.
 Isolation: By controlling the acquisition and release of locks, 2PL provides the necessary isolation
between transactions, preventing conflicts and ensuring data consistency.

Drawbacks of Two-Phase Locking

 Deadlocks: Since transactions may need to wait for locks held by other transactions, deadlocks can
occur. Deadlock detection and resolution mechanisms are required to handle such situations.

Reduced Concurrency: Holding locks for the duration of a transaction can reduce the level of
concurrency and throughput, especially in high-transaction environments.

Dept. of ISE,DSATM 2023-24 2

[Concurrency Control in Databases]

Timestamp Ordering Concurrency Control

Key Concepts

1. Timestamp:
o A unique identifier, often based on the system clock, assigned to each transaction when it
starts.
o It represents the transaction's start time and is used to order transactions chronologically.
2. Read Timestamp (RTS):
o For each data item, the read timestamp is the largest timestamp of any transaction that has
successfully read that item.
3. Write Timestamp (WTS):
o For each data item, the write timestamp is the largest timestamp of any transaction that has
successfully written to that item.

Basic Rules

1. Read Operation:
o If a transaction TiT_iTi with timestamp TS(Ti)TS(T_i)TS(Ti) requests to read a data item
XXX:
 If TS(Ti)TS(T_i)TS(Ti) < WTS(X), the read is rejected, and TiT_iTi is rolled back.
This is because TiT_iTi is trying to read a value that has already been overwritten by
a newer transaction.
 Otherwise, the read is allowed, and RTS(X) is updated to
max⁡(RTS(X),TS(Ti))\max(RTS(X), TS(T_i))max(RTS(X),TS(Ti)).
2. Write Operation:
o If a transaction TiT_iTi with timestamp TS(Ti)TS(T_i)TS(Ti) requests to write a data item
XXX:
 If TS(Ti)TS(T_i)TS(Ti) < RTS(X), the write is rejected, and TiT_iTi is rolled back.
This ensures TiT_iTi does not overwrite a value that has been read by a newer
transaction.
 If TS(Ti)TS(T_i)TS(Ti) < WTS(X), the write is rejected, and TiT_iTi is rolled back.
This prevents TiT_iTi from overwriting a value written by a newer transaction.
 Otherwise, the write is allowed, and WTS(X) is updated to TS(Ti)TS(T_i)TS(Ti).

Advantages

 No Deadlocks: Since transactions are ordered by timestamps, there are no circular wait conditions,
eliminating deadlocks.
 High Concurrency: Transactions can often proceed without waiting, especially if their timestamps
do not conflict with those of other transactions.

Disadvantages

 Transaction Rollbacks: Transactions can be frequently rolled back if there are many conflicting
operations, leading to performance issues.
 Timestamp Management: Maintaining and comparing timestamps can introduce overhead

Dept. of ISE,DSATM 2023-24 3

[Concurrency Control in Databases]

Multiversion Concurrency control

techniques
1. Versioning:

o MVCC maintains multiple versions of each data item that is modified by transactions.
o Each version corresponds to a specific point in time when the data item was modified.
o Versions are typically identified by timestamps, transaction identifiers, or sequence numbers.
2. Reads and Writes:
o Reads: Transactions read from a consistent snapshot of the database, typically based
on the transaction's start time or a specific timestamp.
 Read operations do not block write operations, allowing transactions to read data
without waiting for exclusive locks.
 Transactions read the most recent committed version of each data item that is valid
based on the transaction's snapshot.
o Writes: Transactions create a new version of a data item when performing updates.
 Updates are typically performed on private copies of data items (copy-on-write
strategy) to avoid conflicting with concurrent transactions reading the same data
item.

Techniques and Variants

1. Snapshot Isolation:
o Each transaction executes based on a consistent snapshot of the database taken at the
beginning of the transaction.
o Ensures that a transaction sees a consistent view of the database, even if data is modified by
other transactions during its execution.
2. Multi-Version Timestamp Ordering:
o Combines MVCC with timestamp ordering to ensure serializability.
o Transactions are ordered based on timestamps to avoid conflicts, and MVCC is used to
manage versions of data items.
3. Visibility Rules:
o Transactions are allowed to see only those versions of data items that were committed before
the transaction started (based on its snapshot time).
o Ensures that transactions do not read or write uncommitted data, maintaining database
consistency.

Advantages of MVCC

 Improved Concurrency: Allows for higher levels of concurrency compared to traditional locking
mechanisms.
 Read Consistency: Provides consistent reads without blocking write operations, enhancing
performance.
 No Deadlocks: Since read operations do not block write operations, MVCC eliminates deadlock
scenarios common in locking mechanisms.

Disadvantages of MVCC

 Increased Storage Overhead: Maintaining multiple versions of data items requires additional
storage space.
 Complexity: Implementing and managing MVCC systems can be more complex than traditional
locking mechanisms.

Dept. of ISE,DSATM 2023-24 4

[Concurrency Control in Databases]

Validation Concurrency control techniques

1. Optimistic Approach:
o Transactions are allowed to proceed without acquiring locks or checking for conflicts during
their execution phase.
o This approach assumes that conflicts between transactions (e.g., concurrent updates to the
same data item) are rare.
2. Validation Phase:
o Transactions are validated before they commit to ensure that their execution did not conflict
with other transactions.
o During validation, the system checks if any conflicts occurred. If conflicts are detected,
appropriate actions such as transaction rollback or retry may be taken.
3. Versioning or Timestamps:
o Versions of data items or timestamps are often used to track changes made by transactions.
o These versions or timestamps help determine if a transaction’s changes conflict with changes
made by other transactions.

Techniques and Strategies

1. Timestamp-Based Validation:
o Each transaction is assigned a unique timestamp or version number.
o Before committing, the transaction’s timestamp or version is compared with the timestamps
or versions of other transactions that have accessed the same data items.
o Conflicts are detected if two transactions have overlapping time intervals or versions.
2. Conflict Detection:
o Common conflicts include:
 Read-Write Conflict: A transaction reads a data item that has been modified by
another transaction before it commits.
 Write-Write Conflict: Two transactions attempt to write to the same data item
concurrently.
3. Resolution Strategies:
o If conflicts are detected during validation, the database system may employ strategies such
as:
 Transaction Rollback: Rollback conflicting transactions and allow them to retry.
 Wait-and-Retry: Delay committing conflicting transactions and retry them after
other transactions have completed.
 Deadlock Detection: Detect and resolve deadlocks, if any occur during validation.

Advantages of Validation Concurrency Control

 High Concurrency: Transactions can proceed concurrently without blocking each other, leading to
better overall system throughput.
 No Locking Overhead: Avoids the overhead of acquiring, managing, and releasing locks, which can
improve performance in systems with low contention.
 Reduced Deadlock Risk: By avoiding locks, the risk of deadlock situations is minimized.

Disadvantages of Validation Concurrency Control

 Increased Rollback Rate: Transactions may need to be rolled back and retried more frequently if
conflicts occur, potentially impacting performance.
 Validation Overhead: The validation phase adds overhead to transaction processing, especially in
systems with h

Dept. of ISE,DSATM 2023-24 5

[Concurrency Control in Databases]

CONCLUSION:

Concurrency control in databases stands as a cornerstone of ensuring the integrity and

reliability of data transactions in environments where multiple users concurrently access and
modify the database. By employing sophisticated mechanisms such as locking protocols,
timestamp-based ordering, multiversion concurrency control (MVCC), and optimistic
validation techniques, database systems manage the intricate dance of allowing transactions to
execute simultaneously while preserving consistency and isolation. These methods are
designed to prevent scenarios like data inconsistency or lost updates that can arise when
transactions overlap.
Locking mechanisms, such as two-phase locking (2PL), enforce strict rules on how
transactions acquire and release locks on data items, ensuring that conflicts are managed and
transactions proceed in a serialized manner when necessary. Timestamp-based ordering
assigns unique timestamps to transactions, enabling a systematic approach to scheduling and
ensuring that each transaction sees a consistent view of the database at its start time.
MVCC takes a different approach by maintaining multiple versions of data items, allowing
transactions to read a consistent snapshot of the database without blocking each other, thus
enhancing concurrency and performance. On the other hand, validation-based techniques, like
optimistic concurrency control, let transactions proceed without immediate lock acquisition,
validating them only upon commit to detect and resolve conflicts dynamically.
While these techniques provide robust solutions to concurrency challenges, they also
introduce complexities such as increased storage requirements for maintaining multiple
versions or the overhead of managing transaction rollbacks in case of conflicts. Nonetheless,
their implementation is crucial for upholding the ACID properties—Atomicity, Consistency,
Isolation, and Durability—ensuring that database operations remain reliable and data integrity
is preserved even in demanding and dynamic computing environments.

Dept. of ISE,DSATM 2023-24 6