PRESENTATION

Concurrency Control Technique

CONCURRENCY CONTROL TECHNIQUE

Concurrency Control:
Concurrency control concept comes under the Transaction in database management system
(DBMS). It is a procedure in DBMS which helps us for the management of two simultaneous
processes to execute without conflicts between each other, these conflicts occur in multi user
systems.
Concurrency can simply be said to be executing multiple transactions at a time. It is required to
increase time efficiency. If many transactions try to access the same data, then inconsistency
arises. Concurrency control required to maintain consistency data.
Example:
If we take ATM machines and do not use concurrency, multiple persons cannot draw
money at a time in different places. This is where we need concurrency.
CONCURRENCY CONTROL TECHNIQUE

Concurrency Control deals with interleaved execution of more than one transaction.

Transaction:
A set of logically related operations is known as a transaction. The main operations of a transaction
are:
Read(A):
 Read operations Read(A) or R(A) reads the value of A from the database and stores it in a
buffer in the main memory.
Write (A): Write operation Write(A) or W(A) writes the value back to the database from the
buffer. 
(It doesn’t always need to write it to a database back it just writes the changes to buffer this is the
reason where dirty read comes into the picture) 
CONCURRENCY CONTROL TECHNIQUE

Let us take a debit transaction from an account that consists of the following operations:
R(A);
A=A-1000;
W(A);
 Assume A’s value before starting the transaction is 5000.
 The first operation reads the value of A from the database and stores it in a buffer.
 The Second operation will decrease its value by 1000. So buffer will contain 4000.
 The Third operation will write the value from the buffer to the database. So A’s final value will be
4000.
CONCURRENCY CONTROL TECHNIQUE

But it may also be possible that the transaction may fail after executing some of its operations. The
failure can be because of hardware, software or power, etc. For example, if the debit transaction
discussed above fails after executing operation 2, the value of A will remain 5000 in the database
which is not acceptable by the bank. To avoid this, Database has two important operations: 
Commit: 
After all instructions of a transaction are successfully executed, the changes made by a
transaction are made permanent in the database.
Rollback:
  If a transaction is not able to execute all operations successfully, all the changes made by a
transaction are undone.
CONCURRENCY CONTROL TECHNIQUE

Properties of a transaction:
Atomicity: 
As a transaction is a set of logically related operations, either all of them should be
executed or none. A debit transaction discussed above should either execute all three operations
or none. If the debit transaction fails after executing operations 1 and 2 then its new value of
4000 will not be updated in the database which leads to inconsistency.
Consistency: 
If operations of debit and credit transactions on the same account are executed
concurrently, it may leave the database in an inconsistent state.
For Example, with T1 (debit of Rs. 1000 from A) and T2 (credit of 500 to A) executing
concurrently, the database reaches an inconsistent state.
CONCURRENCY CONTROL TECHNIQUE

Let us assume the Account balance of A is Rs. 5000. T1 reads A(5000) and stores the value in its
local buffer space. Then T2 reads A(5000) and also stores the value in its local buffer space.
T1 performs A=A-1000 (5000-1000=4000) and 4000 is stored in T1 buffer space. Then T2
performs A=A+500 (5000+500=5500) and 5500 is stored in the T2 buffer space. T1 writes the
value from its buffer back to the database.
A’s value is updated to 4000 in the database and then T2 writes the value from its buffer back to the
database. A’s value is updated to 5500 which shows that the effect of the debit transaction is lost
and the database has become inconsistent.
To maintain consistency of the database, we need concurrency control protocols .
CONCURRENCY CONTROL TECHNIQUE

Isolation: 
The result of a transaction should not be visible to others before the transaction is
committed. For example, let us assume that A’s balance is Rs. 5000 and T1 debits Rs. 1000 from
A. A’s new balance will be 4000. If T2 credits Rs. 500 to A’s new balance, A will become 4500,
and after this T1 fails. Then we have to roll back T2 as well because it is using the value
produced by T1. So transaction results are not made visible to other transactions before it
commits.
Durable: 
Once the database has committed a transaction, the changes made by the transaction
should be permanent. e.g.; If a person has credited $500000 to his account, the bank can’t say
that the update has been lost. To avoid this problem, multiple copies of the database are stored at
different locations.
CONCURRENCY CONTROL TECHNIQUE

