Tasks Task 1: Concurrent transactions (6 marks) (1) Implement in PL/SQL the foll
ID: 3716870 • Letter: T
Question
Tasks Task 1: Concurrent transactions (6 marks) (1) Implement in PL/SQL the following database transactions to demonstrate deadlock. The first transaction supposed to change an applicant 000003 email from NULL to mpoppins612@hotmail.com. Then increase the salary by 1000 for all the positions offered by the employers located in New South Wales. The second transaction supposed to increase salary 10% for the positions offered by University of Wollongong. Then update an applicant 000003 phone number to 62336145. Both transactions supposed to run at SERIALIZABLE isolation level. Save the transactions in the files solution1-1.sql and solution1-2.sql. Simulate a concurrent execution of the transactions such that both of them will get into a deadlock situation. To control the concurrent execution of the transactions use a PL/SQL procedure SLEEP from the standard PL/SQL package DBMS_LOCK. A script file delay.sql shows a sample application of a procedure SLEEP. Explain the comments included at the end of a file solution1-1.sql why a deadlock has occurred. Save the reports from the execution the transactions in the files solution1-1.lst and solution1-2.lst with SQL*Plus options ECHO and FEEDBACK set to ON. The files solution1-1.lst and solution1-2.lst will be submitted. (2 marks) (2) Read the database transactions T1 and T2 defined in the script files t1.sql and t2.sql. Assume that a database user would like to concurrently run both transactions and assume that in the same moment no other transactions will be processed. What the best isolation levels would you execute each transaction at to avoid the corruption of the database and to achieve the best performance for each transaction? You have 3 options: READ ONLY, READ COMMITTED, and SERIALIZABLE. Use a technique of presentation of concurrent execution of two database transactions that explained to you during the lecture classes and such that the statements of the first transaction are listed on the left-hand side of a page and the statements of the second transaction are listed on the right-hand side of a page. Make sure that each statement starts in a different line to represent a different moment in time when its execution starts. You should show all possible schedules for two concurrent transactions with the lowest isolation levels except serial schedules. If you decide that a transaction must run at SERIALIZABLE level and it is possible to run such transaction at READ COMMITTED level then you will get no marks for this task as well. Any correct solution without the explanations is also worth no marks. Save the report in a file solution2.pdf. Remember to write the appropriate explanations required by each one of the cases (concurrent transaction schedule) you decided to follow. Note: The report should be formatted like the report file Example_report.pdf. (4 marks) Deliverables Submit files solution1-1.lst and solution1-2.lst with reports from processing of SQL script solution1-1.sql and solution1-2.sql. The report MUST have no errors (except deadlock error) and the report MUST list all SQL statements processed. Submit a file solution2.pdf contains a report of executions of concurrent transactions T1 and T2 defined in the script files t1.sql and t2.sql on all the cases at the lowest isolation levels and the explanations of the concurrent transactions do not corrupt the database. A report that contains no listing of executed SQL and/or PL/SQL statements scores no marks! A report that contains any kind of other processing errors scores no marks! Submission of a file with a different name and/or different extension and/or different type scores no marks! Any correct solution without the explanations for question (2) is also worth no marks. Task 2: Distributed databases (4 marks) It is recommended to read through COOKBOOK 9.1 before attempting this task. An objective if this task is to implement a distributed referential integrity constraint. This task must be implemented in ORACLE database systems in DATA-PCs. Implement SQL script solution3.sql that decomposes a sample database created by a script dbcreate.sql into the databases "SouthWest" and "NorthEast" located at the different Oracle servers. The "SouthWest" database site supposed to contain information about the information of applicants and employers located in the states Victoria, South Australia, Western Australia and Tasmania. These states are called “SouthWest” states. The "NorthEast" database supposed to contain information about the applicants and employers from the states Queensland, New South Wales, and North Territory. These states are called “NorthEast” states. (1) First, connect to your user account on Oracle server on one of data-pc systems in Database lab and execute a script dbcreate.sql to create a sample database. Execute the script file dbload.sql to load data into the database created by dbcreate.sql. In the future we shall refer to this database as to “NorthEast” database. Do not put the execution results of a script file dbcreate.sql and dbload.sql in a report file solution3.lst. (2) Connect to your user account on Oracle server installed on one of data-pc systems and different from a server use in step (1). To connect to another database server use a SQL*Plus statement connect user-name/password@data-pc.. . Execute script dbcreate.sql to create empty relational tables. In the future we shall refer to this database as to “SouthWest” database. Do not put the execution results of a script file dbcreate.sql in a report file solution3.lst (3) Implement a script file solution3.sql for the steps from here and below. Connect to the “NorthEast” database and create a database link to the “SouthWest” database. (4) While connected to the “NorthEast” database, copy all the contents of a relational table SKILL from the “NorthEast” database to the “SouthWest” database. Copy the contents of relational tables APPLICANT, EMPLOYER, EMPLBY, POSITION, SPOSSESSED, SNEEDED and APPLIES from the “NorthEast” database to the “SouthWest” database for those applicants, employers, positions, skill possessed, skill needed and applications that should be saved in the “SouthWest” states. Note: The contents of relational tables to be copied to “SouthWest” database include the applicants applied the positions which offered by the employers that located in the “SouthWest” states; Applicants employed by the employers that located in the “SouthWest” states; Employers hired applicants that located in “SouthWest” states; Positions offered by the employers applied by the applicants that located in “SouthWest” states. The skill needed and possessed by the positions and applicants to be copied to the “SouthWest” database; Applications except those records for the applicants in the “NorthEast” states who have only applied the positions located in the “NorthEast” state. (5) Delete all rows from relational tables APPLICANT, EMPLOYER, EMPLBY, POSITION, SPOSSESSED, SNEEDED and APPLIES at the “NorthEast” database for those applicants, employers, positions, skill possessed, skill needed and applications that only exist in the “SouthWest” database. Commit your deletions. Note: The contents of relational tables to be deleted from “NorthEast” database include the applicants in “SouthWest” states only applied the positions offered by, or only be employed by the employers that located in “SouthWest” states; The skill possessed by the applicants to be deleted; The skill needed by the positions to be deleted. The applications of the applicants located in “SouthWest” state only applied the positions offered in “SouthWest” states. (6) Drop the database link. Execute a script solution3.sql and save a report from the execution in a file solution3.lst. All statements included in a script solution3.sql must be processed with SQL*Plus options SET ECHO ON and SET FEEDBACK ON.
Explanation / Answer
ANS:-
Given that,
— Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; GO
BEGIN TRY
BEGIN TRANSACTION
—Suppose table name is EmployeeMaster
UPDATE EmployeeMaster SET email=‘mpoppins612@hotmail.com’
WHERE applicantID=‘000003’
UPDATE EmployeeMaster SET salary=salary + 1000
WHERE employerLocation=‘New South Wales'
COMMIT TRANSACTION
PRINT ’TRANSACTION SUCCEEDED’
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ’TRANSACTION FAILED’
END CATCH
— Transaction 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRANSACTION
UPDATE EmployeeMaster SET salary=salary + (salary * 0.1)
WHERE offeredBy=‘University of Wollongong'
UPDATE EmployeeMaster SET phone_number=‘62336145’
WHERE applicantID=‘000003’
COMMIT TRANSACTION
PRINT ’TRANSACTION SUCCEEDED’
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ’TRANSACTION FAILED’
END CATCH
THESE TWO TRANSACTIONS WILL CREATE A DEADLOCK DUE TO SERIALIZABLE ISOLATION LEVEL BECAUSE IN SERIALIZABLE ISOLATION LEVEL THE DATA CAANOT BE READ BY OTHER TRANSACTION UNTIL IT IS COMMITED BY THE PREVIOUS TRANSACTION. AS BOTH THE TRANSACTIONS WANTS TO UPDATE THE SAME TABLE AND SAME FIELD AND SAME ROWS OR MAY BE COMMON ROWS BOTH WILL TRY TO LOCK THE DATA AND UNTIL THE DATA IS LOCKED THE NEXT STATEMENT WILL NOT EXECUTE WHICH LEADS TO A DEADLOCK.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.