Database Management System

Transaction

o The transaction is a set of logically related operation. It contains a group of tasks.

o A transaction is an action or series of actions. It is performed by a single user to perform
operations for accessing the contents of the database.

Example: Suppose an employee of bank transfers Rs 800 from X's account to Y's account. This
small transaction contains several low-level tasks:

X's Account

1. Open_Account(X)
2. Old_Balance = [Link]
3. New_Balance = Old_Balance - 800
4. [Link] = New_Balance
5. Close_Account(X)

Y's Account

1. Open_Account(Y)
2. Old_Balance = [Link]
3. New_Balance = Old_Balance + 800
4. [Link] = New_Balance
5. Close_Account(Y)

Operations of Transaction:

Following are the main operations of transaction:

Read(X): Read operation is used to read the value of X from the database and stores it in a
buffer in main memory.

Write(X): Write operation is used to write the value back to the database from the buffer.

Let's take an example to debit transaction from an account which consists of following
operations:

1. 1. R(X);
2. 2. X = X - 500;
3. 3. W(X);

Let's assume the value of X before starting of the transaction is 4000.

 Database Management System

o The first operation reads X's value from database and stores it in a buffer.
o The second operation will decrease the value of X by 500. So buffer will contain 3500.
o The third operation will write the buffer's value to the database. So X's final value will be
3500.

But it may be possible that because of the failure of hardware, software or power, etc. that
transaction may fail before finished all the operations in the set.

For example: If in the above transaction, the debit transaction fails after executing operation 2
then X's value will remain 4000 in the database which is not acceptable by the bank.

To solve this problem, we have two important operations:

Commit: It is used to save the work done permanently.

Rollback: It is used to undo the work done.

Transaction property

The ACID Properties of a Transaction

The transaction has the four properties. These are used to maintain consistency in a database,
before and after the transaction.

Property of Transaction
1. Atomicity
2. Consistency
3. Isolation
4. Durability
 Database Management System

Atomicity
o It states that all operations of the transaction take place at once if not, the transaction is
aborted.
o There is no midway, i.e., the transaction cannot occur partially. Each transaction is
treated as one unit and either run to completion or is not executed at all.
 Database Management System

Atomicity involves the following two operations:

Abort: If a transaction aborts then all the changes made are not visible.

Commit: If a transaction commits then all the changes made are visible.

Example: Let's assume that following transaction T consisting of T1 and T2. A consists of Rs
600 and B consists of Rs 300. Transfer Rs 100 from account A to account B.

T1 T2

Read(A) Read(B)
A:= A-100 B:= B+100
Write(A) Write(B)

After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.

If the transaction T fails after the completion of transaction T1 but before completion of
transaction T2, then the amount will be deducted from A but not added to B. This shows the
inconsistent database state. In order to ensure correctness of database state, the transaction must
be executed in entirety.

Consistency
o The integrity constraints are maintained so that the database is consistent before and after
the transaction.
o The execution of a transaction will leave a database in either its prior stable state or a new
stable state.
o The consistent property of database states that every transaction sees a consistent
database instance.
o The transaction is used to transform the database from one consistent state to another
consistent state.

For example: The total amount must be maintained before or after the transaction.

1. Total before T occurs = 600+300=900

2. Total after T occurs= 500+400=900

Therefore, the database is consistent. In the case when T1 is completed but T2 fails, then
inconsistency will occur.
 Database Management System

Isolation
o It shows that the data which is used at the time of execution of a transaction cannot be
used by the second transaction until the first one is completed.
o In isolation, if the transaction T1 is being executed and using the data item X, then that
data item can't be accessed by any other transaction T2 until the transaction T1 ends.
o The concurrency control subsystem of the DBMS enforced the isolation property.

Durability
o The durability property is used to indicate the performance of the database's consistent
state. It states that the transaction made the permanent changes.
o They cannot be lost by the erroneous operation of a faulty transaction or by the system
failure. When a transaction is completed, then the database reaches a state known as the
consistent state. That consistent state cannot be lost, even in the event of a system's
failure.
o The recovery subsystem of the DBMS has the responsibility of Durability property.

States of Transaction

In a database, the transaction can be in one of the following states -

Active state
 Database Management System

o The active state is the first state of every transaction. In this state, the transaction is being
executed.
o For example: Insertion or deletion or updating a record is done here. But all the records
are still not saved to the database.

Partially committed

o In the partially committed state, a transaction executes its final operation, but the data is
still not saved to the database.
o In the total mark calculation example, a final display of the total marks step is executed in
this state.

Committed

A transaction is said to be in a committed state if it executes all its operations successfully. In

this state, all the effects are now permanently saved on the database system.

Failed state

o If any of the checks made by the database recovery system fails, then the transaction is
said to be in the failed state.
o In the example of total mark calculation, if the database is not able to fire a query to fetch
the marks, then the transaction will fail to execute.

Aborted

o If any of the checks fail and the transaction has reached a failed state then the database
recovery system will make sure that the database is in its previous consistent state. If not
then it will abort or roll back the transaction to bring the database into a consistent state.
o If the transaction fails in the middle of the transaction then before executing the
transaction, all the executed transactions are rolled back to its consistent state.
o After aborting the transaction, the database recovery module will select one of the two
operations:
1. Re-start the transaction
2. Kill the transaction
 Database Management System

Schedules in DBMS-

The order in which the operations of multiple transactions appear for execution
is called as a schedule.

Types of Schedules-

In DBMS, schedules may be classified as-

Serial Schedules-

In serial schedules,
 All the transactions execute serially one after the other.
 When one transaction executes, no other transaction is allowed to execute.

Characteristics-

Serial schedules are always-

 Consistent
 Recoverable
 Cascadeless
 Database Management System

 Strict

Example-01:

In this schedule,
 There are two transactions T1 and T2 executing serially one after the other.
 Transaction T1 executes first.
 After T1 completes its execution, transaction T2 executes.
 So, this schedule is an example of a Serial Schedule.

Example-02:
 Database Management System

In this schedule,
 There are two transactions T1 and T2 executing serially one after the other.
 Transaction T2 executes first.
 After T2 completes its execution, transaction T1 executes.
 So, this schedule is an example of a Serial Schedule.

Non-Serial Schedules-

In non-serial schedules,
 Multiple transactions execute concurrently.
 Operations of all the transactions are inter leaved or mixed with each other.

