Question

1. Describe the types of problem that can possibly occur in a multi-user environment when concurrent access to the database is allowed.                                               

Answer 1

Solution:

In a multi user database environment, there are many users who wants to access the data at the same time.

In order allow the users to access the data from database at the same time, the system allows data concurrency.

Data concurrency allows multiple number of users to access the data at the same time from database.

Consider a relational database where there are many transactions present in the database.

A transaction can be understood as set of operations that should be performed at a time. A transaction should be performed completely or left untouched.

when multiple transactions are accessed by multiple users at the same where there are some dependencies in the data, the concurrency has some problems associated with it.

Problems:

The following are some of the problems of concurrency mechanisms in database.

• Lost Update and dirty write
• Dirty Read
• Phantom read problem
• Incorrect Retrievals
• Access to uncommit data

Explanation:

Lost Update and dirty write:

When two applications use the same data from a record from database, the same data is used by two applications and it calculates new values for one of the columns.

The calculated values are updated in database.

These two write operations to database are performed by different applications.

This leads to incorrect write of data to database. This is called as Dirty write.

Consider transactions T1 and T2. These uses data A from database.

T1 performs calculations on A and before it updates to database, T2 accesses it and performs its own opertions on old value of A.

Now when T1 and T2 write back the value of A, it leads to dirty write and lost update problem

Dirty Read:

The Dirty read problem occurs generally when the application reads the data from database that is being modified by some other application and is uncommitted.

This read operation is called Dirty Read operation.

Phantom read:

The phantom read is the situation when different applications access the same data and one application deletes the data from database and the other application can not read data because it is deleted by other one.

This is called as phantom read problem

Incorrect Retrievals:

Consider the application that performs the operation of summerization on database.

Another application accesses the same data and keeps modifying it.

Due to this concurrent access to data, the retrieval of data summerization results are incorrect.

Access to uncommit data:

This is similar to dirty read operation.

When two applications access same data, when one application is using a uncommitted data then the changes that this application makes are invalid.

The same data might be updated by other application and might lead to inconsistency.

These are the various problems of concurrency in databases.