----------------------------------------------------------------
-- 1 - MOTIVATION FOR TRANSACTIONS


-- suppose we have a database for flights reservations
--
-- Run sqlite3 txn.db in terminal 1  -- run here all commands marked "User 1"
-- Run sqlite3 txn.db in terminal 2  -- run here all commands marked "User 2"

create table Flights(seat int, status int);

insert into Flights values(22,0); -- seat 22 is available
insert into Flights values(23,1); -- seat 23 is occupied
insert into Flights values(24,0);
insert into Flights values(25,0);
insert into Flights values(26,1);

-- User 1 and User 2 want to choose a seat, at about the same time:

-- User 1:
.headers on
.mode columns
select * from Flights;

-- User 2:
.headers on
.mode columns
select * from Flights;

-- User 1: seat 22 is available, grab it:
update Flights set status = 1 where seat = 22;

-- User 2: seat 22 is available, grab it:
update Flights set status = 1 where seat = 22;

-- User 1:
.quit
-- User 2:
.quit

-- *** In class: what is wrong ?


----------------------------------------------------------------
-- 2 - Definitions and Properties

-- begin transaction;   
-- . . .
-- commit;  -- or rollback;

-- Re-start demo from scratch, re-create database, then:

-- User 1:
begin transaction;

-- User 2: 
begin transaction;

-- User 1: 
select * from flights;

-- User 2: 
select * from flights;

-- User 1: 
update flights set status = 1 where seat = 22;

-- User 2: 
update flights set status = 1 where seat = 22;   -- DENIED !!

-- User 2: 
commit; -- (or rollback)  CAN'T ASSIGN SEAT

-- User 1: 
commit;

---------------------------------------------
-- Definition: a SERIAL execution of the transactions is one in which
-- transactions are executed one after the other, in serial order

-- User 1:
begin transaction;

-- User 2: 
begin transaction;

-- User 1: 
select * from flights;

-- User 2: 
select * from flights;

-- User 1: 
update flights set status = 1 where seat = 24;

-- User 2: 
update flights set status = 1 where seat = 24;   -- DENIED !!

-- User 1: 
commit;  -- ERROR -- WHY??

-- User 2: 
commit; -- (or rollback)

-- User 1: 
commit;  -- OK



-- sqlite ensures serializable execution of transactions
-- for that it may have to deny some requests
-- if it allowed user 1 to commit, then user 2 would suddenly see a change in the database; non-serializable!

-- WARNING: You can see somewhat different behaviors with different DBMSs

-- ================================================================
-- 3. Implementation of Transactions in SQLITE (note: all other systems are different!)
-- http://www.sqlite.org/atomiccommit.html
-- one lock for the entire database 
-- 
-- Step 1: READ LOCK (aka "SHARED" lock)
-- Step 2: when TXN wants to write
--         RESERVED LOCK
-- Step 3: when TXN wants to commit
--         PENDING LOCK
--         wait for all read locks to be released
--         EXCLUSIVE LOCK
--	   COMMIT

-- Re-start demo from scratch:


create table r(a int, b int);
insert into r values (1,10);
insert into r values (2,20);
insert into r values (3,30);

-- Run the script below in three different windows, T1, T2, T3:

-- T1:
begin transaction;
select * from r;
-- T1 has a READ LOCK

-- T2:
begin transaction;
select * from r;
-- T2 has a READ LOCK

-- T1:
update r set b=11 where a=1;
-- T1 has a RESERVED LOCK

-- T2:
update r set b=21 where a=2;
-- T2 asked for a RESERVED LOCK:  DENIED

-- T3:
begin transaction;
select * from r;
commit;
-- everything works fine, could obtain READ LOCK

-- T1:
commit;
-- SQL error: database is locked
-- T1 asked for PENDING LOCK -- GRANTED
-- T1 asked for EXCLUSIVE LOCK -- DENIED

-- T3':   (run T3' in the same window as T3)
begin transaction;
select * from r;
-- T3 asked for READ LOCK -- DENIED (because of T1)

-- T2:
commit;
-- releases the last READ LOCK

-- T1:
commit;
-- T1 asked for EXCLUSIVE LOCK -- GRANTED

-- T3':
select * from r;
-- T3 asked for READ LOCK -- GRANTED
commit;

-- *** In class: is this schedule serializable ?  If so, then what is the
--     serialization order of the four transactions T1, T2, T3, T3' ?