Characteristics-

Non-serial schedules are NOT always-

 Database Management System

 Consistent
 Recoverable
 Cascadeless
 Strict

Example-01:

In this schedule,
 There are two transactions T1 and T2 executing concurrently.
 The operations of T1 and T2 are interleaved.
 So, this schedule is an example of a Non-Serial Schedule.

Example-02:
 Database Management System

In this schedule,
 There are two transactions T1 and T2 executing concurrently.
 The operations of T1 and T2 are interleaved.
 So, this schedule is an example of a Non-Serial Schedule.

Finding Number Of Schedules-

Consider there are n number of transactions T1, T2, T3 …. , Tn with N1, N2, N3 …. , Nn
number of operations respectively.

Total Number of Schedules-

Total number of possible schedules (serial + non-serial) is given by-

 Database Management System

Total Number of Serial Schedules-

Total number of serial schedules

= Number of different ways of arranging n transactions
= n!

Total Number of Non-Serial Schedules-

Total number of non-serial schedules

= Total number of schedules – Total number of serial schedules

Serializability in DBMS-

 Some non-serial schedules may lead to inconsistency of the database.

 Serializability is a concept that helps to identify which non-serial schedules are correct
and will maintain the consistency of the database.

Serializable Schedules-

If a given non-serial schedule of ‘n’ transactions is equivalent to some serial schedule of ‘n’
transactions, then it is called as a serializable schedule.

Characteristics-

Serializable schedules behave exactly same as serial schedules.

Thus, serializable schedules are always-
 Database Management System

 Consistent
 Recoverable
 Casacadeless
 Strict

Serial Schedules VsSerializable Schedules-

Serial Schedules Serializable Schedules

No concurrency is allowed. Concurrency is allowed.

Thus, all the transactions necessarily Thus, multiple transactions can
execute concurrently.
execute serially one after the other.

Serializable schedules improve both

Serial schedules lead to less resource
resource utilization and CPU
utilization and CPU throughput.
throughput.

Serial Schedules are less efficient as Serializable Schedules are always

compared to serializable schedules. better than serial schedules.
(due to above reason) (due to above reason)

Types of Serializability-

Serializability is mainly of two types-

1. Conflict Serializability
 Database Management System

2. View Serializability
Conflict Serializability-

If a given non-serial schedule can be converted into a serial schedule by swapping its non-
conflicting operations, then it is called as a conflict serializable schedule.

Conflicting Operations-

Two operations are called as conflicting operations if all the following conditions hold true for
them-
 Both the operations belong to different transactions
 Both the operations are on the same data item
 At least one of the two operations is a write operation

Example-

Consider the following schedule-

In this schedule,
 W1 (A) and R2 (A) are called as conflicting operations.
 Database Management System

 This is because all the above conditions hold true for them.

Checking Whether a Schedule is Conflict SerializableOr Not-

Follow the following steps to check whether a given non-serial schedule is conflict serializable
or not-

Step-01:

Find and list all the conflicting operations.

Step-02:

Start creating a precedence graph by drawing one node for each transaction.

Step-03:

 Draw an edge for each conflict pair such that if Xi (V) and Yj (V) forms a conflict pair
then draw an edge from Ti to Tj.
 This ensures that Ti gets executed before Tj.

Step-04:

 Check if there is any cycle formed in the graph.

 If there is no cycle found, then the schedule is conflict serializable otherwise not.

Problem-01:

Check whether the given schedule S is conflict serializable and recoverable or not-
 Database Management System

Solution-

Checking Whether S is Conflict SerializableOr Not-

Step-01:

List all the conflicting operations and determine the dependency between the transactions-
 R2(X) , W3(X) (T2 → T3)
 R2(X) , W1(X) (T2 → T1)
 W3(X) , W1(X) (T3 → T1)
 W3(X) , R4(X) (T3 → T4)
 W1(X) , R4(X) (T1 → T4)
 W2(Y) , R4(Y) (T2 → T4)
 Database Management System

Step-02:

Draw the precedence graph-

 Clearly, there exists no cycle in the precedence graph.

 Therefore, the given schedule S is conflict serializable.
 Serial schedule is T2---T3---T1---T4

Checking Whether S is Recoverable Or Not-

 Conflict serializable schedules are always recoverable.

 Therefore, the given schedule S is recoverable.

View Serializability-


If a given schedule is found to be view equivalent to some serial schedule, then

it is called as a view serializable schedule.

View Equivalent Schedules-

Consider two schedules S1 and S2 each consisting of two transactions T1 and T2.
 Database Management System

Schedules S1 and S2 are called view equivalent if the following three conditions hold true for
them-

Condition-01:

For each data item X, if transaction Ti reads X from the database initially in schedule S1, then in
schedule S2 also, Ti must perform the initial read of X from the database.

Thumb Rule
“Initial readers must be same for all the data items”.

Condition-02:

If transaction Ti reads a data item that has been updated by the transaction T j in schedule S1, then
in schedule S2 also, transaction Ti must read the same data item that has been updated by the
transaction Tj.

Thumb Rule
“Write-read sequence must be same.”.

Condition-03:

For each data item X, if X has been updated at last by transaction Ti in schedule S1, then in
schedule S2 also, X must be updated at last by transaction Ti.

Thumb Rule
“Final writers must be same for all the data items”.
 Database Management System

Checking Whether a Schedule is View SerializableOr Not-

Method-01:

Check whether the given schedule is conflict serializable or not.

 If the given schedule is conflict serializable, then it is surely view serializable. Stop and
report your answer.
 If the given schedule is not conflict serializable, then it may or may not be view
serializable. Go and check using other methods.

Thumb Rules
 All conflict serializable schedules are view serializable.
 All view serializable schedules may or may not be conflict
serializable.

Method-02:

Check if there exists any blind write operation.

(Writing without reading is called as a blind write).
 If there does not exist any blind write, then the schedule is surely not view serializable.
Stop and report your answer.
 If there exists any blind write, then the schedule may or may not be view serializable. Go
and check using other methods.

Thumb Rule
No blind write means not a view serializable schedule.

Method-03:

In this method, try finding a view equivalent serial schedule.

 Database Management System

 By using the above three conditions, write all the dependencies.

 Then, draw a graph using those dependencies.
 If there exists no cycle in the graph, then the schedule is view serializable otherwise not.
