Why Concurrency Control Is Needed:
Concurrency control is the process of managing simultaneous execution of
transactions (managing simultaneous operations on the database) such as
queries, updates, inserts, deletes and so on in a multiprocessing database
system without having them interfere with one another.
[• Prevents
interference when two or more users are accessing database simultaneously and
at least one is updating data.
• Although two
transactions may be correct in themselves, interleaving of operations may
produce an incorrect result.]
When
multiple transactions execute concurrently in an uncontrolled or unrestricted
manner, then it might lead to several problems. Such problems are called
as concurrency
problems.
For example:
In airline reservations database in which a record is stored for each airline
flight. Each record includes the number of reserved seats on that flight as a named (uniquely identifiable) data item,
among other information.

Figure
21.2(b) shows a simpler transaction T2 that just reserves M seats
on the first flight (X) referenced in transaction T1.
To
simplify our example, we do not show additional portions of the transactions,
such as checking whether a flight has enough seats available before reserving
additional seats.
When
a database access program is written, it has the flight number, flight date,
and the number of seats to be booked as parameters; hence, the same program can
be used to execute many different transactions, each with a different flight
number, date, and number of seats to be booked.
For
concurrency control purposes, a transaction is a particular execution of a
program on a specific date, flight, and number of seats.
In
Figure 21.2(a) and (b), the transactions T1 and T2 are specific executions of
the programs that refer to the specific flights whose numbers of seats are
stored in data items X and Y in the database.
Concurrency problems are classified into four types. They are
1.
Dirty Read Problem
2.
Unrepeatable Read
Problem
3.
Lost Update
Problem
4.
Phantom read
Problem
This property of DBMS allows many transactions to access the same database at the same time without interfering with each other. The primary goal of concurrency is to ensure the atomicity of the execution of transactions in a multi-user database environment. Concurrency controls mechanisms attempt to interleave (parallel) READ and WRITE operations of multiple transactions so that the interleaved execution yields results that are identical to the results of a serial schedule execution.
Lost Update Problem:
This problem occurs when two transactions that access the same database items
have their operations interleaved in a way that makes the value of some
database items incorrect.
In other
words, if transactions T1 and T2 both read a record and then update it, the
effects of the first update will be overwritten by the second update.
Lost update problem
occurs when two or more transactions are updating same data items
simultaneously. The transaction (among the concurrent transactions) that
commits at last (lately) will decide the final value of that particular data
item.
Suppose that transactions T1 and T2 are submitted at approximately the same time, and suppose that their operations are interleaved as shown in Figure 21.3(a); then the final value of item X is incorrect because T2 reads the value of X before T1 changes it in the database, and hence the updated value resulting from T1 is lost.
Temporary Update (or
dirty Read) Problem: This problem occurs
when one transaction updates a database item and then the transaction fails for
some reason (see Section 21.1.4).Meanwhile, the updated item is accessed (read)
by another transaction before it is changed back to its original value.
When one
transaction can see intermediate results of another transaction before it has
committed.
Figure 21.3(b) shows an example where T1 updates item X and then fails before completion, so the system must change X back to its original value. Before it can do so, however, transaction T2 reads the temporary value of X, which will not be recorded permanently in the database because of the failure of T1. The value of item X that is read by T2 is called dirty data because it has been created by a transaction that has not completed and committed yet; hence, this problem is also known as the dirty read problem.
Incorrect Summary /
Inconsistent Analysis problem:
If one transaction is calculating an
aggregate summary function on a number of database items while other
transactions are updating some of these items, the aggregate function may
calculate some values before they are updated and others after they are updated.
For
example, suppose that a transaction T3 is calculating the total number of
reservations on all the flights; meanwhile, transaction T1 is executing. If the
interleaving of operations shown in Figure 21.3(c) occurs, the result of T3
will be off by an amount N because T3 reads the value of X after N seats have
been subtracted from it but reads the value of Y before those N seats have been
added to it.
Unrepeatable Read
Problem: Another problem that may
occur is called unrepeatable read, where a transaction T reads the same item
twice and the item is changed by another transaction T between the two reads.
Hence, T receives different values for its two reads of the same item. This may
occur, for example, if during an airline reservation transaction, a customer
inquires about seat availability on several flights. When the customer decides
on a particular flight, the transaction then reads the number of seats on that
flight a second time before completing the reservation, and it may end up
reading a different value for the item.
Comments
Post a Comment