DATABASE MANAGEMENT SYSTEM

CONCURRENT EXECUTION IN DBMS

In a multi-user system, multiple users can access and manipulate the same database simultaneously, a process known as concurrent execution. This capability is fundamental to modern database systems, enabling efficient use of resources and improved system performance. However, concurrent execution also introduces challenges that must be managed to ensure data integrity and consistency.

Concurrent execution in Database Management Systems (DBMS) refers to multiple database transactions being processed simultaneously. This is crucial for improving performance and ensuring efficient utilization of system resources.

Problems with Concurrent Execution

In a database transaction, the two main operations are READ and WRITE operations..While concurrent execution offers numerous benefits, it also introduces several challenges, mainly due to the interactions between concurrent transactions.

1. Lost Updates

Occurs when two transactions read the same data and update it, but one update is overwritten by the other, leading to lost data changes.

Example:

• Initial State: Account balance X = $100.
• Transaction T1:
  • Reads X = $100.
  • Updates X to $150.
• Transaction T2:
  • Reads X = $100.
  • Updates X to $200.
• Outcome: T2 commits last, so the final value of X is $200. The update made by T1 (to $150) is lost.

2. Dirty Reads

Occurs when a transaction reads data that has been written by another transaction that has not yet committed. If the other transaction rolls back, the data read by the first transaction becomes invalid.

Example:

• Initial State: Account balance X = $100.
• Transaction T1:
  • Updates X to $150 but does not commit.
• Transaction T2:
  • Reads X as $150.
• Transaction T1: Rolls back, reverting X to $100.
• Outcome: T2 has read an invalid (dirty) value of $150.

3. Inconsistent Retrievals (Non-repeatable Reads)

Occurs when a transaction reads the same data multiple times and gets different results because other transactions are concurrently updating the data.

Example:

• Initial State: Account balance X = $100.
• Transaction T1:
  • Reads X = $100.
• Transaction T2:
  • Updates X to $200 and commits.
• Transaction T1:
  • Reads X again and gets $200.
• Outcome: T1 gets inconsistent results for the same query, which can lead to erroneous decisions.

EXAMPLE: ONLINE BANKING SYSTEM

Consider an online banking system where multiple users can access and modify their account balances simultaneously. This scenario is common in the real world and illustrates the potential problems of concurrent execution.

Scenario: Online Banking System

1. Lost Updates

Situation: Two customers, Alice and Bob, try to transfer money from a joint savings account at the same time.

• Initial Balance: $1000

Transactions:

• Transaction T1 (Alice):
  1. Reads balance: $1000
  2. Deducts $200 for transfer: New balance should be $800
• Transaction T2 (Bob):
  1. Reads balance: $1000
  2. Deducts $300 for transfer: New balance should be $700

Execution:

1. T1 reads balance ($1000)
2. T2 reads balance ($1000)
3. T1 deducts $200 and sets balance to $800
4. T2 deducts $300 and sets balance to $700
5. Final balance recorded: $700

Problem: The update by Alice is lost because Bob's transaction overwrites it. The final balance should be $500 (i.e., $1000 - $200 - $300), but it ends up being $700.

2. Dirty Reads

Situation: A bank manager is generating a report on account balances while another transaction is updating balances.

Transactions:

• Transaction T1 (Manager):
  1. Reads balance: $1000
• Transaction T2 (System):
  1. Updates balance to $1200 but does not commit immediately

Execution:

1. T2 updates balance to $1200
2. T1 reads balance as $1200 (dirty read)
3. T2 rolls back the update, reverting balance to $1000

Problem: The manager's report shows an incorrect balance of $1200 because it was based on uncommitted data.

3. Inconsistent Retrievals (Non-repeatable Reads)

Situation: A customer is checking their account balance and recent transactions.

Transactions:

• Transaction T1 (Customer):
  1. Reads balance: $1000
  2. Reads recent transactions
• Transaction T2 (System):
  1. Processes a withdrawal: $300

Execution:

1. T1 reads balance: $1000
2. T2 processes withdrawal and updates balance to $700
3. T1 reads balance again: $700

Problem: The customer sees inconsistent data: the initial balance check shows $1000, but a subsequent check shows $700, causing confusion.