Check whether the given schedule S is view serializable or not-

Solution-

 We know, if a schedule is conflict serializable, then it is surely view serializable.

 So, let us check whether the given schedule is conflict serializable or not.

Checking Whether S is Conflict SerializableOr Not-

Step-01:

List all the conflicting operations and determine the dependency between the transactions-
 R1(A) , W3(A) (T1 → T3)
 R2(A) , W3(A) (T2 → T3)
 R2(A) , W1(A) (T2 → T1)
 W3(A) , W1(A) (T3 → T1)

Step-02:
 Database Management System

Draw the precedence graph-

 Clearly, there exists a cycle in the precedence graph.

 Therefore, the given schedule S is not conflict serializable.

Now,
 Since, the given schedule S is not conflict serializable, so, it may or may not be view
serializable.
 To check whether S is view serializable or not, let us use another method.
 Let us check for blind writes.

Checking for Blind Writes-

 There exists a blind write W3 (A) in the given schedule S.

 Therefore, the given schedule S may or may not be view serializable.

Now,
 To check whether S is view serializable or not, let us use another method.
 Let us derive the dependencies and then draw a dependency graph.

Drawing a Dependency Graph-

 T1 firstly reads A and T3 firstly updates A.

 Database Management System

 So, T1 must execute before T3.

 Thus, we get the dependency T1 → T3.
 Final updation on A is made by the transaction T1.
 So, T1 must execute after all other transactions.
 Thus, we get the dependency (T2, T3) → T1.
 There exists no write-read sequence.

Now, let us draw a dependency graph using these dependencies-

 Clearly, there exists a cycle in the dependency graph.

 Thus, we conclude that the given schedule S is not view serializable.

Non-Serializable Schedules-Schedules involving aborted transaction

 A non-serial schedule which is not serializable is called as a non-serializable schedule.

 A non-serializable schedule is not guaranteed to produce the the same effect as produced
by some serial schedule on any consistent database.

Characteristics-

Non-serializable schedules-
 may or may not be consistent
 may or may not be recoverable
 Database Management System

Irrecoverable Schedules-

If in a schedule,
 A transaction performs a dirty read operation from an uncommitted transaction
 And commits before the transaction from which it has read the value then such a schedule
is known as an Irrecoverable Schedule.

Example-

Consider the following schedule-

Here,
 T2 performs a dirty read operation.
 T2 commits before T1.
 T1 fails later and roll backs.
 The value that T2 read now stands to be incorrect.
 T2 can not recover since it has already committed.
 Database Management System

Recoverable Schedules-

If in a schedule,
 A transaction performs a dirty read operation from an uncommitted transaction
 And its commit operation is delayed till the uncommitted transaction either commits or
roll backs then such a schedule is known as a Recoverable Schedule.

Here,
 The commit operation of the transaction that performs the dirty read is delayed.
 This ensures that it still has a chance to recover if the uncommitted transaction fails later.

Example-

Consider the following schedule-

 Database Management System

Here,
 T2 performs a dirty read operation.
 The commit operation of T2 is delayed till T1 commits or roll backs.
 T1 commits later.
 T2 is now allowed to commit.
 In case, T1 would have failed, T2 has a chance to recover by rolling back.

Checking Whether a Schedule is Recoverable or Irrecoverable-

Method-01:

Check whether the given schedule is conflict serializable or not.

 If the given schedule is conflict serializable, then it is surely recoverable. Stop and report
your answer.
 If the given schedule is not conflict serializable, then it may or may not be recoverable.
Go and check using other methods.

Thumb Rules
 All conflict serializable schedules are recoverable.
 All recoverable schedules may or may not be conflict serializable.

Method-02:

Check if there exists any dirty read operation.

(Reading from an uncommitted transaction is called as a dirty read)
 If there does not exist any dirty read operation, then the schedule is surely recoverable.
Stop and report your answer.
 If there exists any dirty read operation, then the schedule may or may not be recoverable.

If there exists a dirty read operation, then follow the following cases-

Case-01:
 Database Management System

If the commit operation of the transaction performing the dirty read occurs before the commit or
abort operation of the transaction which updated the value, then the schedule is irrecoverable.

Case-02:

If the commit operation of the transaction performing the dirty read is delayed till the commit or
abort operation of the transaction which updated the value, then the schedule is recoverable.

Thumb Rule
No dirty read means a recoverable schedule.

Types of Recoverable Schedules-

A recoverable schedule may be any one of these kinds-

1. Cascading Schedule
2. Cascadeless Schedule
3. Strict Schedule

Cascading Schedule-
 Database Management System

 If in a schedule, failure of one transaction causes several other dependent transactions to

rollback or abort, then such a schedule is called as a Cascading Schedule or Cascading
Rollback or Cascading Abort.
 It simply leads to the wastage of CPU time.

\
Example-

Here,
 Transaction T2 depends on transaction T1.
 Transaction T3 depends on transaction T2.
 Transaction T4 depends on transaction T3.
 Database Management System

In this schedule,
 The failure of transaction T1 causes the transaction T2 to rollback.
 The rollback of transaction T2 causes the transaction T3 to rollback.
 The rollback of transaction T3 causes the transaction T4 to rollback.
Such a rollback is called as a Cascading Rollback.

NOTE-

If the transactions T2, T3 and T4 would have committed before the failure of transaction T1,
then the schedule would have been irrecoverable.

Cascadeless Schedule-

If in a schedule, a transaction is not allowed to read a data item until the last transaction that has
written it is committed or aborted, then such a schedule is called as a Cascadeless Schedule.
In other words,
 Cascadeless schedule allows only committed read operations.
 Therefore, it avoids cascading roll back and thus saves CPU time.

Example-
 Database Management System

NOTE-

 Cascadeless schedule allows only committed read operations.

 However, it allows uncommitted write operations.

Example-
 Database Management System

Strict Schedule-

If in a schedule, a transaction is neither allowed to read nor write a data item until the last
transaction that has written it is committed or aborted, then such a schedule is called as a Strict
Schedule.
In other words,
 Strict schedule allows only committed read and write operations.
 Clearly, strict schedule implements more restrictions than cascadeless schedule.

Example-
 Database Management System

Remember-

 Strict schedules are more strict than cascadeless schedules.

 All strict schedules are cascadeless schedules.
 All cascadeless schedules are not strict schedules.

