Transaction Manager

1. Transaction Manager Concurrency Control Recovery Management

2. Transactions A transaction is a unit of program execution that accesses and possibly updates various data items. [ A transactionprogram is a collection of operations that form a single unit of work.] Clearly, it is essential that all these operations occur, or that, in case of failure, none occur. A database system must ensure proper execution of transactions despite failures – either the entire transaction executes, or none of it does. Furthermore, it must manage concurrent execution of transactions in a way that avoids the introduction of inconsistency.

3. Transactions หมายถึงโปรแกรมการประมวลผลที่เขียนด้วย High-level data manipulation language เพื่อเข้าไป update ข้อมูล ในระบบฐานข้อมูล และ DBMS ต้องรับประกันว่า เมื่อ transaction ทำงานเสร็จแล้ว จะต้องทำให้ข้อมูลอยู่ในสภาพที่สมบูรณ์ถูกต้อง กล่าวคือถ้าก่อนการ update ฐานข้อมูลเดิมมีสภาพดีอยู่แล้ว หลังจากการประมวลผลของ transaction ฐานข้อมูลจะต้องคงสภาพความถูกต้องดังเดิม Collections of operations that form a single logical unit of work are called transactions. DBMS must ensure proper execution of transactions despite failures either the entire transaction executes, or none of it does. Furthermore, it must manage concurrent execution of transactions in a way that avoids he introduction of inconsistency.

4. Architecture of a TPS Application Transaction Keyed Notice of Event TPS Data TPS Data Event TPS Program Response Response Report(s) The event is recorded by keying it into the computer system as a transaction, which is a representation of the event. One or more TPS programs process the transaction against TPS data. The TPS program generates two types of output. It sends messages back to the user terminal, and it generates printed documents.

5. Transaction State A transaction may not always complete its execution successfully. Such a transaction is termed aborted. If we are to ensure the atomicity property, an aborted transaction must have no effect on the state of the database. Thus, any changes that the aborted transaction made to the database must be undone. Once the changes caused by an aborted transaction have been undone, we say that the transaction has been rolled back. Partially committed Committed active failed Aborted

6. Transactions access data using two operations: • read(X), which transfers the data item X from the database • to a local buffer belonging to the transaction that executed • the read operation. • write(X), which transfers the data item X from the local • buffer of the transaction that executed the write back to the • database. • In a real database system, the write operation does not necessarily result in the immediate update of the data on the disk; the write operation may be temporarily stored in memory and executed on the disk later. For now, however, it is assumed that the write operation updates the database immediately.

7. Transaction Concepts Usually, a transaction is initiated by a user program written in high-level DML or programming language, where it is delimited by statements (or function calls) of the form begin transaction and end transaction. The transaction consists of all operations executed between the begin transaction and end transaction. To ensure integrity of the data, we require that the database system maintain ACID properties of the transactions:

8. ACID properties of Transaction ensured by DBMS Atomicity. Either all operations of the transaction are reflected properly in the database, or none are. Consistency. Execution of a transaction in isolation (that is, with no other transaction executing concurrently) preserves the consistency of the database. Isolation. Even though multiple transactions may execute concurrently, the system guarantees that, 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. Thus each transaction is unaware of other transactions executing concurrently in the system. Durability. After a transaction completes successfully, the change it has made to the database persist, even if there are system failures.

9. Atomicity:Because of the failure (power failures, hardware failures, and software errors), the state of the system no longer reflects a real state of the world that the database is supposed to capture. We term such a state an inconsistent state. We must ensure that such inconsistencies are not visible in a database system. [The system must be at some point be in a temporary inconsistent state, however, it is eventually replaced by the consistent state.]

10. The basic idea behind ensuring atomicity is this: The database system keeps track (on disk) of the old values of any data on which a transaction performs a write, and, if the transaction does not complete its execution, the database system restores the old values to make it appear as though the transaction never executed. Ensuring atomicity is the responsibility of the database system itself; specifically, it is handled by a component called the transaction-management component. Consistency: Ensuring consistency for an individual transaction is the responsibility of the application programmer. This task may be facilitated by automatic testing of integrity constraints

