.mode column
.header ON
.nullvalue NULL

drop table if exists acc;
create table acc(uid int, balance int);
insert into acc values (1,10);
insert into acc values (2,20);
insert into acc values (3,30);


-- ================================================================
-- 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


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

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

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

-- T1:
update acc set balance=11 where uid=1;
-- T1 has a RESERVED LOCK

-- T2:
update acc set balance=21 where uid=2;
-- T2 asked for a RESERVED LOCK:  DENIED

-- T3:
begin transaction;
select * from acc;
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 acc;
-- 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 acc;
-- 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' ?