Equivalence of Schedules-

In DBMS, schedules may have the following three different kinds of equivalence relations
among them-

1. Result Equivalence
2. Conflict Equivalence
3. View Equivalence
 Database Management System

1. Result Equivalent Schedules-

 If any two schedules generate the same result after their execution, then they are called as
result equivalent schedules.
 This equivalence relation is considered of least significance.
 This is because some schedules might produce same results for some set of values and
different results for some other set of values.

2. Conflict Equivalent Schedules-

If any two schedules satisfy the following two conditions, then they are called as conflict
equivalent schedules-
1. The set of transactions present in both the schedules is same.
2. The order of pairs of conflicting operations of both the schedules is same.
 Database Management System

3. View Equivalent Schedules-

We have already discussed about View Equivalent Schedules.

Concurrency Problems in DBMS-Anomalies due to interleaved transaction in dbms

 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.

The concurrency problems are-

 Database Management System

1. Dirty Read Problem

2. Unrepeatable Read Problem
3. Lost Update Problem
4. Phantom Read Problem

1. Dirty Read Problem-

Reading the data written by an uncommitted transaction is called as dirty

read.

This read is called as dirty read because-

 There is always a chance that the uncommitted transaction might roll back later.
 Thus, uncommitted transaction might make other transactions read a value that does not
even exist.
 This leads to inconsistency of the database.

NOTE-

 Dirty read does not lead to inconsistency always.

 Database Management System

 It becomes problematic only when the uncommitted transaction fails and roll backs later
due to some reason.

Example-

Here,
1. T1 reads the value of A.
2. T1 updates the value of A in the buffer.
3. T2 reads the value of A from the buffer.
4. T2 writes the updated the value of A.
5. T2 commits.
6. T1 fails in later stages and rolls back.

In this example,
 T2 reads the dirty value of A written by the uncommitted transaction T1.
 T1 fails in later stages and roll backs.
 Thus, the value that T2 read now stands to be incorrect.
 Therefore, database becomes inconsistent.
 Database Management System

2. Unrepeatable Read Problem-

This problem occurs when a transaction gets to read unrepeated i.e. different values of the same
variable in its different read operations even when it has not updated its value.

Example-

Here,
1. T1 reads the value of X (= 10 say).
2. T2 reads the value of X (= 10).
3. T1 updates the value of X (from 10 to 15 say) in the buffer.
4. T2 again reads the value of X (but = 15).

In this example,
 T2 gets to read a different value of X in its second reading.
 T2 wonders how the value of X got changed because according to it, it is running in
isolation.

3. Lost Update Problem(write-write pblm)-

This problem occurs when multiple transactions execute concurrently and updates from one or
more transactions get lost.
 Database Management System

Example-

Here,
1. T1 reads the value of A (= 10 say).
2. T2 updates the value to A (= 15 say) in the buffer.
3. T2 does blind write A = 15 (write without read) in the buffer.
4. T2 commits.
5. When T1 commits, it writes A = 25 in the database.

In this example,
 T1 writes the over written value of X in the database.
 Thus, update from T2 gets lost.

NOTE-

 This problem occurs whenever there is a write-write conflict.

 In write-write conflict, there are two writes one by each transaction on the same data item
without any read in the middle.
Example 2
 Database Management System

4. Phantom Read Problem-

This problem occurs when a transaction reads some variable from the buffer and when it reads
the same variable later, it finds that the variable does not exist.

Example-
 Database Management System

Here,
1. T1 reads X.
2. T2 reads X.
3. T1 deletes X.
4. T2 tries reading X but does not find it.

In this example,
 T2 finds that there does not exist any variable X when it tries reading X again.
 T2 wonders who deleted the variable X because according to it, it is running in isolation.

Avoiding Concurrency Problems-

 To ensure consistency of the database, it is very important to prevent the occurrence of

above problems.
 Concurrency Control Protocols help to prevent the occurrence of above problems and
maintain the consistency of the database.

Concurrency Control Protocol

Concurrency control protocols ensure atomicity, isolation, and serializability of concurrent

transactions. The concurrency control protocol can be divided into three categories:

1. Lock based protocol

2. Time-stamp protocol
3. Validation based protocol

Lock-Based Protocol

One of the main techniques used to control concurrency execution of transactions (that is, to
provide serialisable execution of transactions) is based on the concept of locking data items. A
lock is a variable associate with a data item in the database and describes the status of that data
item with respect to possible operations that can be applied to the item. Generally speaking, there
is one lock for each data item in the database. The overall purpose of locking is to obtain
maximum concurrency and minimum delay in processing transactions.
 Database Management System

Types of locks

The idea of locking is simple: when a transaction needs an assurance that some object, typically
a database record that it is accessing in some way, will not change in some unpredictable manner
while the transaction is not running on the CPU, it acquires a lock on that object. The lock
prevents other transactions from accessing the object. Thus the first transaction can be sure
that the object in question will remain in a stable state as long as the transaction desires.
There are several types of locks that can be used in concurrency control. Binary locks are the
simplest, but are somewhat restrictive in their use.

Binary locks

A binary lock can have two states or values: locked and unlocked (or 1 and 0, for simplicity).
A distinct lock is associated with each database item X. If the value of the lock on X is 1, item
X is locked and cannot be accessed by a database operation that requests the item. If the
value of the lock on X is 0, item X is unlocked, and it can be accessed when requested. We
refer to the value of the lock associated with item X as LOCK(X).
Two operations, lock and unlock, must be included in the transactions when binary locking is
used. A transaction requests access to an item X by issuing a lock(X) operation. If LOCK(X) =
1, the transaction is forced to wait; otherwise, the transaction sets LOCK(X) := 1 (locks the
item) and allows access. When the transaction is through using the item, it issues an unlock(X)
operation, which sets LOCK(X) := 0 (unlocks the item) so that X may be accessed by other
transactions. Hence, a binary lock enforces mutual exclusion on the data item. The DBMS has a
lock manager subsystem to keep track of and control access to locks.
When the binary locking scheme is used, every transaction must obey the following rules:
1. A transaction T must issue the operation lock(X) before any read_item(X) or
write_item(X) operations are performed in T.
2. A transaction T must issue the operation unlock(X) after all read_item(X) and
write_item(X) operations are completed in T.
3. A transaction T will not issue a lock(X) operation if it already holds the lock on item X.
4. A transaction T will not issue an unlock(X) operation unless it already holds the lock on
item X.
These rules can be enforced by a module of the DBMS. Between the lock(X) and unlock(X)
operations in a transaction T, T is said to hold the lock on item X. At most, one transaction can
hold the lock on a particular item. No two transactions can access the same item concurrently.
 Database Management System

