Lab 3 : Transactions

The goal of Lab 3 is to understand transactions of databases, and control of data concurrency and consistency in ORACLE multiuser environment.

Preliminaries

  1. This lab consists of 7 parts. For part D, E and F, you need to work with your partner in seperate windows. Ask your partner to login to SQL*Plus with your team Oracle account in another window. You and your partner use the same account but are shown different user processes by Oracle. We can simulate transactions of different Oracle users with the same account.
  2. This lab works with the movie2000.sql table that contains movies whose opening year is 2000. Download movie2000.sql in your working directory and execute it in your account.

Deliverable

You are expected to submit an "edited" script to help TA understand the observations. Some portions have to show the exact interleaving (temporal sequence) of steps from two transactions.

Summary of Transactions in Oracle

A transaction is a logical unit of work that comprises one or more SQL statements run by a single user. In Oracle, a transactions begins with the user's first executable SQL statement. A transaction ends when any of the following occurs :
  1. A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
  2. A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER.
  3. A user disconnects from Oracle. The current transaction is committed.
  4. A user process terminates abnormally. The current transaction is rolled back.
After one transaction ends, the next transaction begins with the next SQL statement.
Transaction control statements are:
  1. Make a transaction's changes permanent (COMMIT)
  2. Undo the changes in a transaction, either since the transaction started or since a savepoint (ROLLBACK)
  3. Set a point to which you can roll back (SAVEPOINT)
  4. Establish properties for a transaction (SET TRANSACTION)
  5. Control when Oracle commits pending changes to the database (SET AUTOCOMMIT)

PART A : COMMIT or ROLLBACK

This part examines the status of transaction when your process terminate abnormally.
  1. Ensure that AUTOCOMMIT is OFF using "SET ATUTOCOMMIT OFF".
  2. Execute movie2000.sql to load movie data.
  3. Query 'Unbreakable' movie. Examine its votes.
  4. Change the votes value of 'Unbreakable' to 6000.
  5. Re-query 'Unbreakable' movie to check the upate.
  6. Kill your SQL*Plus process to simulate a failure during a transaction. This is very different from a graceful "QUIT" inside SQL*plus. You may need to use another window to look at your processes using "ps" command in Unix.
  7. Re-login to SQL*Plus and query 'Unbreakable'.
    Determine the fate (e.g. rollback, commit) of the previous transaction which encountered system failure.

PART B : FAILURE of TRANSACTION with SAVEPOINT

In this part, we examine the status of transaction with a SAVEPOINT when you encounter abnormal termination.
  1. Ensure that AUTOCOMMIT is OFF.
  2. Update the votes value of 'Unbreakable' to 7000.
  3. Next, set a savepoint by executing "SAVEPOINT s0".
  4. To make the abnormal termination of the session, kill your SQL*Plus process.
  5. Re-login to SQL*Plus and query 'Unbreakable'. Is the votes value of 'Unbreakable' changed to the new value? Determine the fate (e.g. rollback, commit) of your transaction with a savepoint.

PART C : COMMIT, ROLLBACK and SAVEPOINT

This part shows an example of transaction controls with COMMIT, ROLLBACK and SAVEPOINT.
  1. Ensure that AUTOCOMMIT is OFF.
  2. Query 'Unbreakable' movie. Examine its score and votes.
  3. Set a savepoint by executing "SAVEPOINT s0".
  4. First, change the votes value of 'Unbreakable' to 8000.
  5. Set another savepoint by executing "SAVEPOINT s1".
  6. Next, we want to upgrade its score to 8.0 point. But let's make a mistake in this step. Execute the following incorrect update statement: 
     UPDATE movie2000 SET score = 8.0;
  7. Set another savepoint named s2, "SAVEPOINT s2".
  8. Re-query 'Unbreakable' to check if 'Unbreakable' score is upated to 8.0. We notice that the previous update statement modified the score value of other movies as well as 'Unbreakable'.
  9. Rollback your transaction by executing "ROLLBACK TO s1".
  10. Re-query 'Unbreakable'. Is the restoration successful? Is its votes value also changed to original? Why?
  11. Try "ROLLBACK TO s2". Does Oracle support a roll forward ?

  12. Now, do a correct update on score of 'Unbreakable': 
     UPDATE movie2000 SET score = 8.0 WHERE title='Unbreakable';
  13. Commit your transaction using the command "COMMIT".
  14. Again, let's come back to the original score value of 'Unbreakable'. Try "ROLLBACK TO s1". Does Oracle allow rollback across commit ?

PART D: CONCURRENCY CONTROL : ISOLATION LEVELS & IMPLICIT DATA LOCKING

