================================================================
CSE 344 -- Spring 2011
Lecture 19:   Transactions

================================================================

-- We will run these in sqlite (not in postgres !)

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:
select seat from Flights where status = 0;

-- User 2:
select seat from Flights where status = 0;

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

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

-- Transaction = a collection of statements that are executed atomically

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

-- rerun the example in the following order:
-- User 1: begin;
-- User 2: begin;
-- User 1: select seat from flight where status = 0;
-- User 2: select seat from flight where status = 0;
-- 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

-- Fact: nothing can go wrong if the system executes transactions serially

-- *** In Class: the database system could execute all transactions
--     serially, but it doesn't do that.  WHY NOT ?

-- Definition: a SERIALIZABLE execution of the transactions is one
--   that is equivalent to a serial execution

-- *** In class: repeat the two transactions by user 1 and 2, but
--      switch the commit order.  That is, user 1 commits while user 2
--      continues the transaction.  But user 1 receives an error when
--      attempting to commit.  WHY ???


-- sqlite ensures serializable execution of transactions, but may have
   to abort some of them in order to do that

-- postgres fails short of full serializability, in several ways.  You
   need hacks in the application in order to guarantee
   serializability.  We discuss this next time.

---------------------------------------------

White Board Only from here

Definition: each transaction is ATOMIC meaning that all its updates
must happen or not at all.  Important for recovery.

Example: move $100 from account 1 to account 2

update Accounts
set balance = balance - 100
where account = 1

update Accounts
set balance = balance  +100
where account = 2

If the system crashes between the two updates, then we are in trouble.

begin transaction
update Accounts
set balance = balance - 100
where account = 1

update Accounts
set balance = balance  +100
where account = 2
commit

Now all updates happen atomically, when the commit is done.

*** In class: how are atomic commits possible ?

Discuss the main idea of the Write-Ahead-Log (WAL).

---------------------------------------------

Read-only transactions

SET TRANSACTION READ ONLY;

This helps the system in scheduling transactions more efficiently

---------------------------------------------

Dirty Reads

1. Consider the flight seats example.  Transaction 1:

		 begin transaction;
		 select seat from flights where status = 0;

		 -- user selects a seat, say 22
		 update flights set status = 1 where seat = 22;

		 -- user pays for the ticket

		 if (everything ok)
		 commit;
		 else rollback;

If the transaction is aborted, then the status of seat 22 was "dirty"
for a short period of time: it was 1, but was later rolled back to 0.

Suppose a second user runs an identical transaction, Transaction 2:

                 ...
		 select seat from flights where status = 0;


Suppose seat 22 has status = 1, but this is "dirty" data,
i.e. Transaction 1 has not yet committed.  Should Transaction 2 be
allowed to see this "dirty" value ?

***  In class: discuss pros/cons

Weaker isolation level:

        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


Note: this way we tell SQL that we don't need full serializability,
and that dirty reads are OK.


2. Now consider bank transfer, and let's rewrite it like this:

  begin transaction
  update Accounts set balance = balance + 100 where account = 2
  if (select balance from Accounts where account = 1) < 100
        rollback;
  else
  update Accounts set balance = balance - 100 where account = 1
  commit

User 1 transfers $100 from Account 1 to Account 2
User 2 transfers $100 from Account 2 to Account 3  (similar code)

Both use READ UNCOMMITTED. 

*** In class: what goes wrong ?

---------------------------------------------

Next isolation level:

          SET  TRANSACTION ISOLATION LEVEL READ COMMITTED

*** In class what can go wrong here ?


---------------------------------------------

Next isolation level:

          SET  TRANSACTION ISOLATION LEVEL REPEATABLE READ

This is NOT YET serializable because of phantoms (which we discuss
next time)

---------------------------------------------

Highest isolation level:

          SET  TRANSACTION ISOLATION LEVEL SERIALIZABLE