---

Concurrent Executions in DBMS

---

Concurrent execution refers to the simultaneous execution of more than one transaction. This is a common scenario in multi-user database environments where many users or applications might be accessing or modifying the database at the same time. Concurrent execution is crucial for achieving high throughput and efficient resource utilization. However, it introduces the potential for conflicts and data inconsistencies.

Advantages of Concurrent Execution

1. Increased System Throughput: Multiple transactions can be in progress at the same time, but at different stages
2. Maximized Processor Utilization: If one transaction is waiting for I/O operations, another transaction can utilize the processor.
3. Decreased Wait Time: Transactions no longer have to wait for other long transactions to complete.
4. Improved Transaction Response Time: Transactions get processed faster because they can be executed in parallel.

Potential Problems with Concurrent Execution

1. Lost Update Problem (Write-Write conflict):

One transaction's updates could be overwritten by another.

Examples:

   T1     |    T2
----------|-----------
Read(A)   |
A = A+50  |
          |  Read(A)
          |  A = A+100
Write(A)  |
          |  Write(A)

Result: T1's updates are lost.

2.Temporary Inconsistency or Dirty Read Problem (Write-Read conflict):

One transaction might read an inconsistent state of data that's being updated by another.

Examples:

   T1             |    T2
------------------|-----------
Read(A)           |
A = A+50          |
Write(A)          |
                  |  Read(A)
                  |  A = A+100
                  |  Write(A)
Read(A)(rollbacks)|
                  |  commit

Result: T2 has a "dirty" value, that was never committed in T1 and doesn't actually exist in the database.

3. Unrepeatable Read Problem (Read-Write conflict):

when a single transaction reads the same row multiple times and observes different values each time. This occurs because another concurrent transaction has modified the row between the two reads.

Examples:

   T1     |    T2
----------|----------
Read(A)   |
          |  Read(A)
          |  A = A+100
          |  Write(A)
Read(A)   |

Result: Within the same transaction, T1 has read two different values for the same data item. This inconsistency is the unrepeatable read.

To manage concurrent execution and ensure the consistency and reliability of the database, DBMSs use concurrency control techniques. These typically include locking mechanisms, timestamps, optimistic concurrency control, and serializability checks.

---

Next Topic :Serializability in DBMS