Shared and exclusive locks

1. Shared Lock

Often represented as lock-S(), Shared Locks are basically the locks that grant Read-Only access
to the data items associated with it. This means when a shared lock is implemented on a
database, it can be READ by multiple users, however, none of the users reading the data items
will be able to update it. In other words, there is no write access associated with Shared Lock.
Since multiple users can read the data items simultaneously, more than one shared lock can be
placed on them at a given time but for the shared lock to be placed, the data item must not have
any other locks associated with it.

Example of shared Lock:

Consider
a transaction(T1) which requires only to read the data item value A. The following steps take
place when lock protocol is applied to this traction
1. T1 will acquire an shared lock on the data item A
2. Read the current value of data item A
3. Once the transaction is completed, the data item will be unlocked
 Database Management System

2. Exclusive Lock
While Shared Lock allows Read-Only access, Exclusive Locks allow both Read and Write
access on a data item present in the database. Often represented as lock-X(), Exclusive locks
provide the privilege of reading and modifying the data as seen fit by a user. For the sake of
security and consistency of the database, this process will be exclusive to the user using them
and is not permitted to other users trying to access the same data item. Once the exclusive lock is
placed, no other locks including the Shared lock can be placed on the same row or column until
its unlocked.
Example of Exclusive Lock:

Consider a transaction(T2) which requires to update the data item value A. The following steps

take place when lock protocol is applied to this traction

1. T2 will acquire an exclusive lock on the data item A
2. Read the current value of data item A
3. Modify the data item as required. In the example illustrated, a value of 50 is subtracted
from the data item A
4. Write the updated value of the data item
5. Once the transaction is completed, the data item will be unlocked.
Lock Compatibility Matrix
A keynote while applying the Lock based protocols in DBMS is that: any number of tractions
can hold a Shared Lock while only one traction has a claim over Exclusive Lock since because
shared lock is only reading the data and not performing any other actions whereas the exclusive
 Database Management System

lock is performing both read and write operations. This can be illustrated using a compatibility
matrix like the one shown below:

The figure illustrates that when two transactions are involved, and both attempt to only read a
given data item, it is permitted and no conflict arises, but when one transaction attempts to write
the data item and another one tries to read or write at the same time, conflict occurs resulting in a
denied interaction.
Conversion between the locks is possible by the two methods listed below:
Upgrading: conversion from a read lock to write lock
Downgrading: conversion from a write lock to read lock
Problems associated with Simple locking:
1. Data inconsistency between multiple transactions
2. Deadlock, a situation where the transactions try to access lock on already locked data
items
3. No guarantee of serializability (i.e. execution of a concurrent transaction equivalent to
that of a transaction executed serially)

Problem With Simple Locking…

Consider the Partial Schedule:

T1 T2

1 lock-X(B)

2 read(B)
 Database Management System

3 B:=B-50

4 write(B)

5 lock-S(A)

6 read(A)

7 lock-S(B)

8 lock-X(A)

9 …… ……

Deadlock – consider the above execution phase. Now, T1 holds an Exclusive lock over B, and
T2 holds a Shared lock over A. Consider Statement 7, T2 requests for lock on B, while in
Statement 8 T1 requests lock on A. This as you may notice imposes a Deadlock as none can
proceed with their execution.
Starvation – is also possible if concurrency control manager is badly designed. For example: A
transaction may be waiting for an X-lock on an item, while a sequence of other transactions
request and are granted an S-lock on the same item. This may be avoided if the concurrency
control manager is properly designed.
Starvation is the situation when a transaction needs to wait for an indefinite period to acquire a
lock.
Following are the reasons for Starvation:
 When waiting scheme for locked items is not properly managed
 In the case of resource leak
 The same transaction is selected as a victim repeatedly

There are 3 types of lock protocols available:

1. Simplistic lock protocol

It is the simplest way of locking the data while transaction. Simplistic lock-based protocols allow
all the transactions to get the lock on the data before insert or delete or update on it. It will
unlock the data item after completing the transaction.

2. Pre-claiming Lock Protocol

 Database Management System

o Pre-claiming Lock Protocols evaluate the transaction to list all the data items on which
they need locks.
o Before initiating an execution of the transaction, it requests DBMS for all the lock on all
those data items.
o If all the locks are granted then this protocol allows the transaction to begin. When the
transaction is completed then it releases all the lock.
o If all the locks are not granted then this protocol allows the transaction to rolls back and
waits until all the locks are granted.

3. Two-phase locking (2PL)

o The two-phase locking protocol divides the execution phase of the transaction into three
parts.
o In the first part, when the execution of the transaction starts, it seeks permission for the
lock it requires.
o In the second part, the transaction acquires all the locks. The third phase is started as soon
as the transaction releases its first lock.
o In the third phase, the transaction cannot demand any new locks. It only releases the
acquired locks.
 Database Management System

There are two phases of 2PL:

Growing phase: In the growing phase, a new lock on the data item may be acquired by the
transaction, but none can be released.

Shrinking phase: In the shrinking phase, existing lock held by the transaction may be released,
but no new locks can be acquired.

In the below example, if lock conversion is allowed then the following phase can happen:

1. Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.

2. Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.

Example:
 Database Management System

The following way shows how unlocking and locking work with 2-PL.

Transaction T1:

o Growing phase: from step 1-3

o Shrinking phase: from step 5-7
o Lock point: at 3

Transaction T2:

o Growing phase: from step 2-6

o Shrinking phase: from step 8-9
o Lock point: at 6
 Database Management System

What is LOCK POINT ?The Point at which the growing phase ends, i.e., when
transaction takes the final lock it needs to carry on its work.

Cascading Rollbacks in 2-PL –

Let’s see the following Schedule:

because of Dirty Read in T2 and T3 in lines 8 and 12 respectively, when T1 failed we

have to rollback others also. Hence Cascading Rollbacks are possible in 2-PL.