11. Isolation: Even if the consistency and atomicity properties are ensured for each transaction, if several transactions are executes concurrently, their operations may interleave in some undesirable way, resulting in an inconsistent state. A way to avoid the problem of concurrently executing transactions is to execute transaction serially – that is, one after the other. However, concurrent execution of transactions provides significant performance benefits. The isolation property of a transaction ensures that the concurrent execution of transactions results in a system state that is equivalent to state that could have been obtained had these transactions executed one at a time in some order. Ensuring the isolation property is the responsibility of a component of the database system called the concurrency-control component.

12. Durability: We assume that a failure of the computer system may result in loss of data in the main memory, but data written to disk are never lost. DBMS can guarantee durability by ensuring that either : 1. The updates carried out by the transaction have been written to disk before the transaction completes. 2. Information about the updates carried out by the transaction and written to disk is sufficient to enable the database to reconstruct the updates when the database system is restarted after the failure. Ensuring durability is the responsibility of a component of the database system called the recovery-management component.

13. DBMS must maintain the following properties of the transactions :- Atomicity : ถ้า transactions เริ่มดำเนินการ ต้องดำเนินไปจนลุล่วงหมด ทุกคำสั่ง หรือถ้าดำเนินการไม่สำเร็จลุล่วง transactions จะต้องทำให้ ฐานข้อมูลดูเสมือนหนึ่งว่าไม่เคยมีการกระทำใด ๆ เกิดขึ้นเลย ค่าข้อมูล ต่างๆ ยังคงเป็นค่าเดิมก่อนการประมวลผลของ transactions โดยที่ transactions ต้องถูก roll back กลับไปตั้งต้นไป การ commit หรือ roll back นี้จะถูกดำเนินการโดย transaction-management component ซึ่งเป็นองค์ประกอบหนึ่ง ของ DBMS DBMS เก็บค่าเก่าของข้อมูลทุกค่าที่ transactions เข้าไปดำเนินการ write และถ้า transactions ไม่สามารถประมวลผลจนเสร็จสมบูรณ์ (system failure หรือ program runtime error...) DBMS จะนำค่าเก่าขึ้นมาฟื้นสภาพให้กับข้อมูล เสมือนหนึ่งว่าไม่เคยมีการประมวลผลใด ๆ เกิดขึ้นกับข้อมูล เหล่านี้เลย ซึ่งเป็นหน้าที่ของ Recovery manager

14. Consistency : DBMS ต้องรับประกันความถูกต้องของข้อมูลในระบบฐานข้อมูลอยู่เสมอ ไม่ว่าก่อน หรือหลังการประมวลผลของ transaction การรับประกันคุณสมบัติ consistency นี้ สามารถทำได้โดยระบุกฎเกณฑ์ความคงสภาพ (Integrity constraint) Isolation : ถึงแม้ว่าหลาย ๆ transactions สามารถเข้าประมวลผลฐานข้อมูลพร้อม ๆ กันได้ ในเวลาเดียวกัน (Concurrent execution) และอาจเข้าประมวลผลชิ้นข้อมูลเดียวกันด้วย แต่ DBMS ต้องรับประกันในการจัด ลำดับการเข้าประมวลผลของ transactions เหล่านั้น ให้มีลักษณะเสมือนเป็น serial execution การรับประกันคุณสมบัติ Isolation เป็นหน้าที่ความรับผิดชอบของ Concurrency-control component หรือ Scheduler

15. Durability : เมื่อ transactions จบสิ้นการประมวลผลอย่างสมบูรณ์ ระบบ ข้อมูลต้องคงสภาพอยู่อย่างนั้น แม้ว่าจะเกิด system failures ในภายหลัง (คุณสมบัติ durability ภายหลังการเกิด system failures หมายถึง system failures ที่มีผลทำให้ข้อมูลใน main memory สูญหาย แต่ไม่กระทบข้อมูล ที่บันทึกลงบนดิสก์เรียบร้อยแล้ว)

