cis 720
DESCRIPTION
CIS 720. Concurrency Control. Locking. Atomic statement Can be used to perform two or more updates atomically Th1: …. < x = x + 1; y = z>;……. Th2:………….;……. Transactions. A database system is a set of shared data objects - PowerPoint PPT PresentationTRANSCRIPT
CIS 720
Concurrency Control
Locking
• Atomic statement– Can be used to perform two or more updates
atomicallyTh1: …. < x = x + 1; y = z>;…….Th2:………….<m = m + 1;….>;…….
Transactions
• A database system is a set of shared data objects
• A transaction is a sequential program which accesses data objects in the database
• Each transaction is a sequence of read and write operations
The Transaction Model
• Examples of primitives for transactions.
Primitive Description
BEGIN_TRANSACTION Make the start of a transaction
END_TRANSACTION Terminate the transaction and try to commit
ABORT_TRANSACTION Kill the transaction and restore the old values
READ Read data from a file, a table, or otherwise
WRITE Write data to a file, a table, or otherwise
Transactions
• Each transaction is a sequence of read and write operations
• The read set of transaction T, denoted by rs(t), is a set of variables read by T.
• The write set ws(T) is defined similarly
Banking SystemDeposit(amount, account)
{ x = db.account; x = x + amount; db.account = x; }
Withdraw(amount, account) { y = db.account;
if y > amount y = y - amount;
db.account = y; }
Distributed Transactions
BEGIN_TRANSACTION reserve MCI -> JFK; reserve JFK -> FRK;END_TRANSACTION
• A database has an invariant I (integrity constraint).• Each transaction is designed to preserve I• If transactions are executed simultaneously, then
they may interfere and invalidate I. • The task of concurrency control is to preserve I.
Banking SystemDeposit(amount, account)
{ x = db.account; x = x + amount; db.account = x; }
Transaction 1: Deposit $50 in Acc1Transaction 2: Deposit $70 in Acc2
Possible interleavings
T1.x = db.Acc1; T1.x = T1.x + 50 T2.x = db.Acc1; T2.x = T2.x + 70; db.Acc1 = T1.x db.Acc1 = T2.x
Concurrency Control
• General organization of managers for handling transactions.
Concurrency Control• General organization of
managers for handling distributed transactions.
• A schedule is any execution of a set of transaction operations
• Two schedules T1 and T2 are equivalent if - all read operations return the same value in both schedules - the final database state is the same in both
schedules
T1: r1(x)0 w1(x)1T2: r2(y)0 r2(x)1 w2(y)2
T1: r1(x)0 w1(x)1
T1: r1(x)0 w1(x)1
T2: r2(y)0 r2(x)1 w2(y)2
T2: r2(y)0 r2(x)1 w2(y)2
T1: r1(y)0 w1(x)1
T2: w2(y)1 r2(x)1 w2(y)2
T1: r1(y)1 w1(x)1
T2: w2(y)1 r2(x)1 w2(y)2
T1: r1(y)0 w1(x)1
T2: w2(y)1 r2(x)0 w2(y)2
• A serial schedule is a schedule in which transactions execute one at a time.
• We know that a serial schedule preserves IC of the database
• A concurrency control algorithm can restricts the execution so that all schedules are serial.
• A CC ensures that all schedules are equivalent to some serial schedule
• A schedule that is equivalent to a serial schedule is called serializable
Untyped Concurrency control• Assumes that all transactions with intersecting
read and write sets interfere with one another.• How can we determine whether a schedule is
serializable• Let T1,…,Tn be a set of transactions• Define a graph G with transactions as nodes• There is an edge from Ti to Tj if - there exists a read rj(x) which reads from wi(x) - there exists a read ri(x) that occurs before wj(x) - there exists a write wi(x) that occurs before wj(x)
• A graph is serializable if the graph is acyclic
Two-phase Locking
• Obtain a read or write lock before reading or writing a variable respectively.
• rl(x): read lock operation• ul(x): unlock operation• wl(x): write lock operation
• Locking rules: - two read locks can be given at the same time; read and write lock must be exclusive * conflict table
• Simple locking does not ensure serializability
T1: r1(y)1 w1(x)1
T2: w2(y)1 r2(x)1 w2(y)2
T1: rl(y) r1(y)1 ul(y) wl(x) w1(x)1 ul(x)
T2: wl(y) w2(y)1 ul(y) rl(x) r2(x)1 ul(x) wl(y) w2(y)2 ul(y)
Two phase locking rule
• Locking phase: acquire all locks • Unlocking phase: release all locks• Two-phase locking ensures serializability• It is prone to deadlocks
Two-Phase Locking (1)
• Two-phase locking.
Two-Phase Locking (2)
• Strict two-phase locking.
Writeahead Log
• a) A transaction• b) – d) The log before each statement is executed
x = 0;y = 0;BEGIN_TRANSACTION; x = x + 1; y = y + 2 x = y * y;END_TRANSACTION; (a)
Log
[x = 0 / 1]
(b)
Log
[x = 0 / 1][y = 0/2]
(c)
Log
[x = 0 / 1][y = 0/2][x = 1/4]
(d)
Graph based protocols
• Impose a partial ordering on data items• If d1 d2, then any transaction accessing
both d1 and d2 must first access d1 before d2.
Tree protocol
• Only exclusive locks are allowed• First item to be locked can be any one• Next, a data item can be locked only if the
parent is already locked• Data items can be unlocked at any time• A data item cannot be relocked by a
transaction.
Semantics-based concurrency control
• If transactions T1 and T2 do not interfere then they can be executed concurrently.
• Two operations op1 and op2 do not conflict if they commute (that is, op1; op2 is the same as op2; op1)
Predicate Locking
• Each transaction specifies a predicate as a lock.
• A new transaction can execute if it does not interfere with existing predicate locks