Deadlock in 2-PL –
Consider this simple example, it will be easy to [Link] we have two transactions T 1 and
T2.
Schedule: Lock-X1(A) Lock-X2(B) Lock-X1(B) Lock-X2(A)
 Database Management System

T1 T2

Lock-X1(A)
Lock-X2(B)

Lock-X1(B)
Lock-X2(A)

Drawing the precedence graph, you may detect the loop. So Deadlock is also possible in 2-PL.

Two-phase locking may also limit the amount of concurrency that occurs in a schedule because a
Transaction may not be able to release an item after it has used it. This may be because of the
protocols and other restrictions we may put on the schedule to ensure serializability, deadlock
freedom and other factors
The above mentioned type of 2-PL is called Basic 2PL.

To sum it up it ensures Conflict Serializability but does not prevent Cascading Rollback
and Deadlock

Categories of Two Phase Locking (Strict, Rigorous & Conservative)

Strict Two-phase locking (Strict-2PL)

This requires that in addition to the lock being 2-Phase all Exclusive(X) Locks held by the
transaction be released until after the Transaction Commits.

o The first phase of Strict-2PL is similar to 2PL. In the first phase, after acquiring all the
locks, the transaction continues to execute normally.
o The only difference between 2PL and strict 2PL is that Strict-2PL does not release a lock
after using it.
o Strict-2PL waits until the whole transaction to commit, and then it releases all the locks at
a time.
o Strict-2PL protocol does not have shrinking phase of lock release.
 Database Management System

Following Strict 2-PL ensures that our schedule is:

 Recoverable
 Cascadeless
Hence it gives us freedom from Cascading Abort which was still there in Basic 2-PL and
moreover guarantee Strict Schedules but still Deadlocks are possible!

Rigorous 2-PL –

This requires that in addition to the lock being 2-Phase all Exclusive(X) and Shared(S)
Locks held by the transaction be released until after the Transaction Commits.
Following Rigorous 2-PL ensures that our schedule is:

 Recoverable
 Cascadeless
Hence it gives us freedom from Cascading Abort which was still there in Basic 2-PL and
moreover guarantee Strict Schedules but still Deadlocks are possible!
Note the difference between Strict 2-PL and Rigorous 2-PL is that Rigorous is more restrictive, it
requires both Exclusive and Shared locks to be held until after the Transaction commits and this
is what makes the implementation of Rigorous 2-PL more easy.

Conservative 2-PL

 It requires locking of all data items to access before the transaction starts.
 Database Management System

Conservative 2-PL is Deadlock free and but it does not ensure Strict schedule.

However, it is difficult to use in practice because of need to predeclare the read-set and the write-
set which is not possible in many situations. In practice, the most popular variation of 2-PL is
Strict 2-PL.

Deadlock in DBMS

A deadlock is a condition where two or more transactions are waiting indefinitely for one
another to give up locks. Deadlock is said to be one of the most feared complications in DBMS
as no task ever gets finished and is in waiting state forever.

For example: In the student table, transaction T1 holds a lock on some rows and needs to update
some rows in the grade table. Simultaneously, transaction T2 holds locks on some rows in the
grade table and needs to update the rows in the Student table held by Transaction T1.

Now, the main problem arises. Now Transaction T1 is waiting for T2 to release its lock and
similarly, transaction T2 is waiting for T1 to release its lock. All activities come to a halt state
and remain at a standstill. It will remain in a standstill until the DBMS detects the deadlock and
aborts one of the transactions.

Deadlock Avoidance

o When a database is stuck in a deadlock state, then it is better to avoid the database rather
than aborting or restating the database. This is a waste of time and resource.
 Database Management System

o Deadlock avoidance mechanism is used to detect any deadlock situation in advance. A

method like "wait for graph" is used for detecting the deadlock situation but this method
is suitable only for the smaller database. For the larger database, deadlock prevention
method can be used.

Deadlock Detection

In a database, when a transaction waits indefinitely to obtain a lock, then the DBMS should
detect whether the transaction is involved in a deadlock or not. The lock manager maintains a
Wait for the graph to detect the deadlock cycle in the database.

Wait for Graph

o This is the suitable method for deadlock detection. In this method, a graph is created
based on the transaction and their lock. If the created graph has a cycle or closed loop,
then there is a deadlock.
o The wait for the graph is maintained by the system for every transaction which is waiting
for some data held by the others. The system keeps checking the graph if there is any
cycle in the graph.

The wait for a graph for the above scenario is shown below:

Deadlock Prevention
 Database Management System

o Deadlock prevention method is suitable for a large database. If the resources are allocated
in such a way that deadlock never occurs, then the deadlock can be prevented.
o The Database management system analyzes the operations of the transaction whether
they can create a deadlock situation or not. If they do, then the DBMS never allowed that
transaction to be executed.

Wait-Die scheme

In this scheme, if a transaction requests for a resource which is already held with a conflicting
lock by another transaction then the DBMS simply checks the timestamp of both transactions. It
allows the older transaction to wait until the resource is available for execution.

Let's assume there are two transactions Ti and Tj and let TS(T) is a timestamp of any transaction
T. If Tj holds a lock by some other transaction and Ti is requesting for resources held by Tj then
the following actions are performed by DBMS:

1. Check if TS(Ti) < TS(Tj) - If Ti is the older transaction and Tj has held some resource,
then Ti is allowed to wait until the data-item is available for execution. That means if the
older transaction is waiting for a resource which is locked by the younger transaction,
then the older transaction is allowed to wait for resource until it is available.
2. Check if TS(Ti) < TS(Tj) - If Ti is older transaction and has held some resource and if Tj
is waiting for it, then Tj is killed and restarted later with the random delay but with the
same timestamp.

o [Note: The Timestamp Ordering Protocol is used to order the transactions based on their
Timestamps. The order of transaction is nothing but the ascending order of the
transaction creation. Let's assume there are two transactions T1 and T2. Suppose the
transaction T1 has entered the system at 007 times and transaction T2 has entered the
system at 009 times. T1 has the higher priority, so it executes first as it is entered the
system first.]

Wound wait scheme

o In wound wait scheme, if the older transaction requests for a resource which is held by
the younger transaction, then older transaction forces younger one to kill the transaction
and release the resource. After the minute delay, the younger transaction is restarted but
with the same timestamp.
o If the older transaction has held a resource which is requested by the Younger
transaction, then the younger transaction is asked to wait until older releases it.
 Database Management System

