Concurrency control(new)
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
5
Serializable Schedule
Problem: When are interleaved read/write operations from transactions valid?
Serializable schedules are equivalent to executing all transactions one by one in some order T1,T2,…,Tn.
6
Serial Schedule
Suppose A=1000 T1: R(A) A+500 W(A) T2: R(A) A-500 W(A) Two serial schedules: S(S)1: R1(A,1000)W1(A,1500)R2(A,1500)W2(A,1000) S(S)2: R2(A,1000)W2(A,500)R1(A,500)W1(A,1000)
18
2PL
THEOREM: All schedules generated by enforcing the 2PL protocol are serializable. The actual read/write events that take place in the database correspond to some serial execution of transactions. PROOF: The conflict graph induced by 2PL compliant schedulers are acyclic. By contradiction, assume the serialization graph has a cycle of the form: conflict1 T1 conflict2 Then there must be at least two conflicts. A sample scenario for conflicts 1 and 2: R1(A)…W2(A)...R2(B)…W1(B)
8
Conflicting Operations Definition: Two operations Xi(A) and Yj(B) in a schedule are said to conflict (i.e., the order matters) if and only if the following three conditions hold: A = B. Operations on distinct data items never conflict. i ≠ j. Operations conflict only if they are performed by different Transactions. At least of the two operations X or Y is a write, W. (Other can be R or W.)
13
Precedence Graph
S4: R1(A) R2(A) W2(A) W1(A) C1C2
T1
T2
There is a circuit in the precedence graph!
14
The Serializability Theorem
A schedule S has an equivalent serial execution S(S) iff the precedence graph PG(S) contains no circuit.
16
Locking to Ensure Serializability
A transaction should have a lock in its possession for any data objects it accesses. read/shared lock is required to read an item write/exclusive lock is required to write an item Many transactions may have read locks on the same data item. If a transaction holds a write lock on an item, no other transaction may obtain a lock of any kind for this item.
1
Concurrency Control
What is a schedule? What is a serializable schedule? What are conflicting operations? How to determine a schedule is serializable based on conflicting operations?
2
Concurrent Transactions
Transaction1
R(A) A+500 W(A)
3
R(A) A-500 W(A)
Transaction2
Transaction Schedule
Read and write operations from different transactions are interleaved. A schedule is a partial ordering of the read/write operations in a set of transactions recording how the transactions executed. Let T = {T1, T2, ..., Tn}. A schedule S over T is a partial order <S that: includes all the operations in the {Ti }, preserves the order of the operations in each Ti.
9
Example of Conflicting Operations
S3: R1(A) R2(A) W1(A) W2(A) C1C2 S4: R1(A) R2(A) W2(A) W1(A) C1C2
10
Equivalent Schedules Two different schedules over the same T are equivalent if all pairs of conflicting operations lie in the same order in both schedules. We use the notation R1(A) <<S W2(A) to mean that R1(A) comes before W2(A) in S In one of the equivalent serial schedule S(S), R1(A) <<S(S) W2(A) More generally, T1<<S(S)T2
7
Problem
What’s the criterion for determining when a schedule is serializable without an interpretation? Or: How to come up with an equivalent serial schedule to the given schedule S,denoted by S(S)?
4
Illegal Schedule
Suppose A=1000 T1: R(A) A+500 W(A) T2: R(A) A-500 W(A) S1: R1(A) R2(A) W2(A) C1 C2 S2: W1(A) R2(A) R1(A)W2(A) C1 C2 S3: R1(A,1000) R2(A,1000) W1(A,1500) W2(A,500) C1 C2 S4: R1(A,1000) R2(A,1000) W2(A,500) W1(A,1500) C1 C2
Database VS. File System Problems of file system: Inconsistent result(Ex. Transfer money between accounts) Errors of concurrent execution Uncertainty as to when changes become permanent Solution in database system Transactions
12
R2(A) <<S3 W1(A) R2(A) <<S(S) W1(A) T2<<S(S)T1
Precedence Graph A precedence graph for a history H is a directed graph denoted by PG(H). The vertices of PG(H) correspond to the transactions that have COMMITTED in H An edge Ti -> Tj exists in PG(H) whenever two conflicting operations Xi and Yj occur in that order in H. Thus, Ti -> Tj should be interpreted to mean that Ti must precede Tj in any equivalent serial history S(H).
11
Example
Two serial schedules: S(S)1: R1(A)W1(A)R2(A)W2(A) S(S)2: R2(A)W2(A)R1(A)W1(A) S3: R1(A) R2(A) W1(A) W2(A) C1C2 R1(A) <<S3 W2(A) R1(A) <<S(S) W2(A) T1<<S(S)T2
15
Biblioteka Baidu
Summary Two schedules are equivalent if they order conflicting operations of non-aborted transactions in the same way. Goal -- determine if there is a serial schedule S(S) equivalent to a given schedule S. if there is, then the transactions in S did not interfere with one another
Current Lock None RL RL WL
Lock Requested Lock Granted? RL/WL RL WL RL/WL
17
Yes Yes No No
Two Phase Locking (2PL)
Before each read and write operation, the transaction manager attempts to obtain the necessary lock for the appropriate data item if the lock is granted, then the operation is performed if the lock is not granted, then the whole transaction waits in a queue until the lock can be obtained A transaction can release a lock at any time after a transaction releases a lock for the first time, it cannot obtain any new locks on any data items The COMMIT command in SQL releases all locks a transaction holds, which is a stronger condition than required by 2PL. [Strict Two Phase Locking]
Serializable Schedule
Problem: When are interleaved read/write operations from transactions valid?
Serializable schedules are equivalent to executing all transactions one by one in some order T1,T2,…,Tn.
6
Serial Schedule
Suppose A=1000 T1: R(A) A+500 W(A) T2: R(A) A-500 W(A) Two serial schedules: S(S)1: R1(A,1000)W1(A,1500)R2(A,1500)W2(A,1000) S(S)2: R2(A,1000)W2(A,500)R1(A,500)W1(A,1000)
18
2PL
THEOREM: All schedules generated by enforcing the 2PL protocol are serializable. The actual read/write events that take place in the database correspond to some serial execution of transactions. PROOF: The conflict graph induced by 2PL compliant schedulers are acyclic. By contradiction, assume the serialization graph has a cycle of the form: conflict1 T1 conflict2 Then there must be at least two conflicts. A sample scenario for conflicts 1 and 2: R1(A)…W2(A)...R2(B)…W1(B)
8
Conflicting Operations Definition: Two operations Xi(A) and Yj(B) in a schedule are said to conflict (i.e., the order matters) if and only if the following three conditions hold: A = B. Operations on distinct data items never conflict. i ≠ j. Operations conflict only if they are performed by different Transactions. At least of the two operations X or Y is a write, W. (Other can be R or W.)
13
Precedence Graph
S4: R1(A) R2(A) W2(A) W1(A) C1C2
T1
T2
There is a circuit in the precedence graph!
14
The Serializability Theorem
A schedule S has an equivalent serial execution S(S) iff the precedence graph PG(S) contains no circuit.
16
Locking to Ensure Serializability
A transaction should have a lock in its possession for any data objects it accesses. read/shared lock is required to read an item write/exclusive lock is required to write an item Many transactions may have read locks on the same data item. If a transaction holds a write lock on an item, no other transaction may obtain a lock of any kind for this item.
1
Concurrency Control
What is a schedule? What is a serializable schedule? What are conflicting operations? How to determine a schedule is serializable based on conflicting operations?
2
Concurrent Transactions
Transaction1
R(A) A+500 W(A)
3
R(A) A-500 W(A)
Transaction2
Transaction Schedule
Read and write operations from different transactions are interleaved. A schedule is a partial ordering of the read/write operations in a set of transactions recording how the transactions executed. Let T = {T1, T2, ..., Tn}. A schedule S over T is a partial order <S that: includes all the operations in the {Ti }, preserves the order of the operations in each Ti.
9
Example of Conflicting Operations
S3: R1(A) R2(A) W1(A) W2(A) C1C2 S4: R1(A) R2(A) W2(A) W1(A) C1C2
10
Equivalent Schedules Two different schedules over the same T are equivalent if all pairs of conflicting operations lie in the same order in both schedules. We use the notation R1(A) <<S W2(A) to mean that R1(A) comes before W2(A) in S In one of the equivalent serial schedule S(S), R1(A) <<S(S) W2(A) More generally, T1<<S(S)T2
7
Problem
What’s the criterion for determining when a schedule is serializable without an interpretation? Or: How to come up with an equivalent serial schedule to the given schedule S,denoted by S(S)?
4
Illegal Schedule
Suppose A=1000 T1: R(A) A+500 W(A) T2: R(A) A-500 W(A) S1: R1(A) R2(A) W2(A) C1 C2 S2: W1(A) R2(A) R1(A)W2(A) C1 C2 S3: R1(A,1000) R2(A,1000) W1(A,1500) W2(A,500) C1 C2 S4: R1(A,1000) R2(A,1000) W2(A,500) W1(A,1500) C1 C2
Database VS. File System Problems of file system: Inconsistent result(Ex. Transfer money between accounts) Errors of concurrent execution Uncertainty as to when changes become permanent Solution in database system Transactions
12
R2(A) <<S3 W1(A) R2(A) <<S(S) W1(A) T2<<S(S)T1
Precedence Graph A precedence graph for a history H is a directed graph denoted by PG(H). The vertices of PG(H) correspond to the transactions that have COMMITTED in H An edge Ti -> Tj exists in PG(H) whenever two conflicting operations Xi and Yj occur in that order in H. Thus, Ti -> Tj should be interpreted to mean that Ti must precede Tj in any equivalent serial history S(H).
11
Example
Two serial schedules: S(S)1: R1(A)W1(A)R2(A)W2(A) S(S)2: R2(A)W2(A)R1(A)W1(A) S3: R1(A) R2(A) W1(A) W2(A) C1C2 R1(A) <<S3 W2(A) R1(A) <<S(S) W2(A) T1<<S(S)T2
15
Biblioteka Baidu
Summary Two schedules are equivalent if they order conflicting operations of non-aborted transactions in the same way. Goal -- determine if there is a serial schedule S(S) equivalent to a given schedule S. if there is, then the transactions in S did not interfere with one another
Current Lock None RL RL WL
Lock Requested Lock Granted? RL/WL RL WL RL/WL
17
Yes Yes No No
Two Phase Locking (2PL)
Before each read and write operation, the transaction manager attempts to obtain the necessary lock for the appropriate data item if the lock is granted, then the operation is performed if the lock is not granted, then the whole transaction waits in a queue until the lock can be obtained A transaction can release a lock at any time after a transaction releases a lock for the first time, it cannot obtain any new locks on any data items The COMMIT command in SQL releases all locks a transaction holds, which is a stronger condition than required by 2PL. [Strict Two Phase Locking]