CSCI235 Database Systems Assignment 2 2022
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CSCI235 Database Systems
Assignment 2
2022
Task 1 (3 marks)
Concurrent processing of database transactions
(1) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
write(y,a+1)
commit
b = read(y)
write(x,b-1)
commit
c = read(z)
write(z,c+2)
commit
Assume that the initial values of the persistent data items x, y, and z are the following. x = 1, y = 2, and z = 3.
Show a sample concurrent execution of the transactions T1, T2, and T3 that is view serializable and that is NOT conflict serializable.
Prove, that the execution is view serializable and that the execution is NOT 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.
(2) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
write(x,a+1)
commit
b = read(y)
write(x,b-1)
commit
c = read(z)
write(z,c+2)
commit
Assume that the initial values of the persistent data items x, y, and z are the following. x = 1, y = 2, and z = 3.
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.
(3) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
write(x,a+1)
commit
b = read(x)
write(x,b*2)
commit
c = read(z)
write(z,c+2)
commit
Assume that the initial values of the persistent data items x, y, and z are the following. x = 1 and z = 3.
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.
Deliverables
A file solution1.pdf with:
(1) a visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is view serializable and that is NOT conflict serializable, and the proofs that sample concurrent execution of the transactions T1, T2, and T3 is view serializable and that it is NOT conflict serializable .
(2) a 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.
(3) a visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is NOT view serializable and a proofthat the execution is NOT view serializable.
Task 2 (3 marks)
Serialization graph testing, 2PL, and Timestamp ordering schedulers
(1) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
w(y,a+2)
commit
b = read(y)
w(x,b+1)
commit
c = read(z)
d = read(y)
e = read (x)
commit
Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled
by Serialization Graph Testing scheduler and such that the execution triggers abort of one of the transactions.
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) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
w(y,a+2)
commit
b = read(y)
w(x,b+1)
commit
c = read(z)
d = read(y)
e = read (x)
commit
Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by 2PL scheduler and such that the execution leads to a deadlock and one ofthe transactions is aborted.
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) (1 mark)
Consider the database transactions listed below.
T1 T2 T3
a = read(x)
w(y,a+2)
commit
b = read(y)
w(x,b+1)
commit
c = read(z)
d = read(y)
e = read (x)
commit
Show a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by Timestamp ordering scheduler and such that the execution triggers abort of a transaction.
Assume, that to simplify the problem we use only a general concept of timestamp and we do not distinguish between read timestamps and write timestamp.
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) a visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by Timestamp Ordering scheduler and such that one of the transactions is aborted.
(2) a visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by 2PL scheduler and such that its ends in a deadlock and one ofthe transactions is aborted.
(3) a visualization of a sample concurrent execution of the transactions T1, T2, and T3 that is controlled by Serialization Graph Testing scheduler, and such one of the transactions is aborted.
Task 3 (2 marks)
Processing database transactions at READ COMMITTED isolation level
Consider an anonymous PL/SQL block listed below.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE
avgsal NUMBER(9,2);
BEGIN
FOR position_row IN (SELECT * FROM POSITION)
LOOP
SELECT AVG(salary)
INTO avgsal
FROM POSITION;
UPDATE POSITION
SET salary = salary + 0.00001*avgsal
WHERE pnumber = position_row.pnumber;
END LOOP;
COMMIT;
END;
/
(1) (1 mark)
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 the results stored in a database are 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) (1 mark)
Explain why the results obtained from a sample concurrent processing of database transactions are incorrect.
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) a 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 the results are incorrect,
(2) the explanations why the results are incorrect.
Task 4 (2 marks)
Processing database transactions at SERIALIZABLE isolation level
A database programmer implemented the following stored function SKILLS.
CREATE OR REPLACE FUNCTION SKILLS ( applicant_number NUMBER ) RETURN NUMBER IS
tots NUMBER (7);
totc NUMBER (8);
BEGIN
SELECT SUM (slevel)
INTO tots
FROM SPOSSESSED
WHERE anumber = applicant_number;
SELECT COUNT (anumber)
INTO totc
FROM SPOSSESSED
WHERE anumber = applicant_number;
IF (totc = 0) THEN
RETURN 0;
ELSE
RETURN tots/totc;
END IF;
END SKILLS;
It is possible, that in certain circumstances the processing of the function may return an incorrect result when it is concurrently processed concurrently with another transaction and it is processed at an isolation level READ COMMITTED.
Your task is
(1) to explain why the function may return an incorrect result when it is processed at an isolation level READ COMMITTED,
(2) to provide an example of a case when the function may return an incorrect result.
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.
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 solution4.pdf with the explanations why the function may return an incorrect result when it is processed at READ COMMITTED isolation level and an example of a concurrent processing of the function when the returned result may be incorrect.
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 ofthe bottom ofthe Web page
(3) When logged select a site CSCI235 (S122) Database Systems
(4) Scroll down to a section Assessment Items (Laboratories and Assignments)
(6) Click at a button Add Submission
(7) Move a file solution1.pdfinto 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 ofyour submission.
(12)Click at a button Continue
End of specification
2022-05-12