Crash Recovery: Stealing / Forcing Pages

The recovery manager ensures atomicity (by undoing actions of uncommitted transactions) and
durability (by guaranteeing that committed transactions survive crashes/failures) of transactions.

It ensure atomicity by undoing the action of transaction that do not commit.

And durability by making sure that all actions of commited transation survive system crashes.

When DBMS is restarted after crashes, the recovery manager is given control and must bring the
database into consistent state. The recovery manager is responsible for undoing the action of an
aborted transaction.

The transaction manager ensures serializability and consistency of transactions by using an

appropriate locking protocol.

Steal approach: The changes made to an object O by a transaction T may be written to disk
before T commits. This arises when the buffer manager decides to replace the frame containing
O by a page (belonging to a different transaction) from disk.

Force approach: Force all writes of a committed transaction to disk.

Most systems use a steal, no-force approach which is a realistic one:

If any dirty frame is chosen for replacement, the page it contains is written to disk (steal).

Dirty pages in buffer are not forced to disk at commit times of associated transactions (no-force).

It is the method of restoring the database to its correct state in the event of a failure at the time of
the transaction or after the end of a process.

Steal versus No-Steal Buffer Management

A page with modifications by an uncommitted transaction is a dirty page until either commit or
rollback processing for that transaction has been completed. The buffer manager can either
distinguish dirty pages from clean pages when deciding which page to remove from the buffer
pool, or it can ignore the update status of a page.
The buffer manager uses a steal policy, which means pages can be written out to disk even if the
transaction having modified the pages is still active. The alternative is the no-steal policy, in
which case all dirty pages are retained in the buffer pool until the final outcome of the
transaction has been determined.
 Database Management System

Advantages: The steal policy implies that rollback of a transaction requires access to pages on
disk in order to reestablish their old state. With the no-steal policy, no page on disk ever has to
be touched when rolling back a transaction. Consequently, no log information for UNDO
procedure will be needed. Roll back of a transaction during normal processing is also facilitated
by the no-steal policy since all pages modified by such a transaction are simply marked “invalid”
by the buffer manager. The problem with this policy is the size of the buffer pool + necessity of
page locking

Force versus No-Force Buffer Management Force versus no-force concerns writing of clean
pages from the buffer pool. The simple question here is: who decide, and when, that a modified
page is written out to disk? There are two basic approaches: Force policy. At phase 1 of a
transaction’s commit, the buffer manager locates all pages modified by that transaction and
writes the pages to disk. No-force policy. This is the liberal counterpart. A page, whether
modified or not, stays in the buffer as long as it is still needed. Only if it becomes the
replacement victim it will be written to disk. Advantage of the force policy – it avoids any
REDO recovery during restart. If transaction is successfully committed, then, by definition, all
its modified pages must be on disk. Why not use it as a standard buffer management policy?
Because of “hotspot” pages. The force policy simplifies restart, because no work needs to be
done for transactions that committed before the crash – it avoids REDO. The price for that is
significantly more

I/O for frequently modified pages. Another drawback is that a transaction will not be completed
before the last write has been executed successfully, and the response time may be increased
significantly as a consequence. With no-force policy, the only synchronous write operation goes
to the log, and the volume of data to be written is usually about two orders of magnitude less.

Algorithm for Recovery and Isolation

Exploiting Semantics (ARIES)
Algorithm for Recovery and Isolation Exploiting Semantics (ARIES) is based on the Write
Ahead Log (WAL) protocol.

Every update operation writes a log record which is one of the following :
 Database Management System

1. Undo-only log record:

Only the before image is logged. Thus, an undo operation can be done to retrieve the old
data.
2. Redo-only log record:
Only the after image is logged. Thus, a redo operation can be attempted.
3. Undo-redo log record:
Both before images and after images are logged.

In it, every log record is assigned a unique and monotonically increasing log sequence
number (LSN). Every data page has a page LSN field that is set to the LSN of the log record
corresponding to the last update on the page. WAL requires that the log record corresponding
to an update make it to stable storage before the data page corresponding to that update is
written to disk. For performance reasons, each log write is not immediately forced to disk. A
log tail is maintained in main memory to buffer log writes. The log tail is flushed to disk
when it gets full. A transaction cannot be declared committed until the commit log record
makes it to disk.

Once in a while the recovery subsystem writes a checkpoint record to the log. The
checkpoint record contains the transaction table and the dirty page table. A master log record
is maintained separately, in stable storage, to store the LSN of the latest checkpoint record
that made it to disk. On restart, the recovery subsystem reads the master log record to find
the checkpoint’s LSN, reads the checkpoint record, and starts recovery from there on.

The recovery process actually consists of 3 phases:

1. Analysis:
The recovery subsystem determines the earliest log record from which the next pass must
start. It also scans the log forward from the checkpoint record to construct a snapshot of
what the system looked like at the instant of the crash.
2. Redo:
Starting at the earliest LSN, the log is read forward and each update redone.
3. Undo:
The log is scanned backward and updates corresponding to loser transactions are undone.

Database security

Database security is the technique that protects and secures the database against intentional or
accidental threats. Security concerns will be relevant not only to the data resides in an
organization's database: the breaking of security may harm other parts of the system, which may
 Database Management System

ultimately affect the database structure. Consequently, database security includes hardware parts,
software parts, human resources, and data. To efficiently do the uses of security needs
appropriate controls, which are distinct in a specific mission and purpose for the system. The
requirement for getting proper security while often having been neglected or overlooked in the
past days; is now more and more thoroughly checked by the different organizations.
We consider database security about the following situations:

 Theft and fraudulent.

 Loss of confidentiality or secrecy.
 Loss of data privacy.
 Loss of data integrity.
 Loss of availability of data.

These listed circumstances mostly signify the areas in which the organization should focus on
reducing the risk that is the chance of incurring loss or damage to data within a database. In some
conditions, these areas are directly related such that an activity that leads to a loss in one area
may also lead to a loss in another since all of the data within an organization are interconnected.

What is a Threat?

Any situation or event, whether intentionally or incidentally, can cause damage, which can
reflect an adverse effect on the database structure and, consequently, the organization. A threat
may occur by a situation or event involving a person or the action or situations that are probably
to bring harm to an organization and its database.
The degree that an organization undergoes as a result of a threat's following which depends upon
some aspects, such as the existence of countermeasures and contingency plans. Let us take an
example where you have a hardware failure that occurs corrupting secondary storage; all
processing activity must cease until the problem is resolved.

