#################  ISOLATION LEVEL   ################
select @@tx_isolation; 
set session transaction isolation  level   serializable;
select @@tx_isolation;


##################
# INNODB
# TRANSACTIONS:  BEGIN / START TRANSACTION / COMMIT / ROLLBACK
# granularity of lock: record
# lock index records (if index available)
#
# To lock a table:
#           LOCK TABLE TRANSACTIONAL 
#
#################################


##################
# Atomicity
##################
# Client 1

select titleID, title, year from Titles limit 10;

start transaction;
update titles set year = 2001 where titleID=1; 
update titles set year = 2003 where titleID=3;
rollback;

select titleID, title, year from Titles limit 10;


start transaction;
update titles set year = 2001 where titleID=1;
update titles set year = 2003 where titleID=3;
commit;

select titleID, title, year from Titles limit 10;

# Client2: select titleID, title, year from Titles limit 10;

# lock wait timeout

##################
#  CONCURRENCY CONTROL
##################
#Table Titles 
#	titleID: 1 Title: Linux   year:  2001
#
#After the execution of Client1 and Client 2 
#	titleID: 1  Title: Linux   year:  2006
##################

# CLIENT 1: 
# op1	select @valore1 := year from titles where titleID=1;
# op2 select @valore1 := @valore1 + 4;
# op3 update titles set year = @valore1 where titleID=1; 

# CLIENT 2: 
# op1	select @valore2 := year from titles where titleID=1;
# op2 select @valore2 := @valore2 + 1;
# op3 update titles set year = @valore2 where titleID=1; 


# Schedule:
# Client 1: op1
# Client 1: op2
# Client 2: op1
# Client 2: op2
# Client 1: op3
# Client 2: op3


# select titleID, title, year from Titles where titleID=1;
#	titleID: 1 Title: Linux   year:  2002

####################################
USING TRANSACTIONS
####################################

# CLIENT 1: 
# start transaction;
# op1	select @valore1 := year from titles where titleID=1;
# op2 select @valore1 := @valore1 + 4;
# op3 update titles set year = @valore1 where titleID=1; 
# commit;

# CLIENT 2: 
# start transaction;
# op1	select @valore2 := year from titles where titleID=1;
# op2 select @valore2 := @valore2 + 1;
# op3 update titles set year = @valore2 where titleID=1; 
# commit;


# Schedule:
# Client 1: op1
# Client 1: op2
# Client 2: op1
# Client 2: op2
# Client 1: op3
# Client 2: op3


select titles, year from titles limit 10;


############ phanthom problem
#
#  select with open conditions (titleID>10)
#  the following commdand  is blocked
#  insert into titles (titleID, title, year) 
#             values (NULL, 'XXXXX', 213);  

############
# Client1:  start transaction; 
#           select title from Titles where titleID>10;
#           ..............

# Client2:  start transaction; 
#           insert into titles (titleID, title, year) values (NULL, 'XXXXX', 213);
#           ..............


############
# isolation levels
# 
# read uncommitted | read committed |
# repeatable read | serializable
#
############
# InnoDB by default isolation level: repeatable read 
#
# Execute the schedule above assuming repeatable read level  



##################
# MyISAM 
# lock table tablename read
# lock table tablename write
# .....
# unlock tables 
#
# lock type:  READ
#             WRITE
#             LOW_PRIORITY_WRITE
#
# Table fulltitles
#