16. Scheduler Transaction Manager Buffer manager Recovery manager A transaction manager is software that monitors the behavior of transactions and decides whether each action can be allowed to execute. The transaction manager coordinates transactions on behalf of application programs. It communicates with the scheduler (sometimes referred to as the lock manager). This module is responsible for implementing a particular strategy for concurrency control. If a failure occurs during the transaction, then the database could be inconsistent. It is the task of the recovery manager to ensure that the database in consistent state. Finally, the buffer manager is responsible for the transfer of data between disk storage and main memory. File manager Access manager System manager Database and system catalog

17. Transaction Atomicity in a Single-Transaction System In a single-transaction system, only one transaction is execute at any time. If a transaction is active, no other transaction can start. This situation is the same as having one application connected to the database server at a time. To support atomicity, a database server must support operations to open a transaction, commit a transaction, and rollback a transaction by grouping one or more SQL commands together. If either command fails, transaction manager can roll back all commands, returning the data source to its original state. If all commands are successful, the transaction manager commits the changes and make them permanent. Concurrent Transaction Processing Concurrency arises when many applications are executing transactions at the same time. A single database server processes all operations, so only one database operation can be processed at a time. However, the operations of the transactions overlap because independent applications are requesting service by the database server in parallel.

18. Schedule is a sequence of the operations by a set of concurrent tractions that preserves the order of the operations in each of the individual transactions. Clearly, a schedulefor a set of transactions must consists of all instructions of those transactions, and must preserve the chronological order in which instructions appear in each individual transaction. A schedulecan be serial or non-serial schedule. Each serial schedule consists of a sequence of instructions from various transactions, where the operations of each transaction are executed consecutively without any interleaved operations from other transactions. For a set of n transactions, there exist n! different valid serial schedules.

19. When the database system executes several transactions concurrently, the corresponding schedule no longer needs to be serial. OS must perform a context switch (CPU time is shared) among all transactions which concurrently access to database. Several execution sequences are possible, since the various instructions from several transactions may now be interleaved. The number of possible schedules for a set of n transactions is much larger then n!.

20. คั่น ด้วยแผ่นใสอีก 2 แผ่น อยู่ใน ไฟล์ word ขื่อ transaction2.doc อยู่ใน D:\srp\transaction2.doc

21. Schedule : A sequence of the operations by a set of concurrent transactions that preserves the order of the operations in each of the individual transactions. Serial schedule : A schedule where the operations of each transaction are executed consecutively without any interleaved operations from other transactions. T1: read(A); T2: read(A) A := A-50; temp := A * 0.1; write(A) A := A – temp; read(B); write(A); B := B + 50; read(B); write(B); B := B + temp; write(B); T1 T2   read(A); A := A-50; write(A) read(B); B := B + 50; write(B); read(A) temp := A * 0.1; A := A – temp; write(A) read(B); B := B + temp; write(B);

22. Nonserial schedule : A schedule where the operations from a set of concurrent transactions are interleaved. T1 T2 read(A); A := A-50; read(A) temp := A * 0.1; A := A – temp; write(A) read(B); write(A); read(B); B := B + 50; write(B); B := B + temp; write(B);

23. If several transactions run concurrently, and control of concurrent execution is left entirely to the OS, database consistency can be destroyed despite the correctness of each individual transaction We can ensure consistency of the database under concurrent execution by making sure that any schedule that executed has the same effect asa schedule that could have occurred without any concurrent execution. That is, the schedule should, in some sense, be equivalent to a serial schedule.

24. Potential problems caused by concurrency 1. Lost update problem : An apparently successfully completed update operation by one user can be overridden by another user. T3 T4 balance (15) Time1 balance1 = (select balance from 15 Customer where accountID = 101); balance1 += 5.00; Time 2 balance2 = (select balance from 15 Customer where accountID = 101); balance2 += 10.00; Time 3 update Customer set balance = 20 ?balance1 where accountID = 101; Time 4 update Customer set balance = 25 ?balance2 where accountID = 101; Time 5 Commit 25 Time 6 Commit 25