Computer-Based Controls

The different forms of countermeasure to threats on computer systems range from physical
controls to managerial procedures. In spite of the range of computer-based controls that are
preexisting, it is worth noting that, usually, the security of a DBMS is merely as good as that of
the operating system, due to the close association among them.
 Database Management System

Most of the computer-based database security are listed below:

 Access authorization.
 Access controls.
 Views.
 Backup and recovery of data.
 Data integrity.
 Encryption of data.
 RAID technology.

What is Access Controls?

The usual way of supplying access controls to a database system is dependent on the granting
and revoking of privileges within the database. A privilege allows a user to create or access some
database object or to run some specific DBMS utilities. Privileges are granted users to achieve
the tasks required for those jobs.
The database provides various types of access controls:

 Discretionary Access Control (DAC)

 Mandatory Access Control (MAC)

Backup and Recovery

Every Database Management System should offer backup facilities to help with the recovery of a
database after a failure. It is always suitable to make backup copies of the database and log files
at the regular period and for ensuring that the copies are in a secure location. In the event of a
failure that renders the database unusable, the backup copy and the details captured in the log file
are used to restore the database to the latest possible consistent state.

Difference Between Security and Integrity

The key difference between Security and Integrity is that Data security is the protection of
data from unauthorized users. Data integrity means that the data contained in the database is
both correct and consistent. For this purpose, the data stored in the database must satisfy certain
types of constraints (rules).
 Database Management System

Comparison Chart

Security Integrity

Data security defines the prevention of data Data integrity defines the quality of data,
corruption through the use of controlled access which guarantees the data is complete and
mechanisms. has a whole structure.

Data security deals with the protection of data Data integrity deals with the validity of data

Data security is making sure only the people who Data integrity is making sure the data is
should have access to the data are the correct and not corrupt.
only ones who can access the data.

Data security refers to making sure that data is Data integrity refers to the structure of the
accessed by its intended users, thus data and how it matches the schema of the
ensuring the privacy and protection of data. database.

Authentication/authorization, encryptions and Backing up, designing a suitable user

masking are some of the popular means of data interface
security. and error detection/correction in data are
some of the means to preserve integrity.

Data security

 Data security is the protection of data from unauthorized users.

 Only the authorized users are allowed to access the data
 Most of the users are allowed to access a part of the database i.e., the data that is
related to them or related to their department.
 Mostly, the DBA or head of department can access all the data in the database.
 Database Management System

 Some users may be permitted only to retrieve data, whereas others are allowed to
retrieve as well as to update data.
 The database access is controlled by the DBA.
 He/she creates the accounts of users and gives rights to access the database.
 Users or group of users are given usernames protected by passwords.
 The user enters his/her account number (or user name) and password to access the
data from the database.
 For example, if you have an account in the “[Link]”, then you have to give your
correct username and password to access your account or e-mail.
 Similarly, when you insert your ATM card into the Automated Teller Machine
(ATM), the machine reads your ID number printed on the card and then asks you to
enter your pin code (or password). In this way, you can access your account.

Data Integrity

 Data integrity means that the data contained in the database is both correct and
consistent. For this purpose, the data stored in the database must satisfy certain types
of constraints (rules)
 For example, a balance for any account must not be less than zero. Such constraints
are enforced in the system by adding appropriate code in application programs. But,
when new constraints are added, such as balance should not be less than Rs. 5000,
application programs need to be changed. But, it is not an easy task to change
programs whenever required.
 Data in a database must be correct and consistent.
 So, data stored in the database must satisfy certain types of constraints (rules).
 DBMS provides different ways to implement such type of constraints (rules).
 This improves data integrity in a database.

Authorization
 Database Management System

Authorization is a privilege provided by the Database Administer. Users of the database can only
view the contents they are authorized to view. The rest of the database is out of bounds to them.
The different permissions for authorizations available are:

 Primary Permission - This is granted to users publicly and directly.

 Secondary Permission - This is granted to groups and automatically awarded to a user
if he is a member of the group.
 Public Permission - This is publicly granted to all the users.
 Context sensitive permission - This is related to sensitive content and only granted to a
select users.
The categories of authorization that can be given to users are:

 System Administrator - This is the highest administrative authorization for a user.

Users with this authorization can also execute some database administrator commands
such as restore or upgrade a database.
 System Control - This is the highest control authorization for a user. This allows
maintenance operations on the database but not direct access to data.
 System Maintenance - This is the lower level of system control authority. It also allows
users to maintain the database but within a database manager instance.
 System Monitor - Using this authority, the user can monitor the database and take
snapshots of it.

ANOMALIES
An anomaly is an irregularity, or something which deviates from the expected or normal state.
When designing databases, we identify three types of anomalies: Insert, Update and Delete.
Insertion Anomaly in Referencing Relation:
o We can’t insert a row in REFERENCING RELATION if referencing attribute’s value is
not present in referenced attribute value.
o e.g.; Insertion of a student with BRANCH_CODE ‘ME’ in STUDENT relation will
result in error because ‘ME’ is not present in BRANCH_CODE of BRANCH.
Deletion/ Updation Anomaly in Referenced Relation:
o We can’t delete or update a row from REFERENCED RELATION if value of
REFRENCED ATTRIBUTE is used in value of REFERENCING ATTRIBUTE.
o e.g; if we try to delete tuple from BRANCH having BRANCH_CODE ‘CS’, it will result
in error because ‘CS’ is referenced by BRANCH_CODE of STUDENT, but if we try to
delete the row from BRANCH with BRANCH_CODE CV, it will be deleted as the value
is not been used by referencing relation.
o It can be handled by following method:
ON DELETE CASCADE:
 Database Management System

o It will delete the tuples from REFERENCING RELATION if value used by

REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION.
o e.g;, if we delete a row from BRANCH with BRANCH_CODE ‘CS’, the rows in
STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be
deleted.
ON UPDATE CASCADE:
o It will update the REFERENCING ATTRIBUTE in REFERENCING RELATION if
attribute value used by REFERENCING ATTRIBUTE is updated in REFERENCED
RELATION.
e.g;, if we update a row from BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the rows in
STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be updated
with BRANCH_CODE ‘CSE’.