In this part, we examine the difference of concurrency control by isolation levels and understand the default locking characteristics of DML(e.g. INSERT, UPDATE, DELETE).
  1. Ask your partner to login to SQL*Plus using your team account in another window.
  2. Ensure that AUTOCOMMIT is OFF in both SQL*Plus environments.
  3. In your window, query the score of 'Unbreakable'.
  4. Ask your partner to retrieve the score of 'Unbreakable' in his/her window.
  5. Execute following SQL in your window to give a possible maximum score to 'Unbreakable'. 
    UPDATE movie2000 SET score = (SELECT MAX(score) FROM movie2000)
WHERE title = 'Unbreakable' ;
  6. Re-query 'Unbreakable' to confirm the update. What is its score?
  7. Ask your partner to requery 'Unbreakable'. Does this query hang? Does your partner see any change in the score value?
  8. Next, ask your partner to update the score of Unbreakable to 10. Does Oracle allow it? Explain the reason.
  9. Recall that 2 phase locking schemes with read/write locks. Does Oracle follow the 2 pahse locking schemes? Why? Explain with your observation in this part.
  10. Ask your partner to cancel his/her previous operation using Ctl-C and requery 'Unbrekable'.
  11. Commit your transaction using the command "COMMIT".
  12. Ask your partner to requery the score of Unbreakable. Does your partner see the change by your update ?
  13. What is the default isolation level of Oracle from this experiment?

    Next, we examine the effect of a change of transaction isolation level.

  14. You and your partner re-login to SQL*Plus and ensure that AUTOCOMMIT is OFF.
  15. Re-execute movie2000.sql to start with the initial state of movie2000 table.
  16. You will do the previous experiment(Part D) again in default transaction isolation level. Your partner does it in SERIALIZABLE transaction level. Ask your partner to his/her transaction level to SERIALIZABLE using the follwoing statement. 
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  17. Redo above experiment(from step 3 in part D) with your partner.
  18. How different your observation in this experiment is with the previous experiment?

PART E: CONCURRENCY CONTROL : EXPLICIT DATA LOCKING

At the transaction level, transactions that include the following SQL statements override Oracle's default locking: In this part, we examine an explicit data locking with SELECT ... FOR UPDATE statement.
  1. Login to SQL*Plus and ensure that AUTOCOMMIT is OFF.
  2. Re-execute movie2000.sql to start with the initial state of movie2000 table.
  3. Retrieve the score of 'Unbreakable' in your window.
  4. Ask your partner to update the score of 'Unbreakable'. Does Oracle allow this update?
  5. Ask your partner to do "COMMIT" to end his/her current transaction and to release locks as well as free up relevant rollback segments.

  6. Next, in your window, run the following query statement for a future update on 'Unbreakable' : 
    SELECT * FROM movie2000 WHERE title = 'Unbreakable'
FOR UPDATE OF score NOWAIT;
  7. Again ask your partner to update the score of 'Unbreakable'. Does Oracle allow this update? Why?
  8. Execute "Rollback" in your window.
  9. Ask your partner lock a whole movie2000 table using a exclusive table lock. 
     LOCK TABLE movie2000 IN EXCLUSIVE MODE;
  10. Again, in your window, execute the query statement with FOR UPDATE OF. Does this query hang ? Why ?

PART F: NONREPEATABLE READ & PHONTOM READ

In this part, we examine nonrepeatable read and phontom read by isolation level.
  1. Login to SQL*Plus and ensure that transaction isolation level is READ COMMITED. 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. Ensure that AUTOCOMMIT is OFF.
  3. In your window, query the movie whose title starts with 'G'.
  4. Also ask your partner to query the movie whose title starts with 'G'.
  5. In your window, delete all movies whose title starts with 'G' and insert a new movie whose title is 'Godfather,2000'. 
    DELETE FROM movie2000 WHERE title LIKE 'G%';
INSERT INTO movie2000(id, title, yr) VALUES(1702,'Godfather,2000', 2000);
  6. Execute "COMMIT" in your window.
  7. Ask your partner to requery the movie whose title starts with 'G'. Do your partner get the same result as in his/her previous query. If not, explain why your parter have different result. Does Oracle provide nonrepeatable read and phantom read at the default isolation level? Is there any method to prevent these phenomena?

PART G: BIG PICTURE

Using the information gathered from this lab, answer the following questions about the concurrency control technique used by Oracle. Provide brief explanations using the observations in this lab.
  1. Is Oracle's CCT conservative?
  2. Is Oracle's CCT strict?
  3. Does Oracle's CCT obey two phase locking protocol ?
  4. Can Oracle's CCT lead to phantom problems?
  5. Can Oracle's CCT lead to deadlocks?
  6. Can Oracle's CCT lead to livelocks?
  7. Can Oracle's CCT lead to cascaded rollbacks?
  8. Is Oracle's CCT "recoverable" (i.e. never abort a transaction during its commit due the actions of a different transaction) as defined in textbook ?