create table funding_requests (
       request_id	      integer primary key,
       requestor	      varchar(20),
       program_name	      varchar(20),
       -- in billions of dollars
       amount		      number,
       purpose		      varchar(50)
);

insert into funding_requests 
values
(1,'entitlement','Medicare',248,'Health care for old folks');

insert into funding_requests 
values
(2,'entitlement','Medicaid',280,'Health care for poor folks');

insert into funding_requests 
values
(3,'George W','Prescription Drugs',400,'Welfare for drug companies');

select * from funding_requests;
column request_id format 99
column requestor format a12
column program_name format a10
column amount format 9999
column purpose format a20

select * from funding_requests;

---- switch to win2 (TOP)

--- FIRST_COMMIT

commit;

select sum(amount) from funding_requests;

insert into funding_requests 
values
(4,'George W','IraqAid',87,'Rebuild Iraq');

select sum(amount) from funding_requests;

---- switch to win2 (ADD_EDUCATION)

---- SECOND_COMMIT

commit;

update funding_requests
set amount = 500
where request_id = 3;

---- switch to win2 (WIN2_UPDATE_3)

---- COMMIT_UPDATE_3

commit;

---- switch to win2 (SET_SERIALIZABLE)

---- WIN1_UPDATE_4

-- right now, Window 2 has a serializable transaction pending
-- let's update and commit

update funding_requests
set amount = 125
where request_id = 4;
commit;

---- switch to win2 (WIN2_UPDATE_4)


-------------- WINDOW 2 

-- TOP

describe funding_requests

select * from funding_requests;

--- switch to win1 (FIRST_COMMIT)

--- ADD_EDUCATION

select * from funding_requests;
select sum(amount) from funding_requests;

insert into funding_requests 
values
(4,'Democrats','Edumucation',75,'Votes from teacher unions');

--- ** big idea:  writers never wait for anyone except writers trying to
       update the same row or insert a row with the same primary key

--- see
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2570
    for how it works; also http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg08sql.htm#2655

--- switch to win1 (SECOND_COMMIT)

--- WIN2_UPDATE_3

select * from funding_requests where request_id = 3;

update funding_requests 
set amount = 450
where request_id = 3;

--- switch to win1 (COMMIT_UPDATE_3)

--- SET_SERIALIZABLE

set transaction isolation level serializable;
-- tell Oracle to watch for changes after this statement

--- switch to win1 (WIN1_UPDATE_4)

--- WIN2_UPDATE_4

update funding_requests
set amount = 80
where request_id = 4;

-- Oracle is unhappy!