Transcript 486 Advanced SQL

Concurrency Control
Overview of Database
Concurrency Control
BACS 485—Database Management
Concurrency Control
Lecture Objectives




Learn the basic concepts associated with
concurrency control in a relational database
environment.
Learn about serial and serializable schedules.
Learn the basic concurrency control protocols
Learn the basics of locks and timestamps as
there are used in concurrency control.
BACS 485—Database Management
Concurrency Control
Concurrency Background



Modern databases gain their usefulness
by sharing data among many users.
The cost of sharing data is concurrency
problems.
Modern DBMSs deal with concurrency
problems by controlling the interaction
among users to maintain database
consistency (integrity).
BACS 485—Database Management
Concurrency Control
Concurrency Background

There are 5 basic background concepts
related to concurrency control:





Multiprogramming
Data Sharing
Concurrent access vs. integrity conflict
Transactions
Levels of concurrency control
BACS 485—Database Management
Concurrency Control
Multiprogramming


Multiprogramming is the process of
interleaving (shuffling) the operations
of several programs in order to better
utilize the system resources.
By quickly switching “contexts”, the
illusion of simultaneous execution is
possible on a single CPU computer.
BACS 485—Database Management
Concurrency Control
Data Sharing



Database systems share data to reduce
redundancy and gain better control through
centralization.
Shared data that is dynamic (i.e., update,
insert, delete) causes concurrency problems
in database environments.
Strictly “read only” shared data does not
cause concurrency problems.
BACS 485—Database Management
Concurrency Control
Concurrency vs. Integrity




There is a conflict between total
concurrent data access and database
integrity.
The more concurrency that takes place,
the potentially worse the integrity.
Conversely, the better the integrity, the
less concurrency is possible.
The goal is to achieve a reasonable
balance.
BACS 485—Database Management
Concurrency Control
Transactions



Transactions are a logical unit of work
in the database.
By executing transactions, the database
moves from one consistent state to
another.
Transactions are important is several
areas of database theory (including
concurrency).
BACS 485—Database Management
Concurrency Control
Transactions

Good transactions have the following
characteristics:





Atomic – performed totally or not at all
Consistency preservation – consistent state is maintained
before and after the transaction is executed.
Permanence – Changes that are “committed” are
permanent. They cannot be lost.
Isolation – Transaction updates should not be visible
outside of the transaction.
Serializability – Running interleaved transactions should
produce the same result as running them in some serial
order.
BACS 485—Database Management
Concurrency Control
Levels of Control


The concurrency mechanisms that will be
discussed in this slide set apply to multiple
levels of the database.
Specifically, they can apply to:





Data-item level
Tuple level
Table level
Extent level
Full database
BACS 485—Database Management
Concurrency Control
Uncontrolled Concurrency


Uncontrolled concurrency implies that
the transactions are interleaved
randomly.
This causes 3 classic problems:



Lost update problem
Temporary update problem
Incorrect summary problem
BACS 485—Database Management
Concurrency Control
Lost Update Problem
T1
|
T2
_____________________________________|____________________________
R E A D ch eckin g-ba la n ce
|
B a la n ce = B a la n ce - 50
|
|
R E A D ch eckin g-ba la n ce
|
B a la n ce = B a la n ce + 100
W R IT E ch eckin g-ba la n ce
|
R E A D sa vin gs-ba la n ce
|
|
W R IT E ch eckin g-ba la n ce < -T 1 upda te
B a la n ce = B a la n ce + 50
|
lost
W R IT E sa vin gs-ba la n ce
|
BACS 485—Database Management
Concurrency Control
Temporary Update Problem
T1
|
T2
_________________________________|____________________________
R E A D ch eckin g-balan ce
|
B alan ce = B alan ce - 50
|
W R IT E ch eckin g-balan ce
|
|
R E A D ch eckin g-balan ce
|
B alan ce = B alan ce + 100
|
W R IT E ch eckin g-balan ce
R E A D savin gs-balan ce
|
T 1 fails an d m ust be restored
-->
BACS 485—Database Management
Concurrency Control
Incorrect Summary Problem
T1
|
T2
________________________________|____________________________
|
Sum = 0
|
READ check-balance-1
|
Sum = Sum + Balance
| ...
READ checking-balance-5
|
Balance = Balance - 50
|
WRITE check-balance-5
|
|
READ check-balance-5
|
Sum = Sum + Balance
|
READ check-balance-6
|
Sum = Sum + Balance
READ checking-balance-6
|
Balance = Balance + 50
|
WRITE check-balance-6
|
BACS 485—Database Management
Concurrency Control
How do you avoid these problems?




You can avoid the problems of uncontrolled
concurrency by generating “serializable
schedules.”
A “schedule” is a plan for transaction execution.
A “serial schedule” is one where there is no
concurrency (one transaction after another).
A serializable schedule is one that allows
instruction interleaving while still giving serial
schedule results.
BACS 486—Advanced Database Management
Concurrency Control
Schedules assume…





Transactions are individually correct
If database consistent (i.e., correct) before a
transaction, it will be consistent after
Schedules preserve instruction order
A serial execution of several transactions is
“correct” by definition
Interleaved execution is “correct” it is
equivalent to any serial execution (called a
“serializable schedule”)
BACS 486—Advanced Database Management
Concurrency Control
Schedule Example
Example: T1 transfers $50 from checking to savings while T2 takes 10% of checking
and puts it into savings. Initial value checking $1000, savings $2000.
T1
READ checking-balance
Balance = Balance - 50
WRITE checking-balance
READ savings-balance
Balance = Balance + 50
WRITE savings-balance
T2
READ checking-balance
Temp = Balance * 10%
Balance = Balance - Temp
WRITE checking-balance
READ savings-balance
Balance = Balance + Temp
WRITE savings-balance
BACS 486—Advanced Database Management
Concurrency Control
Schedule Example
SCHEDULE 1:
T1
|
T2
________________________|____________________________
READ checking-balance
|
Balance = Balance - 50
|
WRITE checking-balance
|
READ savings-balance
|
Balance = Balance + 50
|
WRITE savings-balance
|
| READ checking-balance
| Temp = Balance * 10%
| Balance = Balance - Temp
| WRITE checking-balance
| READ savings-balance
| Balance = Balance + Temp
| WRITE savings-balance
Checking Value = $855
Savings Value = $2145
BACS 486—Advanced Database Management
Concurrency Control
Schedule Example
SCHEDULE 2:
T1
|
T2
________________________|____________________________
| READ checking-balance
| Temp = Balance * 10%
| Balance = Balance - Temp
| WRITE checking-balance
| READ savings-balance
| Balance = Balance + Temp
| WRITE savings-balance
READ checking-balance
|
Balance = Balance - 50
|
WRITE checking-balance
|
READ savings-balance
|
Balance = Balance + 50
|
WRITE savings-balance
|
Checking Value = $850
Savings Value = $2150
BACS 486—Advanced Database Management
Concurrency Control
Locks and Protocols

Types of simple Locks




Exclusive Locks
Shared Locks (read locks)
Update Locks
Lock Problems


Deadlock
Livelock
BACS 486—Advanced Database Management
Concurrency Control