Byun, Jeonggyong

1
?15? ???? ??Chapter 15. Transaction Processing

• Byun, Jeonggyong
• uDB Lab
• School of Computer Multimedia
• Dongguk University

2
?? ??
DB??? ??
?1? ??, ????
DB ??
?2? ??? ??
?6? DB??? ERD
?3? SQL
?7? ??? DB ??
?4? ??SQL
?8? ???? ???
DB ??
?10? XML
?15? ????
?16? ??? ??
?17? ?? ???
3
contents

• Transaction transaction ????, ???? ?? ??
• Transaction Concept(???? ??)
• Transaction State(???? ??)
• Implementation of Atomicity and Durability(????
  ???? ??)
• Concurrent Executions(?? ??)
• Serializability(???)
• Recoverability(?????)
• Implementation of Isolation(???? ??)
• Transaction Definition in SQL(SQL?? ???? ??)
• Testing for Serializability.(??? ??)

4
Transaction Concept

• A transaction is a unit of program execution that
  accesses and possibly updates various data
  items.
• A transaction must see a consistent database.
• During transaction execution, the database may be
  inconsistent.
• When the transaction is committed, the database
  must be consistent.
• Two main issues to deal with
• Failures of various kinds, such as hardware
  failures and system crashes
• Concurrent execution of multiple transactions

5
ACID (????) Properties
To preserve integrity of data, the database
system must ensure

• Atomicity(???). Either all operations of the
  transaction are properly reflected in the
  database or none are.
• Consistency(???). Execution of a transaction in
  isolation preserves the consistency of the
  database.
• Isolation(???). Although multiple transactions
  may execute concurrently, each transaction must
  be unaware of other concurrently executing
  transactions. Intermediate transaction results
  must be hidden from other concurrently executed
  transactions.
• That is, for every pair of transactions Ti and
  Tj, it appears to Ti that either Tj, finished
  execution before Ti started, or Tj started
  execution after Ti finished.
• Durability(???). After a transaction completes
  successfully, the changes it has made to the
  database persist, even if there are system
  failures.

6
??? ??? ?

• Transaction to transfer 50 from account A to
  account B
• 1. read(A)
• 2. A A 50
• 3. write(A)
• 4. read(B)
• 5. B B 50
• 6. write(B)
• Consistency requirement the sum of A and B is
  unchanged by the execution of the transaction.
• Atomicity requirement if the transaction fails
  after step 3 and before step 6, the system should
  ensure that its updates are not reflected in the
  database, else an inconsistency will result.

7
??? ??? ?(Cont.)

• Durability requirement
• once the user has been notified that the
  transaction has completed (i.e., the transfer of
  the 50 has taken place), the updates to the
  database by the transaction must persist despite
  failures.
• Isolation requirement
• if between steps 3 and 6, another transaction is
  allowed to access the partially updated database,
  it will see an inconsistent database (the sum A
  B will be less than it should be).
• Can be ensured trivially by running transactions
  serially, that is one after the other. However,
  executing multiple transactions concurrently has
  significant benefits, as we will see.

8
Transaction State

• Active(??), the initial state the transaction
  stays in this state while it is executing
• Partially committed(????), after the final
  statement has been executed.
• Failed(??), after the discovery that normal
  execution can no longer proceed.
• Aborted(??,??), after the transaction has been
  rolled back and the database restored to its
  state prior to the start of the transaction. Two
  options after it has been aborted
• restart the transaction only if no internal
  logical error
• kill the transaction
• Committed(??), after successful completion.

9
Transaction State (Cont.)
??
????
??
??
??
10
???? ???? ??

• The recovery-management component of a database
  system implements the support for atomicity and
  durability.
• ??? ????????(shadow-database scheme)
• assume that only one transaction is active at a
  time.
• a pointer called db_pointer always points to the
  current consistent copy of the database.
• all updates are made on a shadow copy of the
  database, and db_pointer is made to point to the
  updated shadow copy only after the transaction
  reaches partial commit and all updated pages have
  been flushed to disk.
• in case transaction fails, old consistent copy
  pointed to by db_pointer can be used, and the
  shadow copy can be deleted.

11
???? ???? ?? (Cont.)
??? ?????? ??

• ???? ???? ?? ??? ???
• ?????? ???. ??? ? DB??? ?? ?????. ?? ???? ??? ??
  DB ????. (Will see better schemes in Chapter 17.)

12
?? ??(Concurrent Executions)

• ?? ????? ????? ????? ????? ??? ?? ??? (????)
• ??? ? ???? ??? ??, ????? ????? ?? ?? ??? ? ?????
  ??? ??, ?? ?? ?? ????? CPU? ??? ? ?? ?.
• ????? ?? ????? ?? ????? ?? ????? ? ?? ??? ????
  ??? ?.