25. Potential problems caused by concurrency 2. The uncommitted dependency problem : This problem occurs when one transaction is allowed to see the intermediate result of another transaction before it has committed. T3 T4 balance (15) Time1 balance1 = (select balance from 15 Customer where accountID = 101); balance1 += 5.00; Time 2 update Customer set balance = 20 ?balance1 where accountID = 101; Time 3 balance2 = (select balance from 20 Customer where accountID = 101); balance2 += 10.00; Time 4 Rollback 15 Time 5 update Customer set balance = 30 ?balance2 where accountID = 101; Time 6 Commit 30

26. 3. Incorrect summary problem : Balance bal 101 bal 102 T3 T4 Time1 balance1 = (select balance from 15 15 Customer where accountID = 101); balance1 += 10.00; Time 2 update Customer set balance = 25 15 ?balance1 where accountID = 101; Time 3 Total = select sum(balance) from customer where accountID = 101 or accountID = 102 Time 4 Commit Time 5 balance1 = (select balance from 25 15 Customer where accountID = 102); balance1 -= 10.00; Time 6 update Customer set balance = 25 5 ?balance1 where accountID = 102; Time 7 Commit 25 5

27. A phantom read problem : It occurs when an aggregate operation is repeated by a transaction and yields a different result because of the insertion of a row by another transaction T1 T2 sum(balance) 100 Time1 totalA = (select sum(balance) from 100 Customer where zipcode = 31101); Time 2 insert into customer (accountID, 100 balance, zipcode) values (105, 10.00, 31101) Time 3 totalB = (select sum(balance) from 110 Customer where zipcode = 31101); Time 4 rollback Time 5 Commit

28. A nonrepeatable read problem : It occurs when a transaction reads the same value more than one time. In between reading the data item, another transaction modifies the data item. T1 T2 balance 15 Time1 balance1 = (select balance from 15 Customer where accountID = 101); Time 2 update customer set balance = 0.0 0.0 where accountID = 101; Time 3 balance2 = (select balance from 110 Customer where accountID = 101);

29. Recoverability : If a transaction fails, the atomicity property requires that we undo the effects of the transaction. In addition, the durability property states that once a transaction commits, its changes cannot be undone. Recoverable schedule : A schedule where, for each pair of transactions Ti and Tj, if Tj reads a data item previously written by Ti, then the commit operation of Ti precedes the commit operation of Tj.

30. Non-recoverable schedule T3 T4 balance (15) Time1 balance1 = (select balance from 15 Customer where accountID = 101); balance1 += 5.00; Time 2 update Customer set balance = 20 ?balance1 where accountID = 101; Time 3 balance2 = (select balance from 20 Customer where accountID = 101); balance2 += 10.00; Time 4 update Customer set balance = 30 ?balance2 where accountID = 101; Time 5 Commit 30 Time 6 Rollback 10

31. Locking : A procedure used to control concurrent access to data. When one transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results. Locking methods are the most widely used approach to ensure serializability of concurrent transactions. There are several variations, but all share the same fundamental characteristic, namely that a transaction must claim a read (shared) or write (exclusive) lock on a data item before the corresponding database read or write operation. The lock prevents another transaction from modifying the item or even reading it, in the case of write lock. Data items of various sizes, ranging from the entire database down to a field, may be locked. The size of the item determines the fineness, or granularity, of the lock. Read lock : If a transaction has a read lock on a data item, it can read the item but not update it Write lock : If a transaction has a write lock on a data item, it can both read and update the item.

32. ·    Any transaction that needs to access a data item must first lock the item, • requesting a read lock only access or a write lock for both read and write • access. • ·    If the item is not already locked by another transaction, the lock will be • granted. • ·    If the item is currently locked, the DBMS determines whether the request is • compatible with the existing lock. If a read lock is requested on an item that • already has a read lock on it, the request will be granted; otherwise, the • transaction must wail until the existing lock is released. • A transaction continues to hold a lock until it explicitly releases it either during • execution or when it terminates (aborts or commits). It is only when the write • lock has been released that the effects of the write operation will be made • visible to other transaction.

