Database Concurrency

ADVANCE
DATABASE
SYSTEMS
DEVELOPMENT
[CC6001]
WEEK - 06
Database Concurrency
There are three sides of ACID.
ENHANCED LONG TERM MEMORY
DECREASED SHORT TERM MEMORY
AND I FORGOT THE THIRD
- TIMOTHY LARY
Requirement
MOST OF THE
CASES, DATABASE IS
ACCESSED BY
MULTIPLE USERS AT A
TIME
Concurrency Control
ENSURES THAT
CONCURRENT
OPERATIONS ARE
CARRIED OUT
 CORRECTLY
 EFFICIENTLY
Example
 Begin Transaction T1
 Read Balance1
 Balance1 = Balance1 – 100
 If Balance <0
 Print insufficient fund
 Abort T1
 End
 Write Balance1
 Read Balance2
 Balance2 = Balance2 +100
 Write Balance2
 Commit T1
Points to Remember
Effect of ABORT is to ROLLBACK the

TRANSACTION and UNDO changes it has made on
the DATABASE
In this example, TRANSACTION was NOT written

to the DATABASE prior to ABORT hence, NO
UNDO is necessary
Yet Another Scenario
Transactions TA and TB performs a series of

processing (sub-tasks) TA1, TA2, TA3 and TB1,
TB2, TB3 respectively.
CPU is shared by TA and TB and other

transactions in the system
Yet Another Scenario
Single Multi
Transaction Transaction
Environment Environment
Interleaved Transactions
The operations of the two transactions TA and TB are
said to be interleaved to achieve concurrent execution.
The operations of the two transactions TA and TB are

said to be interleaved to achieve concurrent execution.
Various concurrency problems may occur if proper control is not enforced!!!
Concurrency Problems
Lost Update
Violation of Integrity
Constraints
Inconsistent Retrieval
Lost Update
two concurrent transactions, say TA and TB, are
allowed to update an uncommitted change on the
same data item, say x.
The earlier update of x by Transaction TA is

overwritten by a subsequent update of x by
transaction TB.
Consequently, the update value of x by TA is lost

after the update by TB.
Lost Update - Example
Lost Update - Question
What should be the correct

balance after TA and TB?
Lost Update - Answer
400
Violation of Integrity Constraints
Integrity Constraint - Question
What should be the correct

balance after TA and TB?
Integrity Constraint - Answer
200
TA is summing up the total number of vacant teaching rooms in 3

building (Tower, Moorgate and Eden), whilst TB is moving 2
classes (2 rooms) from Tower to Eden.
Initial numbers of vacant rooms:

Tower: 10
Moorgate: 15
Eden 5
Total number 30
 Initial numbers of vacant rooms:
 Tower: 10
 Moorgate: 15
 Eden 5
 Correct numbers of vacant rooms after moving should be:
 Tower: 8 (10 – 2)
 Moorgate: 15
 Eden 7 (5+2)
 But we seem to have got 32 vacant rooms now!! WRONG
TOTAL!
Concurrency Control - Principles
Schedule multiple Maximize the

transactions in such a way degree of
as to avoid any concurrency or
interference between parallelism in the
them, while at the same system
time
Solutions to Concurrency Problems
Serialization
Constraints
must be
specified by
DBA using
Concurrency Control
appropriate
language
Transaction Scheduling
Serialization
ONE
Process Disadvantage
TRANSACTI
ON MUST
• Permits SERIAL • Inefficient for COMMIT
EXECUTION of MULTI USER
TRANSACTIONS environment BEFORE
ANOTHER
CAN START.
Concurrency Control
Process Mechanisms
• Allows CONCURRECT • Locking

EXECUTION of • Optimistic Scheduling
transactions in a • Time Stamping
CONTROLLED way.
Locking
Locking method is the most commonly

used approach to ensure serializability
of concurrent transactions.
Locking synchronizes interleaved

transactions so that it is
equivalent to some serial
execution of those transactions.
Locking - Idea
When a transaction requires the assurance that

some data will not change during the course of
processing (read/write) it, this transaction must
request and acquire a lock, in order to prevent
other transactions updating the data.
Lock Types
Exclusive Shared
• X (Write) • S (Read)
• Grant read/write access to a • Grant read only access to a
data item to the transaction data item to the transaction
which holds the lock which holds the lock
• Prevent any other transactions • Prevent any transaction
reading or writing the same writing data item.
data item. • Several transactions may hold
a S lock on the same data
item.
Locking Protocol
 A transaction must get an S lock on a data item before it wishes to READ it
 A transaction must get an X lock on a data item before it wishes to WRITE it
 A TRANSACTION already holding an S lock on a data item can promote
itself to an X lock in order to WRITE it, provided there is no other transaction
also holding an S lock on it already.
 If a lock request is denied, the transaction goes into a WAIT state
 A transaction in a WAIT state resumes operations only when the requested
lock is released by another transaction
 X locks are held until COMMIT/ROLLBACK. S locks are normally the same.
Lock Compatibility Matrix
 A transaction may be granted a lock on an item if

the requested lock is compatible with locks
already held on the item by other transactions
 Any number of transactions can hold shared locks

on an item
 But if any transaction holds an exclusive on the item
no other transaction may hold any lock on the item.
 If a lock cannot be granted, the requesting

transaction is made to wait till all incompatible
locks held by other transactions have been
released. The lock is then granted.
Example
 Locking as above is not sufficient to
guarantee serializability if A and B
get updated in-between the read of
A and B, the displayed sum would be
wrong.
 A locking protocol is a set of rules
followed by all transactions while
requesting and releasing locks.
Locking protocols restrict the set of
possible schedules.
Two Phased Locking
Before operating on
any object (a tuple), a
transaction must
acquire a lock on that
object If all transactions obey
Protocol Theorem the “two–phased
locking protocol”, then
all possible interleaved
schedules are
After releasing a lock a serializable
transaction must never
go on to acquire any
more locks.
Lost Update – Example Revisited
Lost Update – Example Revisited
 neither transaction can update balance as both are waiting

for other to release the lock on balance
Relational Integrity – Example Revisited
Relational Integrity – Example Revisited
CORRECT ANSWER
Thank You

Database Concurrency

Uploaded by

Copyright:

Available Formats

Database Concurrency

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Concurrency

Uploaded by

Copyright:

Available Formats

ADVANCE

Effect of ABORT is to ROLLBACK the

In this example, TRANSACTION was NOT written

Transactions TA and TB performs a series of

CPU is shared by TA and TB and other

The operations of the two transactions TA and TB are

The earlier update of x by Transaction TA is

Consequently, the update value of x by TA is lost

What should be the correct

What should be the correct

TA is summing up the total number of vacant teaching rooms in 3

Initial numbers of vacant rooms:

Schedule multiple Maximize the

• Allows CONCURRECT • Locking

Locking method is the most commonly

Locking synchronizes interleaved

When a transaction requires the assurance that

 A transaction may be granted a lock on an item if

 Any number of transactions can hold shared locks

 If a lock cannot be granted, the requesting

 neither transaction can update balance as both are waiting

You might also like