Database Management System

Classifying a database system according to the

number of users who can use the system concurrently
• Single-User DBMS:
If at most one user at a time can use the system,
mostly restricted to personal computer systems
• Multiuser Systems:
If many users can use the system— and hence
access the database—concurrently.
For example, an airline reservations system is used by
hundreds of users and travel agents concurrently.
Database systems used in banks, insurance agencies,
stock exchanges, supermarkets, and many other
applications are multiuser systems. In these systems,
hundreds or thousands of users are typically operating on
the data- base by submitting transactions concurrently to
the system.
 What is a transaction in computing?
• In computing, a transaction is a set of related
tasks treated as a single action.
• Together the tasks form a logical unit of work
in which all of them must succeed or none of
them can succeed.
• If some tasks succeed but at least one fails,
then all successful tasks are reversed,
returning the system to its original state
before the transaction -- or job step -- was
initiated.
 Example of transaction
• customer's purchase through an e-commerce site.
• After selecting a product and entering the necessary
information, the customer confirms the final sale by
clicking OK or taking some other step to conclude the
process.
• This causes the application to launch a sales
transaction, which includes multiple steps, such as
confirming the product's availability, validating the
credit card, initiating the shipping process and placing
the order with the warehouse.
• If any steps fail, the entire transaction is rolled back.
For example, the product might not be available or the
credit card might fail to validate.
• In this case, any changes are reverted so everything is
returned to its original state.
 Specifying the transaction
• One way of specifying the transaction boundaries is by
specifying explicit begin transaction and end
transaction statements in an application program.
• In this case, all database access operations between
the two are considered as forming one transaction.
• A single application program may contain more than
one transaction if it contains several transaction
boundaries.
• If the database operations in a transaction do not
update the database but only retrieve data, the
transaction is called a read-only transaction; otherwise
it is known as a read-write transaction.
• A database is basically represented as a
collection of named data items.
• A data item can be a database record, but it
can also be a larger unit such as a whole disk
block, or even a smaller unit such as an
individual field (attribute) value of some
record in the database.
• Each data item has a unique name, but this
name is not typically used by the programmer;
rather, it is just a means to uniquely identify
each data item.
• The basic database access operations that a
transaction can include are as follows:
– read_item(X). Reads a database item named X into a program
variable. To simplify our notation.
– write_item(X). Writes the value of program variable X into the
database item named X.

• It is like in programming when we write:

Cout<<“Enter SNN”;
Cin>>SNN;
Here, SNN is the variable to assign the value.
Just like, X in read_item and write_item.
 read_item(x)
• Steps:
Find the address of the disk block that
contains item x. “every disk consists of blocks,
each block has size and address, all blocks are
of the same size”.
Copy the disk block into a buffer in main
memory (if that disk block is not already in
some main memory buffer).
Copy item x from the buffer to the program
variable named x.
 write_item(x)
• Steps:
 Find the address of the disk block that contains
item x.
 Copy that disk block into a buffer in main
memory (if that disk block is not already in some
main memory buffer).
 Copy item x from the program variable named x
into its correct location in the buffer.
 Store the updated block from the buffer back to
disk.
 Example on transactions

1. read_item(X );
2. X := X – N; “update only in buffer not db/disk”
3. write_item(X ); “ update on db/disk”
4. read_item(Y );
5. Y := Y + N;
6. write_item(Y );
• If step1: read x=5 step2: x=5-3=2
If any other transaction read x in that moment, it
will read x=5 “before step3 execution”
 Concurrency
• Many transactions are performed
simultaneously.

• Could be done on one processor

“sequentially”.

• Could be done on multiple processors

“parallelization”.
 Why Concurrency Control Is Needed
• Concurrency control and recovery
mechanisms are mainly concerned with the
database commands in a transaction.
• Transactions submitted by the various users
may execute concurrently and may access and
update the same database items.
• If this concurrent execution is uncontrolled, it
may lead to problems, such as an inconsistent
database.
 Problems of non-concurrency control
1. The 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.
• Suppose that transactions T1 and T2 are submitted at approximately
the same time, and suppose that their operations are interleaved;
• 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.
• For example, if X = 80 at the start (originally there were 80
reservations on the flight), N = 5 (T1 transfers 5 seat reservations
from the flight corresponding to X to the flight corresponding to Y),
and M = 4 (T2 reserves 4 seats on X),
• the final result should be X = 79.
• However, in the interleaving of operations, it is X = 84 because the
update in T1 that removed the five seats from X was lost.
• Example: lost update problem
• Example:
2. The Temporary Update (or Dirty Read) Problem:
This problem occurs when one transaction updates a
database item and then the transaction fails for some reason.
Meanwhile, the updated item is accessed (read) by another
transaction before it is changed back (or rolled back) to its
original value.
an example where T1 updates item X and then fails before
completion, so the system must roll back X 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.
• Example: The Temporary Update (or Dirty
Read) Problem:
3. The Incorrect Summary 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 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.
• Example:
4. The Unrepeatable Read Problem:
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.
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.