• ???????(Concurrency control schemes)
• ???? ??? ?? ? ???? ?????? DB? ???? ????? ??? ??
  ??? ???? ????? ???? ?
• ????? ??? ??? ??? ? 16??? ??.

13
???(Schedules)

• ??,???(Schedules)
• ???? ?????? ????? ???? ??? ??? ???? ?? ??
• ????? ???? ????? ????.
• ???? ??? ?? ???? ?? ????? ?? ????? ????? ?
• ? ????? ??? ????? ??? ?? ?? ????.
• DB?? ?? ????? ?? ??? ? ? ?? ???? ???? ? ?? ?????
  ???.

14
DB ??? ???? ??

• ????? ??
• Read(A) Write(A)
• Read(B) Write(B)

buffer
100
A
200
B
Read (A) Read (B) Write (A) Write (B)
100
200
15
???1,2? ?

• ?? A?? B? 50? ???? ????? T1, A?? 10 ??? ??? B?
  ???? ????? T2.
• T1 gt T2 ? ??? ??? ? ?? ???.
• ??? A1000,B2000
• ??? 1 ltT1, T2gt f15.3
• A855, B2145
• ??? 2 ltT2, T1gt f15.4
• A850, B2150

16
???3? ? (Cont.)

• ?? ???? ?? ??? ??? 3? ???1? ??.
• ? ??? 1? 3??, ? A B? ??(??)??.
• ??? A1000,B2000
• A855, B2145
• ??? 1 AB ??? 3 AB

Fig 15.5
17
???4? ? (Cont.)

• ??? ??? 4? ? AB? ???? ???.
• T1 A 1000
• T2? A? 1000?? 900? ? ? ???
• T2? B? ??, B? 2000
• T1? A? 950? ?? ?. 900 ???
• T1? B2000?? ? 2050? ???
• T2? 2100? ?? ???. 2050 ? ?? ??? ???
• ??? 4

18
???(Serializability)

• ???? ? ????? DB ???? ???.
• ??? ???? ??? ????? DB ???? ???.
• ?? ???? ?? ???? ??(equivalence)?? ? ????
  ????(serializable)??. ??? ??? ?? ??? ? ??
• ?? ???(conflict serializability)
• ? ???(view serializability)
• ??(read) ? ??(write) ??? ??? ????. ??? ?? ??? ??
  ?? ?? ?? ??? ?? ??? ??? ????? ????. ???? ???? ??
  ??, ?? ????? ???? ??.

19
?? ???(Conflict Serializability)

• ? ??? li ? lj (i ? j)? ??? ??? ?? Q? ???? ??? ??
  ? ??? Q? ??? ??? ???? Ti ? Tj ? ? ??? li ? lj ?
  ????.
• 1. li read(Q), lj read(Q). li ? lj ? ????
  ??.2. li read(Q), lj write(Q). ??.3. li
  write(Q), lj read(Q). ??.4. li write(Q),
  lj write(Q). ??.
• ?????, li ? lj ?? ??? ???? ??? ??? ????. li ? lj
  ? ? ????? ?????, ???? ????, ??? ????? ?? ?????
  ???? ??? ??? ?? ?? ? ? ???.

20
?? ??? (Cont.)

• ??? S ? ??? ??? ???? ??? ??? ??? S?? ??? ? ???,
  S ? S ? ????( conflict equivalent)? ??.
• ??? S ? ?????? ???????, ? ???? ??????(conflict
  serializable)?? ??.
• ??? 3 gt ??? 5
• T1 read(B)?T2 read(A)
• T1 write(B)?T2 write(A)
• T1 write(B)?T2 read(A)
• ????? ????
• gt ??? 6

T1 T2
Read(A) Write(A) Read(B) write(B) Read(A) Write(A) Read(B) Write(B)
T1 T2
Read(A) Write(A) Read(B) write(B) Read(A) Write(A) Read(B) Write(B)
??? 5
??? 6
21
?????(??)

• ?? ????? ?? ?
• ??? 7 gt
• ? ???? ?????? lt T3, T4 gt ?? lt T4, T3 gt ?? ???
  ???? ?? ??? ??????? ???.
• Q100, T4 Q200
• ltT3,T4gt Q200, ltT4,T3gt Q250
• ??? ??? 3? ??? ???? ???? ??? 1(T2 ? T1 ? ??)??
  ??? ? ??. ???? ??? 3 ? ?? ???? ???.

T3 T4
Read(Q) Q50 Write(Q) Write(Q)
22
???? ?? ??

• Schedule 8 ? lt T1, T5 gt? ?? ??? ??? ?? ?? ?? ?
  ???? ??.
• ?? ? ?? ?? 960, 2,040?? ???,
• T5? write(B)? T1? read(B)? ????.
• ?? ???(??1,2,3)?? ??
• ?? ?? ??? ???? ?? ??, ??? ?? ??? ??? ????.

??
23
????

