PPT – T1 PowerPoint presentation | free to view

About This Presentation

Title:

T1

Description:

Title: Author: RUC IDKE Last modified by: Created Date: 8/9/2000 8:19:19 AM Document presentation format – PowerPoint PPT presentation

Number of Views:66

Avg rating:3.0/5.0

Slides: 101

Provided by: RUC53

Category:

more less

Transcript and Presenter's Notes

Title: T1

1
??????? An Introduction to Database System ????
????
2
?????

???????????
????????????????
?????????
???????
????????????????????

3
?????(?)

??????????
(1)??????
????????????,????????????????????
??????????,????????????

T1
T2
T3
?????????
4
?????(?)

(2)??????(Interleaved Concurrency)
????????,?????????????????????????
??????????????????????,?????????????,???????

5
?????(?)
???????????
6
?????(?)

(3)??????(simultaneous concurrency)
???????,?????????????,???????????????,????????????
?

7
?????(?)

???????????
??????????????????
??????????????,???????????????

8
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

????
???????????????????????????????????
?????
?????????????????(???????????????)?????????????
??????????????????
?? ???????????????????????????????????????
?????????????,?????????????????????????????

10
11.1 ??????

?????????
???????????
????????
?????????

11
T1????T2???!
??????(?)

???????????????
?1??????????????
? ????(???)??????????A,?A16
? ????(???)???????????A,??16
? ??????????,????A?A-1,??A?15,?A?????
? ???????????,????A?A-1,??A?15,?A?????
??????????,???????????1

12
??????(?)

??????????????,??????????
????????,?????????????????????
???????????,???????????
???4???????A?????????????

13
??????(?)

?????????????
????(Lost Update)
?????(Non-repeatable Read)
????(Dirty Read)
??
R(x)???x
W(x)???x

14
1. ????

????T1?T2?????????,T2????????T1?????,??T1???????
????????????

15
????(?)
T1 T2
? R(A)16

? R(A)16

? A?A-1
W(A)15W

? A?A-1
W(A)15

????
16
2. ?????

?????????T1?????,??T2
??????,?T1????????????

17
?????(?)

???????????
(1)??T1???????,??T2??????,???T1???????,??????????

18
?????(?)
??

T1??B100????
T2??????B,????????B200??????
T1??????????B,B??200,??????????

T1 T2
? R(A)50
R(B)100
??150
? R(B)100
B?B2
(B)200
? R(A)50
R(B)200
?250
(????)
?????
19
?????(?)

(2)??T1????????????????????,??T2?????????,?T1?????
???????,?????????
(3)??T1???????????????????,??T2???????,?T1????????
????,?????????
?????????????????(Phantom Row)

20
3. ????

??????
??T1??????,???????
??T2???????,T1?????????
??T1???????????,T2?????????????????
T2??????????,???????

21
????(?)
??
T1 T2
? R(C)100
C?C2
W(C)200
? R(C)200

?ROLLBACK
C???100

T1?C????200,T2??C?200
T1????????,?????,C????100
??T2???C?200,?????????,?????

????
22
??????(?)

?????????????????????
???????????????????,???????????????????,??????????
???

23
??????(?)

?????????
???(Locking)
???(Timestamp)
?????
???DBMS?????????

24
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

25
11.2 ??

?????
??????
??????

26
?????

??????T????????(???????)????,????????,????
?????T??????????????,???T???????,???????????????

27
??????

?????????????????????????????????
??????
???(Exclusive Locks,???X?)
???(Share Locks,???S?)

28
???

????????
???T?????A??X?,????T?????A,???????????A???????,??T
??A???
???????T??A?????????????A

29
???

????????
???T?????A??S?,?????????A?S?,????X?,??T??A??S?
?????????A,??T??A??S??????A?????

30
??????
31
??????(?)

????????
?????????T1???????????????,???????????
???????????T2???????????????
T2???????????????Y?N??
Y????T2??????T1???????,????????
N??T2??????T1???????,T2??????

32
??????????????
?
??????
T1 T2
? Xlock A
? R(A)16
Xlock A
? A?A-1 ??
W(A)15 ??
Commit ??
Unlock A ??
? ??Xlock A
R(A)15
A?A-1
? W(A)14
Commit
Unlock A

??T1??A????????A?X?
?T2????A?X?????
T2????T1??A????T2???A?X?
??T2???A???T1?????15
T2????A?????,?????A14???????????T1????

33
???????????????
T1 T2
? Slock A
Slock B
R(A)50
R(B)100
??150
? Xlock B
??
??
? R(A)50 ??
R(B)100 ??
??150 ??
Commit ??
Unlock A ??
Unlock B ??
? ??XlockB
R(B)100
B?B2
? W(B)200
Commit
Unlock B
????