33. Lock can solve Lost update problem : (An apparently successfully completed update operation by one user can be overridden by another user.) T3 T4 balance (15) Time1 Write_lock (balance) balance1 = (select balance from 15 Customer where accountID = 101); balance1 += 5.00; Time 2 Write_lock (balance) Time 3 update Customer set balance = Wait 20 ?balance1 where accountID = 101; Time 4 Commit/ Unlock (balance) Wait 20 Time 5 balance2 = (select balance from 20 Customer where accountID = 101); balance2 += 10.00; Time 6 update Customer set balance = 30 ?balance2 where accountID = 101; Time 7 Commit/ Unlock (balance) 30

34. Lock can solveThe uncommitted dependency problem : This problem occurs when one Transaction is allowed to see the intermediate result of another transaction before it has committed. T3 T4 balance (15) Time1 Write_lock (balance) balance1 = (select balance from 15 Customer where accountID = 101); balance1 += 5.00; Time 2 update Customer set balance = 20 ?balance1 where accountID = 101; Time 3 Write_lock (balance) Time 4 Wait Time 5 Rollback / Unlock (balance) Wait 15 Time 6 balance2 = (select balance from 15 Customer where accountID = 101); balance2 += 10.00; Time 7 update Customer set balance = 25 ?balance2 where accountID = 101; Time 8 Commit / Unlock (balance) 25

35. Lock can solve Incorrect summary problem : Balance bal 101 bal 102 T3 T4 Time1 Write_lock (balance) 15 15 balance1 = (select balance from Customer where accountID = 101); balance1 += 10.00; Time 2 update Customer set balance = 25 15 ?balance1 where accountID = 101; Time 3 Write_Lock (balance) Time 4 balance1 = (select balance from 25 15 Customer where accountID = 102); balance1 -= 10.00; Time 5 update Customer set balance = Wait 25 5 ?balance1 where accountID = 102; Time 6 Commit / Unlock (balance) Wait 25 5 Time 7 Total = select sum(balance) from customer where accountID = 101 or accountID = 102 Time 8 Commit / Unlock (balance)

36. ถ้าปล่อย Lock เร็วเกินไป อาจเกิดปัญหา Inconsistency กับฐานข้อมูล Write_Lock (balx); Read (balx); balx = balx + 100; Write(balx); Unlock (balx); Write_Lock (balx); Read (balx); balx = balx * 1.1; Write(balx); Unlock (balx); Write_Lock (baly); Read (baly); baly = baly * 1.1; Write(baly); Unlock (baly); Commit Write_Lock (baly); Read (baly); baly = baly - 100; Write(baly); Unlock (baly); Commit

37. Cascading rollback : the situation, in which a single transaction leads to a series of rollback. Cascading rollbacks are undesirable, since they potentially lead to the undoing of a significant amount of work. Clearly, it would be useful if we could design protocols that prevent cascading rollbacks. One way to achieve this with two-phase locking is to leave the release of all locks until the end of the transaction. T1 T2 T3 Write_Lock (balx); Read (balx); Read_Lock (baly); Read(baly); balx = baly + balx; Write(balx); Unlock (balx); . . . . Rollback Write_Lock (balx); Read (balx); balx = baly + 100; Write(balx); Unlock (balx); . . . . Rollback Read_Lock (balx); . . . . Rollback

38. Two-phase locking (2PL) : A transaction follows the two-phase locking protocol if all locking operations precede the first unlock operation in the transaction.    According to the rules of this protocol, every transaction can be divided into two phases; first a growing phase, in which it acquires all the locks needed but cannot release any locks, and then a shrinking phase, in which it releases its locks but cannot acquire any new locks. Two-phase locking protocol may cause deadlock. Deadlock : An impasse that may result when two or more transactions are each waiting for locks held by the other to be released. Neither transaction can continue because each is waiting for a lock it cannot obtain until the other completes. Once deadlock occurs, the applications involved cannot resolve the problem. Instead, the DBMS has to recognize that deadlock exists and break the deadlock in some way.

