Transcript Crucial Confrontations - San Jose State University

Concurrency Control
Chapter 18
Section 18.5
Presented by
Khadke, Suvarna
CS 257
(Section II) Id 213
1
Overview
 Assume knowledge of:
Lock
 Two phase lock
 Lock modes: shared, exclusive, update

 A simple scheduler architecture based on
following principle :
Insert locks for transaction
 Release locks when the transaction manager tells it

2
Scheduler That Inserts Lock Actions
Scheduler That Inserts Lock Actions
 Part I: Select & insert appropriate lock modes to
db operations (read, write, or update)
 Part II: Take actions (a lock or db operation)
from Part I and executes it appropriately.
 Determine the transaction (T) that action belongs
and status of T (delayed or not)
 If T is not delayed then
1. Database access action is transmitted to the
database and executed
4
Scheduler That Inserts Lock Actions
2. If lock action is received, then L Table checks
whether lock can be granted or not
i> Granted, the L Table is modified to include
granted lock
ii>Not G. then update L Table about requested lock
3. When T commits or aborts, Part I is notified by the
transaction manager and releases all locks. If any
transactions are waiting PartI notifies PartII.
4. Part II when notified, determines next transaction
T’ to get lock to continue.
5
The Lock Table
 A relation that associates database elements with
locking information
 Implemented with a hash table using database
elements as the hash key
 Size is proportional to the number of lock elements
only, not to the size of the entire database
DB
element A
Lock
information
for A
6
Lock Table Entries Structure
Group modes: a
summary of the most
stringent conditions
that transaction
requesting a new lock
on A face.
SXU scheme:
-S: only shared locks
-X: one exclusive
lock and no other
locks
- U: one update lock
and zero or
shared locks
- wait : one transaction
7
waiting
Handling Lock Requests
 SL1(A):
GM=S;W=N
 UL2(A):
 GM=U;W=N
 XL3(A):
 GM=U;W=Y

8
Handling Unlock Requests
Now suppose transaction T
unlocks A
 Delete T’s entry on the list
for A
 T’s lock is not the same as
the group mode, no need to
change group mode
 Otherwise check entire list
for new group mode
S: GM(S) or nothing
U: GM(S) or nothing
X: nothing
9
Handling Unlock Requests
 If the value of waiting is “yes" need to grant one or
more locks using following approaches
First-Come-First-Served:
 Grant longest waiting request.
 No starvation (waiting forever for lock)
Priority to Shared Locks:
 Grant all S locks waiting, then one U lock.
 Grant X lock if no others waiting
Priority to Upgrading:
 If there is a U lock waiting to upgrade to an X
lock, grant that first.
10
Reference List
 ULLMAN, J. D., WISDOM J. & HECTOR G., DATABASE SYSTEMS
THE COMPLETE BOOK, 2nd Edition, 2008.
11
Thank You
12