CS计算机代考程序代写 SQL concurrency scheme 2021/4/28 Transaction Isolation

程序代写 CS代考 / concurrency, Scheme代写代考, SQL代写代考

2021/4/28 Transaction Isolation
Transaction Isolation
Transaction Isolation Serializability
Checking Serializability Transaction Isolation Levels Concurrency Control
>>
COMP9315 21T1 ♢ Transaction Isolation ♢ [0/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
1/13

2021/4/28 Transaction Isolation
∧ >>
❖ Transaction Isolation Simplestformofisolation:serialexecution (T1;T2;T3
; …)
Problem: serial execution yields poor throughput.
Concurrency control schemes (CCSs) aim for “safe” concurrency
Abstract view of DBMS concurrency mechanisms:
COMP9315 21T1 ♢ Transaction Isolation ♢ [1/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
2/13

2021/4/28 Transaction Isolation
<< ∧ >>
❖ Serializability
Consider two schedules S1 and S2 produced by
executing the same set of transactions T1..Tn concurrently
but with a non-serial interleaving of R/W operations
S1 and S2 are equivalent if StateAfter(S1) = StateAfter(S2)
i.e. nal state yielded by S1 is same as nal state yielded by S2
S is a serializable schedule (for a set of concurrent tx’s T1 ..Tn) if
S is equivalent to some serial schedule Ss of T1 ..Tn
Under these circumstances, consistency is
guaranteed
(assuming no aborted transactions and no system failures)
COMP9315 21T1 ♢ Transaction Isolation ♢ [2/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
3/13

2021/4/28 Transaction Isolation
❖ Serializability (cont)
Two formulations of serializability:
conict serializibility
i.e. conicting R/W operations occur in the “right order”
check via precedence graph; look for absence of cycles
view serializibility
i.e. read operations see the correct version of data
checked via VS conditions on likely equivalent schedules
View serializability is strictly weaker than conict serializability.
COMP9315 21T1 ♢ Transaction Isolation ♢ [3/11]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
4/13

2021/4/28 Transaction Isolation
❖ Checking Serializability Conict serializablility checking:
make a graph with just nodes, one for each Ti
for each pair of operations across transactions {
if (Ti and Tj have conflicting ops on variable X) {
put a directed edge between Ti and Tj
where the direction goes from
first tx to access X to second tx to access X
if this new edge forms a cycle in the graph
<< ∧ >>
return “Not conflict serializable”
return “Conflict serializable”
COMP9315 21T1 ♢ Transaction Isolation ♢ [4/11]
} }
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
5/13

2021/4/28 Transaction Isolation
<< ∧ >> ❖ Checking Serializability (cont)
View serializability checking:
// TC,i denotes transaction i in concurrent schedule
for each serial schedule S {
// TS,i denotes transaction i in serial schedule
for each shared variable X {
if TC,i reads same version of X as TS,i
(either initial value or value written by Tj)
continue
else
give up on this serial schedule
if TC,i and TS,i write the final version of X
continue
else
give up on this serial schedule
}
return “View serializable”
}
return “Not view serializable”
COMP9315 21T1 ♢ Transaction Isolation ♢ [5/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
6/13

2021/4/28 Transaction Isolation
<< ∧ >> ❖ Transaction Isolation Levels
SQL programmers’ concurrency control mechanism …
set transaction
read only — so weaker isolation may be ok read write — suggests stronger isolation needed
isolation level
— weakest isolation, maximum concurrency
read uncommitted
read committed
repeatable read
serializable
— strongest isolation, minimum concurrency
Applies to current tx only; affects how scheduler treats this tx.
COMP9315 21T1 ♢ Transaction Isolation ♢ [6/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
7/13

2021/4/28
Transaction Isolation
<< ∧ >> ❖ Transaction Isolation Levels (cont)
Implication of transaction isolation levels:
Isolation Dirty Level Read
Read Uncommitted
Nonrepeatable Read
Possible Possible Not Possible Not Possible
Phantom Read
Possible
Possible
Possible
Not Possible
Read Not Committed Possible
Repeatable Not Read Possible
Serializable Not Possible
COMP9315 21T1 ♢ Transaction Isolation ♢ [7/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
8/13
Possible

2021/4/28 Transaction Isolation
<< ∧ >> ❖ Transaction Isolation Levels (cont)
For transaction isolation, PostgreSQL
provides syntax for all four levels
treats read uncommitted as read committed repeatable read behaves like serializable default level is read committed
Note: cannot implement read uncommitted because of MVCC
For more details, see PostgreSQL Documentation section 13.2
extensive discussion of semantics of UPDATE, INSERT, DELETE
COMP9315 21T1 ♢ Transaction Isolation ♢ [8/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
9/13

2021/4/28 Transaction Isolation
<< ∧ >> ❖ Transaction Isolation Levels (cont)
A PostgreSQL tx consists of a sequence of SQL statements:
BEGIN S1; S2; … Sn; COMMIT;
Isolation levels affect view of DB provided to each Si: in read committed …
each Si sees snapshot of DB at start of Si
in repeatable read and serializable …
each Si sees snapshot of DB at start of tx
serializable checks for extra conditions
Transactions fail if the system detects violation of isolation level.
COMP9315 21T1 ♢ Transaction Isolation ♢ [9/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
10/13

2021/4/28 Transaction Isolation
<< ∧ >> ❖ Transaction Isolation Levels (cont)
Example of repeatable read vs serializable table R(class,value) containing (1,10) (1,20)
(2,100) (2,200)
T1: X = sum(value) where class=1; insert R(2,X); commit
T2: X = sum(value) where class=2; insert R(1,X); commit
with repeatable read, both transactions commit, giving
updated table: (1,10) (1,20) (2,100) (2,200)
(1,300) (2,30)
with serial transactions, only one transaction
commits
T1;T2 gives (1,10) (1,20) (2,100) (2,200) (2,30)
(1,330)
T2;T1 gives (1,10) (1,20) (2,100) (2,200)
(1,300) (2,330)
PG recognises that committing both gives
serialization violation
COMP9315 21T1 ♢ Transaction Isolation ♢ [10/11]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html
11/13

2021/4/28 Transaction Isolation
❖ Concurrency Control
Isolation requires some method to control
concurrency
Possible approaches to implementing concurrency control:
Lock-based
Synchronise tx execution via locks on relevant part of DB.
Version-based (multi-versionconcurrencycontrol) Allow multiple consistent versions of the data to exist.
Each tx has access only to version existing at start of tx.
Validation-based (optimisticconcurrencycontrol) Execute all tx’s; check for validity problems on commit.
Timestamp-based
Organise tx execution via timestamps assigned to actions.
COMP9315 21T1 ♢ Transaction Isolation ♢ [11/11]
<< ∧ https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html 12/13 2021/4/28 Transaction Isolation Produced: 14 Apr 2021 https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-isolation/slides.html 13/13

Related Posts