39. Lock can solveThe uncommitted dependency problem : This problem occurs when one transaction is allowed to see the intermediate result of another transaction before it has committed. Time1 Write_lock (balance); balance1 = (select balance from customer where accountID = 101); balance1 += 10.00; Time 2 Write_lock (balance); balance1 = (select balance from customer where accountID = 102; balance -= 10.00; Time 3 update Customer set balance = ?balance1 where accountID = 101; Time 4 update Customer set balance = ?balance1 where accountID = 102; Time 5 Write_lock (balance); balance2 = (select balance from customer where accountID = 102); Time 6 Wait Time 7 Write_lock (balance) balance2 = (select balance from customer where accountID = 101; Time 8 Wait Wait

40. In addition to these rules, some systems permit a transaction to issue a read lock on an item and then later to upgrade the lock to a write lock. This effectively allows a transaction to examine the data first and then decide whether it wishes to update it. If upgrading is not supported, a transaction must hold write locks on all data items that it may update at some time during the execution of the transaction, thereby potentially reducing the level of concurrency in the system. For the same reason, some systems also permit a transaction to issue a write lock and then later to downgrade the lock to a read lock.

41. Granularity of Data Items Granularity : The size of data items chosen as the unit of protection by a concurrency control protocol. A data item is chosen to be one of the following, ranging from coarse to fine, where fine granularity refers to small item sizes and coarse granularity refers to large item sizes: ·    The entire database. ·    A file. ·    A page (sometimes called an area or database space – a section of physical disk in which relations are stored). ·    A record ·    A field value of a record The size of granularity of the data item that can be locked in a single operation has a significant effect on the overall performance of the concurrency control algorithm. The granularity would prevent any other transactions from executing until the lock is released. Thus, the coarser the data item size, the lower the degree of concurrency permitted. On the other hand, the finer the item size, the more locking information that is needed to be stored. The best item size depends upon the nature of the transactions.

42. The solutions to this problem will involve providing a locking mechanism in the database server. Any restrictions on the concurrency of transactions will have a negative effect on the number of transactions that can be executing at any time. This balancing act is a typical trade-off. The more restrictive the concurrency strategy is, the more reliable it is, and the slower it is. DBMS designers, database administrators, and application developers must all carefully consider how much concurrency can be achieved without sacrificing either speed or reliability.

43. Timestamp-Based Protocal เป็นกฎเกณฑ์ที่ใช้ควบคุมให้การทำงานของรายการเปลี่ยนแปลงต่างๆ ภายใน schedule ให้มีการรันเป็น conflict serializable โดยระบบ จะทำการกำหนด Timestamps ซึ่งก็คือจำนวนตัวเลขสะสมครั้งละ 1 CLOCK “TICK” และระบบจะทำการนับ 1 CLOCK TICK ทุก ๆ 1 Microsecond เมื่อ Transaction เริ่มทำงาน จะได้รับ Timestamps ซึ่งเป็นเวลาปัจจุบันของนาฬิกา และเมื่อ Transaction เริ่มทำงาน คำสั่ง READ หรือ WRITE Transaction ก็จะได้รับ Timestamp สำหรับการ READ หรือ WRITE เช่นกัน

44. The Timestamp-ordering Protocal 1. Suppose that transaction Ti issues read(Q) (a)If TS(Ti) < W-timestamp(Q), then Ti needs to read a value of Q that was already overwritten. Hence, the read operation is rejected. (b)If TS(Ti) ≥ W-timestamp(Q), then the read operation is executed, and R-timestamp(Q) is set to the maximum of R-timestamp(Q) and TS(Ti). 2. Suppose that transaction Ti issues write(Q) (a)If TS(Ti) < R-timestamp(Q), then the value of Q that Ti is producing was needed previously, and the system assumed that that value would never be produced. Hence, the system rejects the write operation. (b)If TS(Ti) < W-timestamp(Q), then Ti is attempting to write an obsolete value of Q. Hence, the system rejects this write operation. (c) Otherwise, the system executes the write operation and sets W-timestamp(Q) to TS(Ti).

45. Failure Classification  Transaction failure. There are 2 types of errors that may cause a transaction to fail: Logical error: The transaction can no longer continue with its normal execution because of some internal condition, such as bad input, data not found, overflow or resource limit exceeded. System error : The system has entered an undesirable state.  System crash. There is a hardware malfunction, or a bug in the DBMS or OS, that causes the loss of the content of volatile storage and brings transaction processing to a halt. The content of nonvolatile storage remains intact.  Disk failure. A disk block loses its content as a result of either a head crash or failure during a data transfer operation.

46. The execution of an SQL statement begins with an implicit request to open a transaction, followed by the processing of the statement, followed automatically by a commit request. Rollback happens only when the SQL statement fails. An application must make explicit calls to the database transaction manager to enter explicit-commit mode and allow multiple SQL statements to execute as a single transaction. An application executes an open transaction statement (begin transaction) to ask the transaction manager to create a new transaction before the next SQL statement executes. The application executes a commit transaction statement to ask the transaction manager to commit the transaction. The application executes a rollback statement to ask the application to cancel the transaction.

47. Storage Hierarchy ระบบฐานข้อมูล เก็บอยู่ใน nonvolatile storage เช่น ดิสก์ โดยเนื้อที่ของดิสก์ถูกแบ่งเป็น fixed-length storage เรียกว่า block (เป็นหน่วยของข้อมูลที่ใช้ในการเคลื่อนย้ายระหว่าง ดิสก์ กับ main memory) Block ที่อยู่ในดิสก์เรียกว่า physical block Block ที่อยู่ใน main memory เรียกว่า buffer block คำสั่งที่ทำให้เกิด block movement ระหว่าง ดิสก์ กับ main memory คือ Input (X) : เคลื่อนย้าย physical block ที่บรรจุข้อมูล X จากดิสก์มาไว้ใน main memory Output(X) : เคลื่อนย้าย buffer block ที่บรรจุข้อมูล X ไปยังดิสก์ Input (A) A Output (B) B B Main Memory Disk

48. Transaction Ti ทำการส่งผ่านข้อมูลไปมาระหว่าง working area ของ Ti ใน main memory กับฐานข้อมูลในดิสก์ ด้วย 2 คำสั่ง คือ • Read (X) : คำสั่งนี้ทำการให้ค่า (assign) ข้อมูล X กับตัวแปร Xi โดยมีขั้นตอนการทำงาน ดังนี้ •  ถ้าบล็อก Bx ที่มีค่าข้อมูล X อาศัยอยู่ ยังไม่ได้อยู่ใน main memory ระบบจัดการ • ฐานข้อมูล จะออกคำสั่ง input (X) เพื่อเคลื่อนย้ายบล็อก Bx จากดิสก์เข้ามา •  ให้ค่าข้อมูล X กับตัวแปร Xi • Write (X) : คำสั่งนี้จะนำค่าของตัวแปร Xi assign ให้กับข้อมูล X ที่อยู่ใน buffer โดยมีขั้นตอนการทำงาน ดังนี้ •  ถ้าบล็อก Bx ที่มีค่าข้อมูล X อาศัยอยู่ ยังไม่ได้อยู่ใน main memory ระบบจัดการ • ฐานข้อมูล จะออกคำสั่ง input (X) เพื่อเคลื่อนย้ายบล็อก Bx จากดิสก์เข้ามา •  นำค่าจากตัวแปร Xi ให้กับข้อมูล X

49. Both operations may require the transfer of a block from disk to main memory. However, they do not require the transfer of a block from main memory to disk. The output (Bx) operation for the buffer block Bx on which X resides does not need to take effect immediately after write (X) is executed, since the block Bx may contain other data items that are still being accessed. A buffer block is eventually written out to the disk either because the buffer manager needs the memory space for other purposes or because the database system wishes to reflect the change to B on the disk. (DBMS performs a force-output of buffer B if it issues an output B).

50. Algorithms proposed to ensure database consistency and transaction atomicity despite failures are known as recovery algorithms, which have 2 parts :- 1: Actions taken during normal transaction processing to ensure that enough information exists to allow recovery from failures. 2: Actions taken after a failure to recover the database contents to a state that ensures database consistency, transaction atomicity, and durability.