Consider the following parallel schedule. What do you expect will happen at each step? and then run through the schedule to check your intuition.

Now, manually run the schedule in two parallel SQL*Plus sessions and check to see if your expections were correct.

Experiment a bit with some SQL*Plus sessions, making changes, exiting and restarting the session and then answer the following questions.

1. Does Oracle handle the transaction life-cycle (see Figure 20.4) with respect to starting and stopping SQL*Plus sessions?
2. Can we implement any of the ACID properties using START-TRANSACTION/SAVEPOINT/ROLLBACK/COMMIT?

Rebuild your CPDB as needed. Store your answers in lab10_1.txt; you don’t need to preserve your code.

Manually re-enact the well-known concurrency problems listed below. Note the following before you start.

• Use separate SQL*Plus sessions to simulate separate threads (as done in the previous exercise).
• Turn AUTOCOMMIT on, which allows you to demonstrate these concurrency problems at the SQL*Plus command-line.
• A good test transaction is to try to add 0.2 and/or subtract 0.1 from the rank of one of the movies. You can simulate “reading” the current value by selecting it, modifying the value in your head and “writing” the modifed value by updating the record.

And now, the problems - Do only the problems assigned by the instructor:

1. The lost update problem, cf. Figure 20.3.a.
2. [Optional] The dirty read problem, cf. Figure 20.3.b.
3. [Optional] The incorrect summary problem, cf. Figure 20.3.c.

Save two scripts for each problem exercise, e.g., lab10_2a_script1.txt and lab10_2a_script2.txt for exercise 10.2.a.

Re-run the schedule(s) from the previous exercise, but this time use an appropriate locking mechanism and/or transaction commands to ensure the ACID properties. You should turn AUTOCOMMIT back OFF for these exercises.

As you run these schedules, be sure to test what each script knows and when it knows it. Note that Oracle may automatically handle some locking and/or transaction commands.

Again, save two scripts for each exercise, in lab10_3a_script1.txt and lab10_3a_script2.txt to match exercise 10.2.a.

Consider the following stored procedure.

CREATE OR REPLACE PROCEDURE incrementRank
	(movieIdIn IN Movie.id%type, 
	 deltaIn IN integer
    ) AS
	x Movie.rank%type;
BEGIN
	FOR i IN 1..50000 LOOP
		SELECT rank INTO x FROM Movie WHERE id=movieIdIn;
		UPDATE Movie SET rank=x+deltaIn WHERE id=movieIdIn;
		COMMIT;
	END LOOP;
END;
/

Restart your two SQL*Plus sessions, run the given stored procedure simultaneously in each of them using:

EXECUTE incrementRank(movieId, smallIncrementValue);

Now, determine if it ran correctly. If it does, explain how. If it doesn’t, identify the problem and modify the code so that it does.

Submit your procedure along with an explanation of any changes you made in lab10_4.sql.