School of Computing and Information Technology Session: Autumn 2021 University of Wollongong Lecturer: Janusz R. Getta CSCI235 Database Systems Assignment 2 12 April 2021 Scope This assignment includes the tasks related to processing of database transactions. The outcomes of this assignment are due by Saturday 8 May, 2021, 7.00 pm (sharp). Please read very carefully information listed below. This assignment contributes to 20% of the total evaluation in a subject CSCI235. A submission procedure is explained at the end of specification. This assignment consists of 4 tasks and specification of each task starts from a new page. It is recommended to solve the problems before attending the laboratory classes in order to efficiently use supervised laboratory time. A submission marked by Moodle as "late" is treated as a late submission no matter how many seconds it is late. A policy regarding late submissions is included in the subject outline. A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated. All files left on Moodle in a state "Draft(not submitted)" will not be evaluated. It is expected that all tasks included within Assignment 2 will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for the assessment task. Task 1 (6 marks) Concurrent processing of database transactions Consider the database transactions listed below. T1 T2 T3 a = read(x) b = read(x) d = read(z) write(x,a+1) c = read(y) write(z,2) write(y,a+2) write(z,b*c) commit commit commit Assume that the initial values of the persistent data items x, y, and z are the following. x = 1, y = 2, and z = 1. (1) (2 marks) Show a sample concurrent execution of the transactions T1, T2, and T3 that is not view serializable. Prove, that the execution is not view serializable. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. (2) (2 marks) Show a sample concurrent execution of the transactions T1, T2, and T3 that is not conflict serializable and that is view serializable. Prove, that the execution is not conflict serializable and that it is view serializable. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. (2) (2 marks) Show a sample concurrent execution of the transactions T1, T2, and T3 that is conflict serializable and that is not order-preserving conflict serializable. Prove, that the execution is conflict serializable and that it is not order-preserving conflict serializable. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. Deliverables A file solution1.pdf with: (1) a visualization of a sample concurrent processing of the transactions T1, T2, and T3 that is not view serializable and a proof that the processing is not view serializable. (2) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is not conflict serializable and that is view serializable and a proof that the execution is not conflict serializable and that it is view serializable. (3) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is conflict serializable and that is not order-preserving conflict serializable and a proof that the execution is conflict serializable and that it is not order-preserving conflict serializable. Task 2 (6 marks) Serialization graph testing, 2PL, and Timestamp ordering scheduler Consider a concurrent execution of database transactions T1, T2, and T3 when the execution is not controlled by any scheduler. T1 T2 T3 a=read(x) b=read(y) write(x,a+1) c=read(z) write(x,b+2) write(y,c+1) d=read(z) write(z,d-2) (1) (2 marks) Assume, that the transactions attempt to interleave their operations in the same way as in the execution above. Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by 2PL scheduler. Assume, that to simplify the problem we use only a general concept of a lock and we do not distinguish between shared locks and exclusive locks. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. (3) (2 marks) Assume, that the transactions attempt to interleave their operations in the same way as in the execution above. Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by timestamp ordering scheduler. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. Show the data items accessed by the transactions together with the timestamps left by the transactions on the data items. (3) (2 marks) Assume, that the transactions attempt to interleave their operations in the same way as in the execution above. Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by serialization graph testing scheduler. Draw a conflict serialization graph. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 10 Introduction to Transaction Processing (1), slide 9. Deliverables A file solution2.pdf with: (1) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by serialization graph testing scheduler and a conflict serialization graph, (2) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by 2PL scheduler, (3) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by timestamp ordering scheduler. Task 3 (5 marks) Processing transactions at READ COMMITTED isolation level Consider an anonymous PL/SQL block listed below. SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DECLARE max3323456721 NUMBER; maxall NUMBER; result NUMBER; BEGIN SELECT MAX(amount) INTO max3323456721 FROM TRANSACTION WHERE acc_num = 3323456721; SELECT MAX(amount) INTO maxall FROM TRANSACTION; SELECT maxall - max3323456721 INTO result FROM DUAL; DBMS_OUTPUT.PUT_LINE(result); COMMIT; END; (1) (3 marks) Assume, that the anonymous PL/SQL block is processed as a database transaction at READ COMMITTED ISOLATION level. Show a sample concurrent execution of the anonymous PL/SQL block listed above, such that the anonymous PL/SQL block interleaves the operations with another transaction and such that a result returned by DBMS_OUTPUT.PUT_LINE(result) is incorrect. The other transaction is up to you. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 14 Transaction Processing in Oracle DBMS slide 16. (2) (2 marks) Rewrite the anonymous PL/SQL block listed above such that it can be processed at READ COMMITTED level and show how the improved PL/SQL block interleaves the operations with another transaction and such that a result returned by DBMS_OUTPUT.PUT_LINE(result) is always correct. When visualizing the concurrent executions use a technique of two-dimensional diagrams presented to you during the lecture classes, for example, see a presentation 14 Transaction Processing in Oracle DBMS slide 16. Deliverables A file solution3.pdf with: (1) visualization of a sample concurrent execution of the anonymous PL/SQL block at READ COMMITTED level that interleaves the operations with another transaction and such that a result returned by DBMS_OUTPUT.PUT_LINE(result) is incorrect, (2) visualization of a sample concurrent execution of an improved function at READ COMMITTED level that interleaves the operations with another transaction and such that a result returned by DBMS_OUTPUT.PUT_LINE(result) is incorrect. Task 4 (3 marks) Processing transactions at SERIALIZABLE isolation level A database programmer implemented a stored procedure TRANSFER that transfer a given amount of money from one account to another. A procedure TRANSFER is listed below. CREATE OR REPLACE PROCEDURE TRANSFER( from_acc IN ACCOUNT.account_num%TYPE, to_acc IN ACCOUNT.account_num%TYPE, amount IN NUMBER ) IS from_balance ACCOUNT.balance%TYPE; BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT balance INTO from_balance FROM ACCOUNT WHERE account_num = from_acc; IF (from_balance >= amount) THEN UPDATE ACCOUNT SET balance = balance - amount WHERE account_num = from_acc; COMMIT; UPDATE ACCOUNT SET balance = balance + amount WHERE account_num = to_acc; COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('Not enough funds to transfer'); ROLLBACK; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); ROLLBACK; END TRANSFER; A comprehensive testing of a procedure TRANSFER together with the other concurrently running transactions and operating on the same relational table ACCOUNT revealed, that from time to time certain amounts of money disappear from the bank accounts. It simply means, that a procedure TRANSFER is implemented in incorrect way. Your task is to find and to comprehensively explain a mistake in the implementation of a procedure TRANSFER and show in what circumstances concurrent processing of the procedure with other transactions operating on the bank accounts may corrupt the contents of a relational table ACCOUNT. Please note, that as always an "educated guess" scores no marks. Deliverables A file solution4.pdf with the comprehensive explanations of a mistake in implementation of a procedure TRANSFER and an example when concurrent processing of the procedure with other transactions operating on the bank accounts may corrupt the contents of a relational table ACCOUNT. Submission Submit the files solution1.pdf, solution2.pdf, solution3.pdf, and solution4.pdf through Moodle in the following way: (1) Access Moodle at http://moodle.uowplatform.edu.au/ (2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page (3) When logged select a site CSCI235 (S121) Database Systems (4) Scroll down to a section SUBMISSIONS (5) Click at a link In this place you can submit the outcomes of Assignment 2 (6) Click at a button Add Submission (7) Move a file solution1.pdf into an area You can drag and drop files here to add them. You can also use a link Add… (8) Repeat a step (7) for the files solution2.pdf, solution3.pdf, and solution4.pdf. (9) Click at a button Save changes (10) Click at a button Submit assignment (11) Click at the checkbox with a text attached: By checking this box, I confirm that this submission is my own work, … in order to confirm the authorship of your submission. (12) Click at a button Continue End of specification
欢迎咨询51作业君
