Database Concurrency [307]

Database users access shared data.

Integrity must be preserved.

Transaction - is an execution of a program

• atomicity - a transaction either happens or doesn't
• durability - the results of a transaction are permanent
• consistency - constraints are maintained
• two possible outcomes
  • commit - transaction completes, results permanent
  • abort - transaction failed, no results
    • hardware failure
    • system error
    • bad input data
    • not consistent with constraints

Transactions [308]

Data Entities A,B,C ... (pages, tuples, relations, etc)

Transactions built with

begin - creates private workspace

read(X) - look for X in workspace else on disk

write(X) - write X onto disk

end - end of transaction

But consider two executions of program INCREMENT:

begin read(A); A = A +1; write(A); end

time	T1	T2	A in DB
0	begin		7
1		begin	7
2		read(A)	7
3	read(A)		7
4	A=A+1		7
5		A=A+1	7
6		write(A)	8
7	write(A)		8
8	end		8
9		end	8

Locks [309]

• each data entity has an associated LOCK
• possess lock for entity A before a read(A) or write(A)
• at most one transaction can posses a lock to A at any time
• transaction releases lock when it is not needed - unlock(A)

begin lock(A); read(A); A = A + 1; write(A); unlock(A); end

time	T1	T2	A in DB
0	begin		7
1		begin	7
2		lock(A)	7
3	lock(A)		7
4		read(A)	7
5		A=A+1	7
6		write(A)	8
7		unlock(A)	8
8		end	8
9	read(A)		8
10	A=A+1		8
11	write(A)		9
12	unlock(A)		9
13	end		9

Schedule [310]

• A schedule for a set of transactions is the order in which the elementary steps of the transaction are to be done.
• A schedule is serial if all the steps of each transcation occur consecutively.
• A schedule is serializable if its effect is equivalent to that of some serial schedule.
• A schedule is serializable if there are no cycles in its `unlock-lock' graph.
• The two-phase protocol (all locks precede any unlock) insures serializability

Schedule [311]

Assume A=50,B=40,C=10.

(a) is serial (final values are 40,30,30)

(b) is serializable (final values are 40,30,30)

(c) is not serializable (final values are 40,50,30)

T1	T2
read(A)
A=A-10
write(A)
read(B)
B=B+10
write(B)
	read(B)
	B=B-20
	write(B)
	read(C)
	C=C+20
	write(C)

T1	T2
read(A)
	read(B)
A=A-10
	B=B-20
write(A)
	write(B)
read(B)
	read(C)
B=B+10
	C=C+20
write(B)
	write(C)

T1	T2
read(A)
A=A-10
	read(B)
write(A)
	B=B-20
read(B)
	write(B)
B=B+10
	read(C)
write(B)
	C=C+20
	write(C)

Test for Serializability [312]

Input: Schedule S of transactions with locks

Output: YES if S is serializable

Method:

• create a directed graph
• each transaction is a node
• add an arc $T_{i} \rightarrow T_{j}$ if $T_{i}$ has an unlock(A) and $T_{j}$ has the next lock(A)
• output YES if no cycles

T1	T2	T3
	lock(A)
	unlock(A)
		lock(A)
		unlock(A)
lock(B)
unlock(B)
	lock(B)
	unlock(B)

T1	T2
lock(A)
unlock(A)
	lock(A)
	lock(B)
	unlock(A)
	unlock(B)
lock(B)
unlock(B)

Serializable: $T1 \rightarrow T2 \rightarrow T3$ NOT serializable: $T1 \rightleftharpoons T2$

2-Phase Locking Protocol [313]

• OS does not have advance knowledge of transaction steps
• 2-Phase Locking Protocol insures resultant schedule serializable

• Protocol - All locks must be requested before any unlocks.

Example of 2-phase protocol:

lock(A); lock(B) A = A+1; B= B+3; unlock(B); unlock(A);

Example of not using 2-phase protocol

lock(A); A = A + 1; unlock(A); lock(B); B = B+3; unlock(B);

This is the same transaction as T2 in earlier example.

In the previous case, there was a serializable schedule but that does not guarantee that all of them would have been.

In the dynamic case, any schedule might result. If it follows 2-phase locking protocol, all schedules are serializable, hence equivalent to a serial schedule.

Share Locks [314]

Improved concurrency with

• shared lock - read only, share(A)

  Any # of transactions can hold a share lock at any time

• exclusive lock - read/write, exclusive(A)

  At most one transaction can hold an exclusive lock. No transaction can hold a share lock at the same time.

• unlock(A)

INDEX: INTENTIONAL EMPTY [315]

INDEX: INTENTIONAL EMPTY [316]