Schedule:
A schedule is a series of operations from one or more transactions.
A schedule can be of two types: 
Serial Schedule:
 When one transaction completely executes before starting another
transaction, the schedule is called a serial schedule. A serial schedule is always
consistent. e.g.; If a schedule S has debit transaction T1 and credit transaction T2,
possible serial schedules are T1 followed by T2 (T1->T2) or T2 followed by T1 ((T2-
>T1). A serial schedule has low throughput and less resource utilization.
CONCURRENCY CONTROL TECHNIQUE

Concurrent Schedule: 
When operations of a transaction are interleaved with operations of other
transactions of a schedule, the schedule is called a Concurrent schedule. e.g.; the Schedule of
debit and credit transactions shown in Table 1 is concurrent. But concurrency can lead to
inconsistency in the database.
 Consider the following transaction involving two bank accounts x and y: 
read(x);
x := x – 50;
write(x);
read(y);
y := y + 50;
write(y);
  
CONCURRENCY CONTROL TECHNIQUE

The constraint that the sum of the accounts x and y should remain constant is that of? 
 Atomicity
 Consistency
 Isolation
 Durability
Solution: 
As discussed in properties of transactions, consistency properties say that sum of
accounts x and y should remain constant before starting and after completion of a transaction. So,
the correct answer is B.   
CONCURRENCY CONTROL TECHNIQUE

Advantages of Concurrency:
In general, concurrency means, that more than one transaction can work on a system.
The advantages of a concurrent system are:
Waiting Time: 
It means if a process is in a ready state but still the process does not get the system
to get execute is called waiting time. So, concurrency leads to less waiting time.
Response Time: 
The time wasted in getting the response from the cpu for the first time, is called
response time. So, concurrency leads to less Response Time.
CONCURRENCY CONTROL TECHNIQUE

Resource Utilization: 
The amount of Resource utilization in a particular system is called Resource
Utilization. Multiple transactions can run parallel in a system. So, concurrency leads to more
Resource Utilization.
Efficiency:
 The amount of output produced in comparison to given input is called efficiency. So,
Concurrency leads to more Efficiency.
Main problems in using Concurrency
The problems which arise while using concurrency are as follows −
Updates will be lost :
One transaction does some changes and another transaction deletes that
change. One transaction nullifies the updates of another transaction.
CONCURRENCY CONTROL TECHNIQUE

Uncommitted Dependency or dirty read problem :

On variable has updated in one transaction, at the same time another transaction has started
and deleted the value of the variable there the variable is not getting updated or committed that
has been done on the first transaction this gives us false values or the previous values of the
variables this is a major problem.
Inconsistent retrievals :
One transaction is updating multiple different variables, another transaction is in a process to
update those variables, and the problem occurs is inconsistency of the same variable in different
instances.
CONCURRENCY CONTROL TECHNIQUE

Concurrency control techniques

The concurrency control techniques are as follows −
Locking:
Lock guaranties exclusive use of data items to a current transaction. It first accesses the data
items by acquiring a lock, after completion of the transaction it releases the lock.
Types of Locks:
The types of locks are as follows −
Shared Lock [Transaction can read only the data item values]
Exclusive Lock [Used for both read and write data item values]
CONCURRENCY CONTROL TECHNIQUE

Time Stamping
Time stamp is a unique identifier created by DBMS that indicates relative starting time of a
transaction. Whatever transaction we are doing it stores the starting time of the transaction and
denotes a specific time.
This can be generated using a system clock or logical counter. This can be started whenever a
transaction is started. Here, the logical counter is incremented after a new timestamp has been
assigned
CONCURRENCY CONTROL TECHNIQUE

Optimistic
It is based on the assumption that conflict is rare and it is more efficient to allow transactions to
proceed without imposing delays to ensure serializability.