??T1??A,B??,??A,B?S?
????????A,B?S?,????X?,????????A,B,?????
?T2???B????B?X?????????T1??B???
T1?????A,B,?????B??100,??????150,?????
T1?????A,B??S??T2????B?X?

34
??????????????
?
T1 T2
? Xlock C
R(C)100
C?C2
W(C)200
? Slock C
??
? ROLLBACK ??
(C???100) ??
Unlock C ??
? ??Slock C
R(C)100
? Commit C
Unlock C
?????

??T1??C??????,??C?X?,?????????
T2???C??S?,?T1??C???X?,T2????
T1????????,C?????100
T1??C??X??T2??C??S?,?C100????T2????

35
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

36
11.3 ?????

?????????????????????,??????????
??
??

37
11.3.1 ??

??T1?????R
??T2?????R,??T2???
T3?????R,?T1???R?????????????T3???,T2?????
T4?????R,?T3???R????????????T4???
T2???????,????????

38
??(?)
? ?
39
??(?)

??????????????
????????????????
?????????????????
????????????,?????????????????

40
11.3.2 ??

??T1?????R1
T2?????R2
T1?????R2,?T2????R2,??T1??T2??R2???
??T2?????R1,?T1????R1,T2?????T1??R1???
??T1???T2,?T2????T1,T1?T2??????????,????

41
??(?)
T1 T2
lock R1
Lock R2

Lock R2.
??
?? Lock R1
?? ??
?? ??

? ?
42
???????

????
1. ????
2. ????????

43
1. ?????

??????????????????????????,??????????????????????,
????????
???????????????????

44
?????(?)

???????
?????
?????

45
(1)?????

???????????????????????,?????????
?????
???????
????????????

46
(2)?????

?????????????????????,???????????????
??????????
????
???????????????,?????????
??????????????????????

47
?????(?)

??
?????????????????????????????
DBMS??????????????????????????

48
2. ????????

?????
???
??????

49
(1) ???

???????????????????,????????
??????
??
???????
????????,???????????

50
(2)????

???????????????????
???????????G(T,U)
T??????,????????????
U?????,????????????
?T1??T2,?T1,T2????????,?T1??T2

51
????(?)

?(a)?,??T1??T2,T2??T1,?????
?(b)?,??T1??T2,T2??T3,T3??T4,T4???T1,?????
?(b)?,??T3?????T2,???????????

52
????(?)

???????????(??????)???????,???????????????,???????
?????

53
????????(?)

????
???????????????,????
????????????,????????????

54
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

55
11.4 ?????????

DBMS????????????????????
???????????

56
11.4.1 ??????

????(Serializable)??
?????????????,????????????????????????????
????(Serializability)
????????????
?????????,???????????,????????

57
??????(?)

????????,????????
??T1?BAB1??A
??T2?ABA1??B
????????????????

58
?????,?????
T1 T2
Slock B
YR(B)2
Unlock B
Xlock A
AY13
W(A)
Unlock A
Slock A
XR(A)3
Unlock A
Xlock B
BX14
W(B)
Unlock B

??A?B?????2?
?T1?T2???????A3,B4
??????,?????

????(a)
59
?????,?????
T1 T2
Slock A
XR(A)2
Unlock A
Xlock B
BX13
W(B)
Unlock B
Slock B
YR(B)3
Unlock B
Xlock A
AY14
W(A)
Unlock A

??A?B?????2?
T2?T1???????B3,A4
??????,?????

????(b)
60
???????,?????
T1 T2
Slock B
YR(B)2
Slock A
XR(A)2
Unlock B
Unlock A
Xlock A
AY13
W(A)
Xlock B
BX13
W(B)
Unlock A
Unlock B

?????(a)?(b)??????
??????

????????
61
??????,?????
T1 T2
Slock B
YR(B)2
Unlock B
Xlock A
Slock A
AY13 ??
W(A) ??
Unlock A ??
XR(A)3
Unlock A
Xlock B
BX14
W(B)
Unlock B

?????????(a)???????
??????

???????
62
11.4.2 ????????

???????????
????Sc????????????????,???????????????????????Sc,
??Sc????,???Sc??????????
???????????,??????????

63
????????(?)

????
???????????????????????????
Ri (x)?Wj(x) / ??Ti?x,Tj?x/
Wi(x)?Wj(x) / ??Ti?x,Tj?x/
??????????
???????????????????????(Swap)

64
????????(?)

?????Sc1r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B
)
?w2(A)?r1(B)w1(B)??,??
r1(A)w1(A)r2(A)r1(B)w1(B)w2(A)r2(B)w2(B)
??r2(A)?r1(B)w1(B)??
Sc2r1(A)w1(A)r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)
Sc2?????????T1,T2,Sc1?????????

65
????????(?)