• ?? ???? ??? ?? ? ???? S ? S ?? ??. ?? 3?? ???
  ???? S ? S ? ???(view equivalent) ?? ??
• ???1,???2? ????? ??. ???2? ??2 ??
• ???3? ?? 1,2,3 ????? ?????.

• ? ?? ?? Q? ???, ?? ??? S? ?? ???? Ti ? Q? ????
  ????, Ti ? ??? S??? Q? ???? ??? ??.
• ? ?? ?? Q? ???, ?? ???? Ti ? ??? S?? read(Q)?
  ????, ? ?? ???? Tj (?? ??)? write(Q)? ??? ????
  ????, ???? Ti ? read(Q) ??? ??? S ??? Tj ?
  write(Q)? ??? ???? Q? ?? ??? ?.
• ? ?? ?? Q? ??, ?? ??? S?? ?? write(Q) ??? ????
  ????? S??? ??? write(Q)? ???? ??.

24
? ??? (Cont.)

• ??? S ? ?????? ?????? ? ???? ? ????
• ?? ?????? ???? ?? ? ????, ?? ???? ??.
• ??? 9? ???????? ??????? ?? ?????.
• T4? T6? read(Q)? ?? ?? ?? write(Q)? ????. ??
  ????? ?????? ?? ?? ? ?? ????? ??? ? ??
• ??? 9 gt
• ??? 9? ltT3,T4,T6gt? ??? (??1,3 ??).
• ??????? ?? ?? ????? ???? ?? ??(blind writes)??.

25
???(Recoverability)

• ?????? ????? ??? ??
• Ti? ???? ??? ??? ??? Ti? ?? ?? ??(abort)
• Ti? ???? Tj? ?? ?? ??(abort)??? ?
• ?? ??? ???(Recoverable schedule)
• Tj? ??? Ti? ??? ??? ???? ?? ???? Ti? Tj ? ? ?? ??
  Ti? Tj? ??? Tj ? ??? ???? ?? Ti ? ??? ?? ???? ???
• ??? 11? ?? ???
• T9? ?? ? ? ?? ??(T8?? ??)
• T8? ????? ? ?????
• DB? ???? ?? ???? ???? ?.

??? 11
26
???(Cont.)

• ???? ??(Cascading rollback) ??? ????? ?????? ?
  ?? ??? ??? ?????? ??? ???? ??
• ??? ??? ????? ?? ????.
• T10? ???? T11? ????? ??? ?? ? ??
• T12 ?? T11? ????? ??? ?? ? ??
• ??? 12 ltT10, T11, T12gt

T11
T10
T12
??
4
2
1
3
??
??
data
??
27
???(Cont.)

• ???? ???(Cascadeless schedules) Ti? ??? ??? ???
  ?? ??? Tj? ?? ? ?? ???? Ti? Tj ?? Tj? ?? ??? ??
  Ti? ?? ??? ???? ???
• ?? ???? ???? ?? ????
• ????? ?????? ???? ???? ?? ?????.

Ti
Tj
2
??
1
3
??
data
??
28
???? ??

• DB? ??? ??, ??? ??? ??? ?? ???? ?? ?? ??????, ?
  ????, ???? ??
• ??? ?????? DB ??? ???? ?? ??
• ?? ? ?????? ? ??? ??? ? ??? ??? ???, ??????? ???
  ??? ???, ? ???? ??
• ??? ?? ?? gt 16?
• ?? ??? ?? ????? ??, ?????? ?????? ????? ?? ????
  ???? ?
• ?? ???? ???? ?? ???? ??? ???? ??? ??? ??? ?? ???
  ???.(Trade-off)
• ??? ??? ??? ?? ?????? ????, ?? ?? ? ????? ?????
  ????.

29
??? ??

• T1, T2, ..., Tn ???? ??? ?? ??? ??
• ?????(Precedence graph) ??? ????, ???? ?? ??? ?
  ??. G (V,E)
• Tj? read(Q)? ???? ?? Ti? write(Q)? ??
• Tj? write(Q)? ???? ?? Ti? read(Q)? ??
• Tj? write(Q)? ???? ?? Ti? write(Q)? ??
• Ti -gt Tj?? Ti? ??? Tj?? ?? ????? ?
• ??? 4??, ?? ???? ??(cycle)? ??? ???? ?? ????? ???.

???4
x
y
30
???? ??

• ????? ????? ?????? ?? ??? ???? ????? ????
  ????(topological sorting)? ??? ?? ? ??.
• ????? ?? gt??????

31
End of Chapter
32
Schedule 5 -- Schedule 3 After Swapping A Pair
of Instructions
33
Schedule 6 -- A Serial Schedule That is
Equivalent to Schedule 3
34
Schedule 7
35
Precedence Graph for (a) Schedule 1 and (b)
Schedule 2
36
Precedence Graph
37
fig. 15.21