------------------------------------------------------------------------
-- SQL EXAMPLES                                                       --
-- Introduction to Databases, IT University of Copenhagen, Fall 2004  --
-- Taught by Rasmus Pagh                                              --
-- November 5, 2004                                                   --
------------------------------------------------------------------------

-- Commit and rollback examples
-- ----------------------------

-- The user executing the SQL command is given before the >.
-- (The two users could also be two different connections
-- to the database by the same user, if you want to try it out alone.)

pagh> CREATE TABLE Primes (p INT);
pagh> GRANT SELECT, UPDATE, INSERT ON Primes to pagh2;

-- Isolation level SERIALIZABLE:

pagh>  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
pagh2> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

pagh>  SELECT * FROM Primes;
pagh2> SELECT * FROM pagh.Primes;

pagh>  INSERT INTO Primes VALUES (41);
pagh2> INSERT INTO pagh.Primes VALUES (43);

pagh>  SELECT * FROM Primes;
pagh2> SELECT * FROM pagh.Primes;

pagh>  COMMIT;
pagh2> SELECT * FROM pagh.Primes;

pagh2> COMMIT;
pagh2> SELECT * FROM pagh.Primes;


-- Isolation level READ COMMITTED (default in Oracle):

pagh>  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
pagh2> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

pagh>  INSERT INTO Primes VALUES (2);
pagh2> SELECT * FROM pagh.Primes;

pagh2> INSERT INTO pagh.Primes VALUES (2003);
pagh2> SELECT * FROM pagh.Primes;

pagh>  COMMIT;
pagh2> SELECT * FROM pagh.Primes;
pagh>  SELECT * FROM Primes;

pagh2> COMMIT;
pagh>  SELECT * FROM Primes;

pagh>  INSERT INTO Primes VALUES (3);
pagh>  ROLLBACK;
pagh>  SELECT * FROM Primes;


-- Process killed example
-- ----------------------

COMMIT;
INSERT INTO Primes VALUES (5);
SELECT * FROM Primes;

-- Now use the operating system feature to kill SQL*Plus, and log in again

SELECT * FROM Primes;
DROP TABLE Primes;