????????????????????,????????????????????????????
??3???
T1W1(Y)W1(X),T2W2(Y)W2(X),T3W3(X)
??L1W1(Y)W1(X)W2(Y)W2(X) W3(X)????????
??L2W1(Y)W2(Y)W2(X)W1(X)W3(X)??????????????L2????
??,??L2????????L1??,Y?????T2??,X?????T3??

66
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

67
11.5 ?????

????
???????,????????????????
??????
????
???????
??????(Two-Phase Locking,??2PL)???????????,???????
????????????????

68
?????(?)

?????
?????????????????????
???????????????,??????????????
?????????,???????????????

69
?????(?)

??????
????????
?????????,???????
?????????????????????,?????????
?????????,???????
???????????????????,??????????

70
?????(?)

?
??Ti???????,??????
Slock A Slock B Xlock C Unlock B
Unlock A Unlock C
? ???? ? ? ???? ?
??Tj????????,??????
Slock A Unlock A Slock B Xlock C
Unlock C Unlock B

71
?????(?)
??T1 ??T2
Slock(A)
R(A260)
Slock(C)
R(C300)
Xlock(A)
W(A160)
Xlock( C )
W(C250)
Slock(A)
Slock(B) ??
R(B1000) ??
Xlock(B) ??
W(B1100) ??
Unlock(A) ??
R(A160)
Xlock(A)
Unlock(B)
W(A210)
Unlock( C )

??????????????,??????????????

??????????????
72
?????(?)

?????????????????????,????????
?????????????,??????????????????????
????????????????,???????????????

73
?????(?)

????????????????
????????????????????????????,?????????,???????????
???
??????????????????????????????,??????????????????

74
?????(?)

? ??????????????

T1 Slock B R(B)2 Xlock A ?? ??
T2 Slock A R(A)2 Xlock A ??
????????????????
75
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

76
????

?????????????(Granularity)
?????????,????
????????,????
???? ???????????????????????????????
?????(???????)??????

77
????????

????????????????????????
???????,????????????????,??????,???????
???????,?????,?????????

78
?????????(?)

?
?????????,??T1??????L1,?T1?????L1??????A?????T1?A?
????T2???A???L2,?T2????,??T1??A?
?????????,?T1?T2?????L1?L2??,???????,??????????
??,??T???????,????????,T?????????????,????

79
?????????(?)

?????(Multiple Granularity Locking)
????????????????????????
??????
????????????????,????????
???????????????????????????
?????????????????????
????????????????????

80
11.6.1 ?????

????
??????????????
?????????,?????????
????????????

81
?????(?)

??????????????,??????????,??????????

?????
82
???????

??????????????????
???????????????????????????????
???????????????????????????????

83
?????????

???? ????????????
???? ???????????,?????????????????????
????????????????

84
?????????(?)

?????????
???????
????????
????T????R1?X?
?????????????????R1
????R1?????,?R1???????
???????????????????,?T????

85
?????????(?)

?????????,?????
?????
??????????
??????
??????????????????????????(?????????????)
??????
???????????????????(??????????)??

86
11.6.2 ???

?????(intention lock)??
???????????????????

87
???(?)

???????????,????????????????
?????????,??????????????
??,????????,??????????????????

88
?????

?????(Intent Share Lock,??IS?)
?????(Intent Exclusive Lock,??IX?)
???????(Share Intent Exclusive Lock,??SIX?)

89
???(?)

IS?
??????????IS?,?????????(??)?S??
????T1??R1??????S?,???????R1?????IS?

90
???(?)

IX?
??????????IX?,?????????(??)?X??
????T1??R1??????X?,?????? ?R1?????IX?

91
???(?)

SIX?
??????????SIX?,?????S?,??IX?,?SIX S IX?
??????SIX?,???????????(???????S?),?????????(?
??????IX?)?

92
???(?)
????????
93
???(?)

????
????????????????
?????????????????????,?????

94
???(?)

?????????????
?????????????????
??????????????????
????T1????R1?S?
????????IS?
??????R1??????????(X?IX)
?????????R1?????????????(X?)

95
???(?)

?????????????
?????????
???????????
????????????????????

96
???? ????

11.1 ??????
11.2 ??
11.3 ?????
11.4 ?????????
11.5 ?????
11.6 ?????
11.7 ??

97
11.7 ??

???????????????
??????????????????????????
???????????????????????????
?????????????????????
???????????????????????????,??????????

98
??(?)

??????????????
????????????????
????????

99
??(?)

????????????????????????????
?????????????????????
?????????????????,???????

100
??(?)

?????????,????
?? ?????
??
????
?????
?????
????????
???
????

Write a Comment

User Comments (0)

About PowerShow.com

T1 - PowerPoint PPT Presentation

T1

Title: Author: RUC IDKE Last modified by: Created Date: 8/9/2000 8:19:19 AM Document presentation format – PowerPoint PPT presentation